SQL Server Error 9002 – The transaction log for database <DBName> is full due to 'Replication.'

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.

SQL Server Error 9002

Checking log_reuse_wait_desc shows the state of "REPLICATION" before the log can be shrunk or reused.

Check if CDC is enabled for SQL Database

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).


Check CDC or Transaction Replciation SQL Server

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.

Change Data Capture (CDC) Jobs

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:


https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms


Run the below command to enable the CDC feature in the database.


Use DBblogger
Go
EXEC sys.sp_cdc_enable_db

Now, 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’
CDC status

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

Configure CDC for a table

It starts the CDC jobs - Capture and Cleanup in SQL agent.

Change Data Capture Jobs

Now for testing purposes, disable and stop the CDC.DBblogger_capture job.


SQL Server agent jobs

Run some update statement statements in the table


SQL Update statement

When I check the status in the database, it shows waiting for REPLICATION in the log_reuse_wait_desc column in sys.databases.


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


SQL Server error code 9002 - Transaction log for database is full

Even Alter statement in the database failed due to a 9002 error.

Alter statement Error 9002

Enable the CDC.DBBlogger_Capture agent job, and make sure it is successful.


CDC.DBBlogger_Capture job

The log_resuse_wait_desc column status shows NOTHING, as shown below.


NOTHING status in sys.dataases

You can shrink the database file using the following DBCC SHRINKFILE statement (Script 2).

31 views0 comments