If you are running SQL Server database engine, then you might be experiencing a database error 9002 – The transaction log for database <DBName> is full due to 'Replication.' error. The database error 9002 is an error that appears in the database server logs. In this article, I will describe how this error can be resolved.
Recently I faced Error 9002 in shrinking log file in one of the databases where the SQL transaction log file is full and makes database out of space.
Checking log_reuse_wait_desc shows the state of "REPLICATION" before the log can be shrunk or reused.
In my environment, the database doesn't have any replication enabled. It's confusing why it's showing status as waiting on REPLICATION.
To troubleshoot the error, we need to check whether the database is part of REPLICATION (Subscriber, Publisher) or Change Data Capture(CDC) using the following query
Script 1: Check Database status in Replication or CDC
SELECT
Case [is_published]
When 1 Then 'Database is Publisher in the Replication'
Else 'Database is not a publisher'
End as PublisherStatus
,
CASE [is_subscribed]
When 1 Then 'Database is Subscriber in the Replication'
Else 'Database is not a Subscriber'
End as SubscriberStatus
,
Case [is_cdc_enabled]
When 1 then 'CDC is enabled'
else 'CDC is not enabled'
End as CDCStatus
FROM sys.databases
WHERE name = 'DBBlogger'
As shown below, my database is not part of Replication (Publisher, Subscriber) or Change Data Capture (CDC).
If the CDC is enabled, further check the status of the CDC capture job (Job name- CDC.<DBName>_Capture) is in a failed state. The CDC job failure causes an issue in log truncation.
Check the job history and troubleshoot it to ensure it's working fine. Once the job status is successful, you can shrink the database log file to reclaim its space.
Script 2: Shrink Database log file
DECLARE @SQL VARCHAR(MAX);
SET @SQL = '';
SELECT
@SQL = @SQL +
'USE ['+ sd.name +']; CHECKPOINT; DBCC SHRINKFILE ('+f.name+');'
FROM sys.master_files f
INNER JOIN sys.databases sd
ON sd.database_id = f.database_id
WHERE f.type = 1 /*Shrink log file*/
AND sd.name = 'DatabaseName'
SELECT @SQL SQL
EXEC (@SQL)Replicate scenario
Let us replicate the scenario to understand it better. I have database 'dBblogger' restored from the AdventureWorks sample database from Microsoft.
Note: Adventureworks sample database is available from Microsoft. Reference Link below:
Run the below command to enable the CDC feature in the database.
Use DBblogger
Go
EXEC sys.sp_cdc_enable_dbNow, check the status of CDC in sys.databases using following script.
Select is_cdc_enabled,log_reuse_wait_desc,name from sys.databases where name=’DBblogger’
Run below statement to enable CDC feature in a particular table. I am using the SalesOrderDetail table in the Sales schema.
Use DBblogger
Go
EXEX sys.sp_cdc_enable_table
@source_schema=N’sales’,
@source_name=N’SalesOrderDetail’,
@role_name= NULL,
@filegroup_name=NULL,
@supports_net_changes=0
GO
It starts the CDC jobs - Capture and Cleanup in SQL agent.
Now for testing purposes, disable and stop the CDC.DBblogger_capture job.
Run some update statement statements in the table
When I check the status in the database, it shows waiting for REPLICATION in the log_reuse_wait_desc column in sys.databases.
Now, if the transaction log is filled with multiple statements, It generates SQL Server error code 9002
Even Alter statement in the database failed due to a 9002 error.
Enable the CDC.DBBlogger_Capture agent job, and make sure it is successful.
The log_resuse_wait_desc column status shows NOTHING, as shown below.
You can shrink the database file using the following DBCC SHRINKFILE statement (Script 2).