DB Wont Shrink - SQL Server Forums

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources