shrinking log file

Question

Post reply

shrinking log file

jsb12

SSChasing Mays

Points: 608
More actions
January 22, 2010 at 8:46 am

#137601

To shrink a log file, is it necessary to do a log backup? Can we just use dbcc shrinkfile?
I just restored the database and put it in simple mode and tried just the dbcc shrinkfile. Also there was a message saying couldn’t shrink log because all logical log files are in use. But yet it didn’t give any error, it succeeded and the log file was reduced in size. So I didn’t understand what happened. Again, can we use just dbcc shrinkfile?

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

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

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 1

jsb12 (1/22/2010)
To shrink a log file, is it necessary to do a log backup? Can we just use dbcc shrinkfile?

To shrink a file and give space to OS you have to make use of dbcc shrinkfile.

In order to shrink a log file if you run dbcc shrinkfile after mentioning the number it will succeed even though if it has not shrunk your file.

in order to shrink log file active portion of lsn have to become inactive, this can be done by taking a full backup if simple mode or transaction log backup if in full\bulk recovery mode, once this is done, there is free space available in transaction log file. now this free space can be released to OS with dbcc shrinkfile.

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

Please read through this - Managing Transaction Logs[/url]

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

tjp8 SSCommitted Points: 1989 More actions · Answer 3

I have found that sometimes a log file in simple mode will shrink without a backup and sometimes it will not. It usually works for me to do the following

1. shrink the log file

2. run a Backup Trans log WITH TRUNCATE_ONLY

3. shrink the log file again

This usually gives the space back

TJP8

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 4

There is an active transaction and is still opened before completion of backup. For this reason it cann't make these transactions inactive.

jsb12 SSChasing Mays Points: 608 More actions · Answer 5

thank you for the help guys. Also the article was informative

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 6

tjp8 (1/22/2010)
I have found that sometimes a log file in simple mode will shrink without a backup and sometimes it will not. It usually works for me to do the following
1. shrink the log file
2. run a Backup Trans log WITH TRUNCATE_ONLY
3. shrink the log file again
This usually gives the space back
TJP8

You really need to forget that command. Using that command to truncate the transaction log breaks the log chain (when in full or bulk-logged recovery model). When in simple mode - it just performs a checkpoint operation.

The preferred method of shrinking a log file is to perform regular scheduled transaction log backups, monitor the usage and when the VLF's that are being used have rolled over to the beginning of the log file then you can shrink the file.

Now, the real question is why do you need to shrink the file? Only when you have an unusual event that is not ever going to occur again (for example: a data load when building the system). Once the log file has stabilized at a size you really should leave it alone.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs