Clear the SQL Server transaction log and fix the 9002 error

How to clear the SQL Server transaction log and fix the error 9002?

It is time to clear the transaction log when a SQL Server database is slow, or the available disk space is getting abnormally small. Indeed, it is sometimes enough to clear the SQL Server transaction log and reduce the files in the SQL Server database. Often, during this operation, this message appears : The database transaction log is full. Or Check the log_reuse_wait column of sys.databases.

Clear SQL Server transaction log and manage the 9002 error

Before emptying the logs, inspect the data in the sys.databases system view of the database and locate the table that is not in the NOTHING state. To avoid the error “The database transaction log is full” and to shrink the files, it is necessary to execute two commands, shrink database and then shrink file. Indeed the full detailed error message looks like this one:

Microsoft SQL Server Error 9002 : The transaction log for database YOUR-DB is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc COLUMN IN SYS.DATABASES (#9002)

Run SHRINKDATABASE to clear the transaction log

In addition, if trying to run a DBCC SHRINKDATABASE script like the one below. So, run the SHRINKDATABASE command and replace DATABASE_EXAMPLE with the name of the database file. The command will reduce the size of the database on the disk. Adapt the path with the file, stored in a folder of this type, ideally on a dedicated disk: D:\SQL_DATA\DBFILE\

USE DATABASE_EXAMPLE;

DBCC SHRINKDATABASE(N'DATABASE_EXAMPLE', 10, TRUNCATEONLY);

In my example, by default the database name looks like this DATABASE_EXAMPLE.mdf. Note that a SQL Server database main file is a MDF file for Master Database File.

Run SHRINKFILE to reduce the database files size

Then use the SHRINKFILE command to replace DATABASE_EXAMPLE with the name of the SQL Server log file, for example in a dedicated folder like this one D:\SQL_DATA\DB\DBLOG\. In my example, by default the log file name is like this DATABASE_EXAMPLE_log.ldf. It’s a LDF file, for Log Database File.

ALTER DATABASE DWH SET RECOVERY SIMPLE;

DBCC SHRINKFILE ('DATABASE_EXAMPLE_log', 1);

ALTER DATABASE DATABASE_EXAMPLE SET RECOVERY FULL;

If an error of this type occurs when running one of the commands:

The database transaction log is full. Check the log_reuse_wait_desc column in sys.databases.

Then check the active transactions from the log_reuse_wait column. To do so, run this query in SQL Server Management Studio.

SELECT [name]
,[database_id]
,[source_database_id]
,[owner_sid]
,[create_date]
,[compatibility_level]
,[collation_name]
,[user_access]
,[user_access_desc]
,[is_read_only]
,[is_auto_close_on]
,[is_auto_shrink_on]
,[state]
,[state_desc]
,[is_in_standby]
,[is_cleanly_shutdown]
,[is_supplemental_logging_enabled]
,[snapshot_isolation_state]
,[snapshot_isolation_state_desc]
,[is_read_committed_snapshot_on]
,[recovery_model]
,[recovery_model_desc]
,[page_verify_option]
,[page_verify_option_desc]
,[is_auto_create_stats_on]
,[is_auto_update_stats_on]
,[is_auto_update_stats_async_on]
,[is_ansi_null_default_on]
,[is_ansi_nulls_on]
,[is_ansi_padding_on]
,[is_ansi_warnings_on]
,[is_arithabort_on]
,[is_concat_null_yields_null_on]
,[is_numeric_roundabort_on]
,[is_quoted_identifier_on]
,[is_recursive_triggers_on]
,[is_cursor_close_on_commit_on]
,[is_local_cursor_default]
,[is_fulltext_enabled]
,[is_trustworthy_on]
,[is_db_chaining_on]
,[is_parameterization_forced]
,[is_master_key_encrypted_by_server]
,[is_published]
,[is_subscribed]
,[is_merge_published]
,[is_distributor]
,[is_sync_with_backup]
,[service_broker_guid]
,[is_broker_enabled]
,[log_reuse_wait]
,[log_reuse_wait_desc]
,[is_date_correlation_on]
,[is_cdc_enabled]
,[is_encrypted]
,[is_honor_broker_priority_on]
FROM [tempdb].[sys].[databases]

Then inspect the databases corresponding to the rows with the status ACTIVE_TRANSACTION in the log_reuse_wait_desc column. Furthermore, it is necessary to run the SHRINKFILE and SHRINKDATABASE script on the database(s) in question to unblock the status and return it to NOTHING.

Finally, after emptying the SQL Server transaction log, it is possible to reduce the database without any problem. Thus, this article shows the procedure to completely delete a useless SQL Server database.

Reducing logs is not always a good idea for performance. Indeed, the relational database management system, or RDBMS, fragment the data after the database reduction. And when rebuilding the indexes, they will take up all the necessary space. That’s it for this post on how to fix the common SQL Server 9002 error, well known by DBAs.

Cannot drop the database because it is currently in use