Shrink Problems | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Shrink Problems

I am trying to shrink a database that has (almost) filled the disk the files are on.
As soon as I issue the SHRINKDATABASE, the transaction log start groing out of proportions, fills the disk completely, and breaks the shrink operation. The database is running in single user mode, and the recovery model is simple. I just can’t understand what is going on, and i really need that disk space.
take it out of single-user mode. the transaction log cannot be cleared while it is in single-user mode. there is no need for it to be in single-user during a shrink operation. stick in multi-user mode, and simple recovery mode, and you should be ok.
Tom Pullen
DBA, Oxfam GB
The exact same thing happens in multi-user mode.
I tried that first, as part of my regular script, but i blew. I usually shrink that database monthly, but this time it has overgrown because od some data imports i was not informed about, and now I’m left with the hot potato.
what does your script do? anything else? maybe you could post it in its entirety for me to peruse.? Tom Pullen
DBA, Oxfam GB
The other option (bit murky) is detach the database and delete the Transaction log file, then re-attach using SP_ATTACH_SINGLE_FILE_DB in order to recreate a fresh Tlog file. Ensure to maintain required levels of free space for the drives where data files and log files are located. Perform the Trnasaction log backup regularly (say atleast 15 mins interval) in order to maintain the logical space of Transaction log that helps avoiding the bulge of Tlog. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:Originally posted by satya The other option (bit murky) is detach the database and delete the Transaction log file, then re-attach using SP_ATTACH_SINGLE_FILE_DB in order to recreate a fresh Tlog file.

It’s not really murky – it works a treat in my experience. maybe not to be recommended on production servers, though.
Tom Pullen
DBA, Oxfam GB
At the start of the procedure i backup and shrink the log file, so it is small when i issue the database shrink, a simple
DBCC SHRINKDATABASE (‘billing’,0) satya, are you suggesting that i run transaction log backups while the database is shrinking to avoid the overgrowing of the log file?
I’m not sure whether i can run trans.log backups at all, since my db is in simple recovery mode.
I recommend using dbcc shrinkfile instead and specify a target size. Tom Pullen
DBA, Oxfam GB
It’s ok, it turned a SQL Server bug by itself http://support.microsoft.com/?kbid=891017
I think we can specify ‘Trunc log on chkpt’ when Recovery model is SIMPLE .Is there any issue with tht? anyone pls advice …
]]>