Empty the SQL Server transaction log and fix error 9002

How to empty the SQL Server transaction log and fix the error 9002 ? Microsoft SQL Server database is slow, or the available disk space is getting abnormally small? To solve this problem, it is sometimes enough to empty 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.”.

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.

How to empty the SQL Server transaction log and manage the 9002 error ?

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 the SHRINKDATABASE command 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 the SHRINKFILE command to reduce 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.

To view the log_reuse_wait column, 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.

Note: 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.

This post shows how to fix the common SQL Server 9002 error, well known by DBAs.

How to clear the transaction log with SQL Server ?

To clear the SQL Server transaction log and avoid error 9002, use the commands DBCC SHRINKDATABASE and DBCC SHRINKFILE. To reduce the database and the associated files respectively.

Be the first to comment

Leave a Reply