Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 DB Wont Shrink

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-05-26 : 16:03:03
my database will not reclaim unused space, i've tried shrinking the db, and nothing, i've tried just files in the group, nothing....

any help would be greatly appreciated.


i've tried:



USE [TestDB]
GO
DBCC SHRINKDATABASE(N'TestDB',truncateonly )
GO

EXEC dba.dbo.isp_Backup
@path = '\\natimarksql4\i$\MSSQL\BACKUP\SQL3\',
@dbType = '-testdb',
@bkpType = 'Tlog',
@retention = 30,
@bkpSwType = 'NC',
@archiveBit = 1,
@copyOnly = 0
go

USE [TestDB]
GO
DBCC SHRINKDATABASE(N'TestDB',truncateonly )
GO

EXEC dba.dbo.isp_Backup
@path = '\\natimarksql4\i$\MSSQL\BACKUP\SQL3\',
@dbType = '-testdb',
@bkpType = 'Tlog',
@retention = 30,
@bkpSwType = 'NC',
@archiveBit = 1,
@copyOnly = 0
go

USE [TestDB]
GO
DBCC SHRINKDATABASE(N'TestDB',truncateonly )
GO

EXEC dba.dbo.isp_Backup
@path = '\\natimarksql4\i$\MSSQL\BACKUP\SQL3\',
@dbType = '-testdb',
@bkpType = 'Tlog',
@retention = 30,
@bkpSwType = 'NC',
@archiveBit = 1,
@copyOnly = 0
go

USE [TestDB]
GO
DBCC SHRINKDATABASE(N'TestDB',truncateonly )
GO
[code]

and

[code]EXEC dba.dbo.isp_Backup
@path = '\\natimarksql4\i$\MSSQL\BACKUP\SQL3\',
@dbType = '-testdb',
@bkpType = 'Tlog',
@retention = 30,
@bkpSwType = 'NC',
@archiveBit = 1,
@copyOnly = 0
go

USE [TestDB]
GO
DBCC SHRINKFILE (N'TestDB_FileGroup01' , 0, TRUNCATEONLY)
GO


EXEC dba.dbo.isp_Backup
@path = '\\natimarksql4\i$\MSSQL\BACKUP\SQL3\',
@dbType = '-testdb',
@bkpType = 'Tlog',
@retention = 30,
@bkpSwType = 'NC',
@archiveBit = 1,
@copyOnly = 0
go

USE [TestDB]
GO
DBCC SHRINKFILE (N'TestDB_FileGroup01' , 0, TRUNCATEONLY)
GO



and neither claim space. the db has 18 files, 2 logs, and 16 data files. SSMS says i have like 99% free space, yet the files do not decrease in size :(

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-26 : 16:33:46
You need to give a target size and leave out the tryncateonly then it will reorganise.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -