Shrinking the Database Fills up the Log File

Question

Shrinking the Database Fills up the Log File

apat

SSCertifiable

Points: 5929
More actions
June 23, 2010 at 1:58 pm

#223178

Hi All,
I have a very limited drive space on my server. And my database has enough unallocated space to be freed upon. My database is in simple recovery mode. Now, when I try to shrink the data (.mdf) file to minimum size, this operation basically fills up the log file and because of that my drive size start reducing. Cosidering if I can't change the recover model, what are the other options to avoid this from happening?
Thanks for the help...

Viewing 8 posts - 1 through 7 (of 7 total)

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Not much more you can do. If you are in SIMPLE mode, you're truncating the log as fast as you can, and shrinking a database is a logged operation. You can't shrink the data without adding to the log. Can you move the log file to an alternate location if needed?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Shrink it in small chunks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

apat SSCertifiable Points: 5929 More actions · Answer 3

Thanks both for the reply,

If I change the recovery model to bulk logged, and then shrink -- will it help?

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 4

No -- DBCC ShrinkDatabase is not one of the minimally logged operations, so bulk logging won't help you.

Besides -- SIMPLE is going to be better for log space than bulk logged in almost every case I can think of.

apat SSCertifiable Points: 5929 More actions · Answer 5

Okay, thanks for the reply. I would probably move the log file to some other location if possible.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

apat (6/23/2010)
If I change the recovery model to bulk logged, and then shrink -- will it help?

You're already in simple recovery, in simple recovery bulk operations are minimally logged and the log is automatically truncated on checkpoint. In bulk logged recovery bulk operations are minimally logged but the log is only truncated on a checkpoint.

Hence, if you change to bulk logged recovery, bulk logged operations (of which shrink is not) will still have the same log impact but now you'll have to take log backups for the space to be reused. Hence the log is more likely to grow, not less.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

apat (6/23/2010)
Okay, thanks for the reply. I would probably move the log file to some other location if possible.

Good idea. Also see of you can get more space for the data file so that you don't have to shrink it. It's not a good thing to do regularly.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass