unable to shrinkdatabase

Question

Post reply

unable to shrinkdatabase

doug turner

SSChasing Mays

Points: 657
More actions
September 14, 2007 at 7:37 am

#118268

If I run backup log <database> with truncate_only, it appears to run ok - no errors. If I then run dbcc shrinkfile(<database>, <size&gt, it appears to run ok - no errors. BUT, when I then try to do a shrinkdatabase(<database>, TRUNCATEONLY), I get a error telling me the trans log is full.
I'm running SQL Server 2005 on Windows Server 2003. I see 50Gb still available on the drive. The database log is 500K and the database file is 8.1Gb.
So, why do I get the trans log full error?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

Issue check point that will commit the uncommited transactions.

Backup the databse will do the same.

dbcc shrinkfile('log file path',size)....

If all these are not working then

take a full backup,change the recovery mode to simple and then run dbcc shrinkfile('log file path',size).It will definitely work.

doug turner SSChasing Mays Points: 657 More actions · Answer 2

Thanks for the tip. Once I did the checkpoint, I was able to shrink the db. I have nightly maintenance that dumps the logs (we don't need backups) and does a shrinkfile. Should I also be doing a checkpoint and shrinkdatabase during this same interval?

Mike Good SSCertifiable Points: 7415 More actions · Answer 3

1. You got the point, but I think Rasheesh used somewhat poor choice of words above. Checkpoint does not really "commit the uncommited transactions", rather it flushes committed transactions to disk; uncommitted transactions remain in the txlog until committed or rolled-back.

2. Why would you want to shrink files every day, and then force SQL Server to have to regrow them every day? I would only shrink files on an ad-hoc basis, if you know you've just purged off an unusually large amount of data, and the DB is not likely to regrow. The extra unused space in your txlog isn't hurting anything.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

VastSQL

SSC-Dedicated

Points: 36097

September 14, 2007 at 8:27 am

#733795

Mike

Thanks for that tip....

doug turner SSChasing Mays Points: 657 More actions · Answer 5

I decided to do it nightly because of an issue we had on another db that ended up eating all the disk space. And since is not my primary job repsonsibility, I was afraid I'd forget to do it if I didn't schedule a nightly program. Plus the vendor for the software this db is the backend for suggested doing it nightly.