How to manage SQL Server transaction log file size

5 min read

Table of Contents

Case #

You have a SQL Server instance with at least one database with full recovery model. At some during database operations you come across the following error either in the SQL Server event log or in the Windows system event log or even in other utility applications, such as any SQL Server backup application. The transaction log for database 'database name' is full due to 'LOG_BACKUP'. (Microsoft SQL Server Error 9002).

sql transaction log error 9002

Solution #

SQL transaction log best practices #

SQL Server Error 9002 is a typical error when the SQL Server transaction log of any database becomes full or if the latest transaction log backup is missing or if there is a broken SQL log shipping configuration. This can happen unless you follow the below best practices for SQL Server transaction log management:

  • Regularly backup the SQL Server transaction log of any database which has a full recovery model. Taking transaction log backups ensures that the SQL Server engine automatically truncates the transaction log immediately afterwards. Depending on the size and growth rate of your transaction log (this heavily depends on your software application behavior), you may need to take less or more frequent transaction log backups during the day. Remember that the transaction log file size will continue to grow (if set to autogrow, i.e. unlimited maximum limit) until it is truncated.
  • Ensure that the disk on which the transaction log file is located always has sufficient space. In case this is possible, an alternative is to plan for moving a transaction log file to another disk.
  • The transaction log file (.ldf) should be configured to automatically grow until a certain storage (unlimited option practically means approx. 2 TB space). It is recommended to not place any maximum limits to the transaction log file size. You can use the MODIFY FILE clause in the ALTER DATABASE T-SQL command to do this. Then you can define the specific SIZE and MAXSIZE for the database transaction log file in question. This cannot be set if you have an active SQL 9002 error. See below for more details on resolution of this error.
  • Capacity of a transaction log file can be increased by adding a second .ldf file as a second transaction log file for the same database. For this, you can run the ADD FILE clause in the ALTER DATABASE T-SQL command. This allows to add an additional .ldf file which in turns then allows to increase the log file.
  • Sometimes there can be long-running SQL transactions which need to be completed first or even be forcefully killed in order for the transaction log size to return to normal.

How to resolve SQL Server error 9002 #

Firstly, let's clarify the concepts of transaction log truncation and shrinking. Log truncation occurs during a transaction log backup. Truncation is the operation which removes all committed records inside the transaction log. Log shrinking on the other hand is an operation which reclaims available physical space on the file system by reducing the file size. Log truncation occurs on a virtual-log-file (VLF) boundary. A transaction log file can contain more than one VLFs. A log file can be shrunk only if there's empty space inside the log file to reclaim. Shrinking a log file alone can't solve the problem of a full log file. Instead, you must discover why the log file is full and can't be truncated. Also take into account that data which is moved during transaction log shrink operations can cause index fragmentation and therefore slow down the performance of SQL queries on that database. Therefore SQL transaction log file shrinking should always be followed by index rebuilding. For more details, review the Microsoft article Shrink a database.

To resolve error 9002, carry out the steps below.

  • First off, you need to ensure that the latest SQL transaction log backup has been taken successfully. When this  happens, the transaction log will be truncated and free space will be available again. You can either take a transaction log backup manually from SQL Server Management Studio or by running T-SQL commands, or by using any third party SQL Server backup software. To manually perform a transaction log truncation operation in SQL Server Management Studio carry out the steps below.
    • Right-click the desired SQL database, then select Properties, Options.
    • Set the database recovery model to Simple and exit.
    • Right-click the same database again and select Tasks, Shrink, Files.
    • Change the type field to Log.
    • In the Shrink action, tick the "Reorganize pages before releasing unused space" option and click OK.When this procedure is successfully completed, you should change the database recovery model back to Full or Bulk-Logged and then take a full database backup.
  • If you are having issues with transaction log file truncation, then it is imperative to identify these issues and determine the root cause of transaction log file truncation issues. Run the following T-SQL command, to check if there any issues with your transaction log file truncation and to discover the root cause of truncation issues.   select name, log_reuse_wait_desc  from sys.databases. Make use of the more complex T-SQL script provided by Microsoft to gather more details on this matter: https://learn.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017.

References #

sql server, transaction log
What are your Feelings

Powered by BetterDocs