Post reply

truncating transaction log - not shrinking after backup

  • May 10, 2006 at 5:22 pm

    #172439

    We are backing up the transaction log and I am trying to run a DBCC SHRINKFILE (2, 'TRUNCATEONLY') and it is not working.

    When I do it through Enterprise Manager it says it is shrinking but then when I go to the file I see that size is the same. So neither through Enterprise Manager or through TSQL I am able to shrink it.

    Any ideas?

    Thanks.

  • May 10, 2006 at 6:50 pm

    #637139

    Try executing the command DBCC SQLPERF(LOGSPACE) to determine what percentage of the log file is used. If too high, then you can't really shrink it down much further. If the value is low then try:

    USE <database name>

    DBCC SHRINKFILE(2,<required space in MB&gt

    Try this a couple of times to see whether it reduces the size of the physical log file. You may need to change the <required space in MB> to make it happen.

    Remember that the 'active' portion of the log within the virtual log files cannot be removed. When you perform a backup of the log/database, the 'active' portion should reduce, unless if you have a long running transaction.

    Frequent backups of the transaction log is a good way to keep its size in check.

  • May 10, 2006 at 7:01 pm

    #637140

    Thanks. Yes that is what we are shrinking the log after the back up. But now I am able to shrink it and hence recover the space from the unactive section of the file. For 4 hours I have been trying and it was like if it was not accepting my command.

     

Viewing 3 posts - 1 through 2 (of 2 total)

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