Cannot Shrink Log file

Question

Post reply

Cannot Shrink Log file

John Burgess-239089

SSC Veteran

Points: 222
More actions
July 20, 2005 at 8:29 am

#165423

On several occasions recently, I have found that I cannot shrink a transaction log file.
It happens from time to time, that the transaction log file of our test databases will grow (because they are set to auto expand up to a certain limit) but with disk space shortage, we need to shrink the files back down to a reasonable size. I normally do this with DBCC shrinkfile, and normally have success with it.
But occasionally, I notice that it does not work, presumably because there are transactions still in the file. My current example is a 2GB log file, with 26MB of space used. Presumably, the problem is that this is fragmented in the file and therefore SQL Server cannot shrink the device. The problem is, that no matter what I do, I cannot reduce the 26MB used.
I have tried backing up, truncating etc but no luck

Anybody had similar experience ?
/John

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

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

Lawrence-136504 SSC Eights! Points: 961 More actions · Answer 1

Hello John,

Have you tried truncating the log first in query analyzer?

(i.e. -

BACKUP LOG DB_NAME WITH

TRUNCATE_ONLY

)

I usually truncate then shrink.

I hope this helps you.

Thanks,

LL

John Burgess-239089 SSC Veteran Points: 222 More actions · Answer 2

Thanks

Yes I tried already. Whatever I do, I cannot remove the 26mb used. I can expand the device further, and when I shrink it again, it will not reduce below 2GB in size.

/John

Lawrence-136504 SSC Eights! Points: 961 More actions · Answer 3

I'm assuming that your doing this in Query Analyzer. Try doing it in Enterprise Manager.

Right click your database>All Tasks>Shrink Database>click on Files button on bottom right>Select TLog in drop down list for Database File>click ok. It will shrink it. See if that gives you a different result.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

You may not be able to reduce the log size further because the transaction log can't shrink below the size of the active virtual log file. DBCC LOGINFO will show the size of the active virtual log file (the active file will have a status of 2).

Greg

John Burgess-239089 SSC Veteran Points: 222 More actions · Answer 5

Thanks to both of you for your advice.

Somehow I have managed now to shrink the file. I tried using the shrink database through enterprise manager and it actually increased the amount of space used in the file (active portion) from 26mb to 48mb. I assume that the shrinkdb must create some transactions. The actual file size was not reduced.

I then removed the option on the log file (auto expand) and tried again. This time the shrinkdb reduced the active portion to 21mb. Then I ran dbcc shrinkfile and this worked.

I wish I could explain it logically but I can't. Thanks Greg for the DBCC LOGINFO command, this will be useful in the future.

/John