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
Go to Top of Page

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
Go to Top of Page

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/
Go to Top of Page
   

- Advertisement -