Are you getting the following SQL Server error 9002 for SQL Database with the following descriptions?
The transaction log for database '%ls' is full due to '%ls'
or
The transaction log for database 'database_name' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
This article is a handy guide for troubleshooting and fixing the error. Before moving on, you need to install Azure Data Studio on your system to work with this article. You can install it on Windows, macOS, or Linux platforms.
Once you have Azure Data Studio on your system, click on the Azure Data Studio Microsoft docs notebook and click on the checkbox – Always allow Microsoft docs to open azure data studio links.
Again, Azure Data Studio asks permission to open this URI.
Click on Open, and it launches a guide to troubleshoot a full transaction log (SQL Server Error 9002).
Let's explore this notebook on how it will help resolve the SQL Server error 9002. Click on the attach to section and choose your SQL Server connection.
Scroll down in Azure notebook and check the What is preventing log truncation? It has the query that runs for all databases, returns their log space, and helps identify if a transaction log is not truncated and the reason for it.
The most common actions you can consider here are to review your database recovery model and backup your database's transaction log.
If the transaction log fails with LOG_BACKUP log_reuse_wait category, you might not be running regular transaction log backups. The database in the full and bulk-logged recovery model supports t-log backup. Therefore, consider reviewing if you need those recovery models. Else, switch to a simple recovery model.
Sometimes, due to an active transaction, the transaction logs growth occurs, and you require multiple transaction log backups for Database Engine to truncate the record to the point of the last backup.
As shown below, the notebook gave recommendations and a script to take log backup.
Note: You might have existing transaction log backup jobs in place. Therefore, you can execute the job manually to take a log backup as per our backup pattern.
You must take transaction log backups to allow log records to be released and the log truncated. If
The following section returns the database backup history for you to review existing full, diff, and transaction log backup for problematic databases.
The notebook also covers steps to identify long-running transactions or open transactions. You can review those transactions and kill them if required.
The notebook also contains a utility script for identifying log files that use a large percentage of disk space and suggest actions.
For example, it suggests shrinking the transaction log file or adding a new file to a new volume.
Conclusion
I hope that short guide (Reference: Microsoft docs) has helped troubleshoot this common SQL Server error 9002 about transaction log full. Unlike other errors, which indicate a problem with your SQL Server, Error 9002 shows that your database has reached its maximum log size. Thank you for reading, and hopefully, this blog has helped you troubleshoot this error.
Comments