locked
WSS_Content_log.ldf is too big and Shrink command caused to increase fruther RRS feed

  • Question

  • I looked all over the web regarding large wss_content_log.ldf file and the general recommendation was to shrink it. My current wss_content_log.ldf is 88 GB.

    I logged in to SQL 2005 Management Studio expanded Databases, right clicked on Tasks>Shrink>Files. In Shrink windows properties I changed File Type to "log" and it displayed the log files info as follow:

    Currently Allocated Space: 88123.12 MB

    Available Free Disk Space: 54123.00 (6%)

    In the Shrink action I selected “release unused space” and clicked on the OK button.  Once operation was completed the log file increase to 92 GB.

    Did I do something wrong????

    Thanks…B

    Saturday, September 17, 2011 8:57 PM

Answers

All replies

  • Does this help ?

    http://support.microsoft.com/kb/907511


    Steve Thomas
    • Edited by Thomas,Steve Sunday, September 18, 2011 4:19 AM
    • Marked as answer by Pengyu Zhao Thursday, September 29, 2011 2:15 AM
    Sunday, September 18, 2011 4:19 AM
  • Hi Bluem.

    When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size. This log backup operation is in addition to the three steps that you perform to shrink the transaction log file in SQL Server 2000. For more information, see the Microsoft Knowledge Base article that is mentioned in the "Summary" section. To shrink a transaction log file that has little free space in SQL Server 2005, follow these steps:
    1. Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, start SQL Server Management Studio and then run a Transact-SQL statement that resembles the following Transact-SQL statement. 
      BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'
      Note In this statement, <var><DatabaseName></var> is a placeholder for the name of the database that you are backing up, and<var><BackupFile></var> is a placeholder for the full path of the backup file.

      For example, run the following Transact-SQL statement.
      BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'
    2. Shrink the transaction log file. To do this, run a Transact-SQL statement that resembles the following Transact-SQL statement.
      DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
      Note In this statement, <var><FileName></var> is a placeholder for the name of the transaction log file, and <var><TargetSize></var> is a placeholder for the target size that you want the transaction log file to be. The target size must be reasonable. For example, you cannot shrink the transaction log file to a size that is less than 2 virtual log files.
    3. If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive.
    4. Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be close to the target size.
    In summary, the log manager's algorithm for obtaining the next virtual log file changed in SQL Server 2005. Therefore, shrinking the transaction log file in SQL Server 2005 may differ from shrinking the transaction log file in SQL Server 2000. 
    • If a log file has lots of free space, shrinking the transaction log file in SQL Server 2005 is faster than shrinking the transaction log file in SQL Server 2000.
    • If a log file has no free space, shrinking the transaction log file in SQL Server 2005 is the same as shrinking the transaction log file in SQL Server 2000.
    • If a log file has little free space, you may have to perform an additional log backup operation in SQL Server 2005 than you have to perform in SQL Server 2000.

    Thanks,

    Raghu

    • Proposed as answer by DawnYu Monday, December 16, 2013 5:39 AM
    Tuesday, December 10, 2013 2:29 PM