db file 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 file wont shrink

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-06-20 : 15:00:38

for some odd reason, i can not get this file to shrink.

this is currently what im doing:



ALTER DATABASE CONSUMER SET RECOVERY SIMPLE
GO
ALTER DATABASE CONSUMER SET RECOVERY FULL
GO

EXEC	dba.[dbo].[isp_Backup]
		@path = N'\\n8800\mssql\BACKUPS\DBSERVER02',
		@dbType = '-CONSUMER',
		@bkpType = N'FULL',
		@retention = 30,
		@bkpSwType = N'NC'
GO

Checkpoint 
GO

DBCC SHRINKFILE ('CONSUMER_201306_04',100000,Truncateonly)
go

ALTER DATABASE CONSUMER SET RECOVERY SIMPLE

all other files (1,2,3,5) shrink fine, but this one has about twice as much space as it needs, and i cant shrink it. any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-06-24 : 18:21:34

Shrink in small increments instead. I use MVJ's script that he posted here a while ago. His I think shrinks in 50MB chunks, but I always change that to a higher value such as 500MB or even 1024MB.

And if that doesn't work, try rebuilding the indexes and then shrinking in small chunks.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-06-24 : 18:24:27

Here's the script: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-24 : 23:45:04

Hi Tara, I read about your approach about shrinking in blocks of 100MB last week, and I must feedback that it works perfectly.
Thanks.

Duane.
http://ditchiecubeblog.wordpress.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources