locked
Shrinking of Transaction Log File RRS feed

  • Question

  • Hi,

    Could anyone of you please explain me about the pros and cons of Shrinking a Transaction Log File.

    Can I shrink the log file or not while the database is in production.

    What are the after effects when i shrink the log file in production and no log shipping or mirroring or replication is setup for this database.


    Regards,

    Sandesh Segu

    http://www.SansSQL.com

    SansSQL

    ↑ Grab this Headline Animator

    Monday, December 6, 2010 4:49 AM

Answers

  • Hi Sandesh,

    Shrinking of Transaction Logs is a topic which interests all DBAs. You in your question try to fill Ocean in a bucket. I will try my level best to answer it as much as I can:

    As per Books On Line:

    Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up. However, truncation can be delayed by a number of factors.

    1) Log truncation frees space in the log file for reuse by the transaction log. Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time.

    2) Log records can remain active under a variety of conditions. You can discover what, if anything, is preventing log truncation by using the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.

    3) Log truncation cannot happen during any backup or restore operation. In SQL Server 2005 and later versions, log backups can occur during a data backup. However, log truncation cannot occur during such log backups, because all of the transaction log must remain available to the data backup operation. If a data backup is preventing log truncation, canceling the backup might help the immediate problem.

    4) An active transaction requires that the log remain active from the log record that contains the start of the transaction. For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user. In these cases, although the waiting transaction generates very little log itself, the transaction holds up log truncation and causes the log to grow large.

    Specific to Database Mirroring & Replication (although as you mentioned you don't have this enabled in your case)

    5) Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.

    6) Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs or setting it run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.

    Additionally, if you have set the option 'sync with backup' on the publication database or distribution database, the transaction log is not truncated until all transactions have been backed up. If the transaction log is growing too large, and you have this option set, consider shortening the interval between transaction log backups.

    Now coming back to Truncation of Transaction Logs:

    If you know that a transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file.

    Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

    Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file. For example, if you have a 600 megabyte (MB) log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but the file cannot be reduced to sizes such as 433 MB or 525 MB. A virtual log file that holds any active log records, that is, an active virtual log file, is part of the logical log, and it cannot be removed.

    More about this concept can be read from Books On Line link below:

    http://technet.microsoft.com/en-us/library/ms189085.aspx

    http://technet.microsoft.com/en-us/library/ms189085(SQL.90).aspx

     

    Best Practicies for Shrinking

    Consider the following information when you plan to shrink a database or file:

    • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

    • Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

    • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.

    • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

     

    Below are some other good readings about Best Practices:

    http://sqlserver-training.com/sql-server-transaction-log-operational-best-practice/-

    http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

    HTH

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    • Marked as answer by Sandesh Segu Wednesday, December 8, 2010 8:26 AM
    Monday, December 6, 2010 5:30 AM
  • In addition

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Sandesh Segu Wednesday, December 8, 2010 8:26 AM
    Monday, December 6, 2010 10:34 AM
    Answerer

All replies

  • Hi Sandesh,

    Shrinking of Transaction Logs is a topic which interests all DBAs. You in your question try to fill Ocean in a bucket. I will try my level best to answer it as much as I can:

    As per Books On Line:

    Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up. However, truncation can be delayed by a number of factors.

    1) Log truncation frees space in the log file for reuse by the transaction log. Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time.

    2) Log records can remain active under a variety of conditions. You can discover what, if anything, is preventing log truncation by using the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.

    3) Log truncation cannot happen during any backup or restore operation. In SQL Server 2005 and later versions, log backups can occur during a data backup. However, log truncation cannot occur during such log backups, because all of the transaction log must remain available to the data backup operation. If a data backup is preventing log truncation, canceling the backup might help the immediate problem.

    4) An active transaction requires that the log remain active from the log record that contains the start of the transaction. For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user. In these cases, although the waiting transaction generates very little log itself, the transaction holds up log truncation and causes the log to grow large.

    Specific to Database Mirroring & Replication (although as you mentioned you don't have this enabled in your case)

    5) Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.

    6) Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database. If the transaction log is growing too large, and the Log Reader Agent is running on a scheduled basis, consider shortening the interval between runs or setting it run in continuous mode. If it is set to run in continuous mode (the default), ensure that it is running.

    Additionally, if you have set the option 'sync with backup' on the publication database or distribution database, the transaction log is not truncated until all transactions have been backed up. If the transaction log is growing too large, and you have this option set, consider shortening the interval between transaction log backups.

    Now coming back to Truncation of Transaction Logs:

    If you know that a transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file.

    Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

    Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file. For example, if you have a 600 megabyte (MB) log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but the file cannot be reduced to sizes such as 433 MB or 525 MB. A virtual log file that holds any active log records, that is, an active virtual log file, is part of the logical log, and it cannot be removed.

    More about this concept can be read from Books On Line link below:

    http://technet.microsoft.com/en-us/library/ms189085.aspx

    http://technet.microsoft.com/en-us/library/ms189085(SQL.90).aspx

     

    Best Practicies for Shrinking

    Consider the following information when you plan to shrink a database or file:

    • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

    • Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

    • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.

    • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

     

    Below are some other good readings about Best Practices:

    http://sqlserver-training.com/sql-server-transaction-log-operational-best-practice/-

    http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

    HTH

     


    Regards Gursethi Blog: http://ms-gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    • Marked as answer by Sandesh Segu Wednesday, December 8, 2010 8:26 AM
    Monday, December 6, 2010 5:30 AM
  • In addition

    http://www.karaszi.com/SQLServer/info_dont_shrink.asp


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Sandesh Segu Wednesday, December 8, 2010 8:26 AM
    Monday, December 6, 2010 10:34 AM
    Answerer