Troubleshoot a Full Transaction Log (SQL Server Error 9002)

Applies To: SQL Server 2016
This topic discusses possible responses to a full transaction log and suggests how to avoid it in the future.
When the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online, or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated. If the log fills during recovery, the Database Engine marks the database as RESOURCE PENDING. In either case, user action is required to make log space available.

Responding to a full transaction log

The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill.
To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. For more information, see sys.databases (Transact-SQL). For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).

IMPORTANT!!
If the database was in recovery when the 9002 error occurred, after resolving the problem, recover the database by using ALTER DATABASE database_name SET ONLINE.

Alternatives for responding to a full transaction log include:

• Backing up the log.
• Freeing disk space so that the log can automatically grow.
• Moving the log file to a disk drive with sufficient space.
• Increasing the size of a log file.
• Adding a log file on a different disk.
• Completing or killing a long-running transaction.

These alternatives are discussed in the following sections. Choose a response that fits your situation best.

Back up the log

Under the full recovery model or bulk-logged recovery model, if the transaction log has not been backed up recently, backup might be what is preventing log truncation. If the log has never been backed up, you must create two log backups to permit the Database Engine to truncate the log to the point of the last backup. Truncating the log frees space for new log records. To keep the log from filling up again, take log backups frequently.
To create a transaction log backup

IMPORTANT
If the database is damaged, see Tail-Log Backups (SQL Server).

• Back Up a Transaction Log (SQL Server)
• SqlBackup (SMO)

Freeing disk space

You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. The freed disk space allows the recovery system to enlarge the log file automatically.

Move the log file to a different disk

If you cannot free enough disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space.

IMPORTANT!! Log files should never be placed on compressed file systems.

Move a log file

• Move Database Files

Increase log file size

If space is available on the log disk, you can increase the size of the log file. The maximum size for log files is two terabytes (TB) per log file.
Increase the file size
If autogrow is disabled, the database is online, and sufficient space is available on the disk, either:

• Manually increase the file size to produce a single growth increment.
• Turn on autogrow by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.

NOTE In either case, if the current size limit has been reached, increase the MAXSIZE value.

Add a log file on a different disk

Add a new log file to the database on a different disk that has sufficient space by using ALTER DATABASE ADD LOG FILE.
Add a log file

• Add Data or Log Files to a Database

Complete or kill a long-running transaction

Discovering long-running transactions

A very long-running transaction can cause the transaction log to fill. To look for long-running transactions, use one of the following:

• sys.dm_tran_database_transactions. This dynamic management view returns information about transactions at the database level. For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).
• DBCC OPENTRAN. This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back).

Kill a transaction

Sometimes you just have to end the process; you may have to use the KILL statement. Please use this statement very carefully, especially when critical processes are running that you don't want to kill. For more information, see KILL (Transact-SQL)

Manage the Size of the Transaction Log File

Applies To: SQL Server 2016
This topic contains information about how to monitor the size of a SQL Server transaction log, shrink the transaction log, add or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

Monitor log space use

Monitor log space use by using DBCC SQLPERF (LOGSPACE). This command returns information about the amount of log space currently used, and indicates when the transaction log needs truncation. For more information, see DBCC SQLPERF (Transact-SQL). For information about the current size of a log file, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files. For more information, see sys.database_files (Transact-SQL).
Important! Avoid overloading the log disk!

Shrink log file size

To reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space you will not need.You can shrink a log file can occur only while the database is online and at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

Note
Factors, such as a long-running transaction, that keep virtual log files active for an extended period can restrict log shrinkage or even prevent the log from shrinking at all. For information about factors that can delay log truncation, see The Transaction Log (SQL Server).

Shrinking a log file removes one or more virtual log files that hold no part of the logical log (that is, inactive virtual log files). When a transaction log file is shrunk, enough inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size.
Shrink a log file (without shrinking database files)

• DBCC SHRINKFILE (Transact-SQL)
• Shrink a File

Monitor log-file shrink events

• Log File Auto Shrink Event Class.

Monitor log space

• DBCC SQLPERF (Transact-SQL)
• sys.database_files (Transact-SQL) (See the size, max_size, and growth columns for the log file or files.)

Note

Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger. For information about changing the setting of the autoshrink property, see View or Change the Properties of a Database—use the Auto Shrink property on the Options page—or ALTER DATABASE SET Options (Transact-SQL)—use the AUTO_SHRINK option.

Add or enlarge a log file

Alternatively, you can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk.

• To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. Adding a log file allows the log to grow.
• To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. For more information, see ALTER DATABASE (Transact-SQL).

Optimize tempdb transaction log size

Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. This can reduce the performance of the tempdb transaction log. You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. For more information, see tempdb Database.

Control transaction log file growth

You can use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file. Note the following:

• To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
• To change the growth increment, use the FILEGROWTH option. A value of 0 indicates that automatic growth is set to off and no additional space is permitted. A small autogrowth increment on a log file can reduce performance. The file growth increment on a log file should be sufficiently large to avoid frequent expansion. The default growth increment of 10 percent is generally suitable.

For information on changing the file-growth property on a log file, see ALTER DATABASE (Transact-SQL).

• To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

Waiting a long time when "searching for updates" in AX 2012 R3 Installation

When installing the AX 2012 R3 it is getting stuck on searching updates. I have waited for more than 2 hours on the same screen.




Solution:

I read a blog in AX community one of the solutions says stop the Windows Update service. I tried it, it works.