Tags

, , , , , ,


Today I received an alert in one of the Production database Server for T-Log File full >90%. This is one of the critical database in my company and the Recovery Model of the database is FULL.

I checked if the TLog backup job is running on the server. It was working fine. I triggered the Tlog Backup job once more to take the Tlog Backup and truncate the Tlog File, the backup job ran successfully. I checked the percentage of Tlog usage by the below command:

DBCC SQLPERF(LOGSPACE)

So the TLOG was not truncated by the Backup.

Huh!! Nice start of the weekend !!

Recently, CDC was enabled in this database. So my next step was to check Log_Reuse_Wait_Desc in sys.databases

SELECT name,recovery_model_desc,log_reuse_wait_desc,is_cdc_enabled FROM SYS.DATABASES

 

We never had REPLICATION on this database. Then I checked to see the active transactions on the database:

DBCC OPENTRAN

Oldest active transaction:
SPID (server process ID): 461
UID (user ID) : -1
Name : user_transaction
LSN : (190294:3758708:1)
Start time : Dec 17 2011 6:29:08:810AM
SID : 0x01050000000000051500000029f32b7099d078b448d6060059040000

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (190181:4701335:41)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So my Tlog was not truncating because of Oldest non-distributed LSN.

Actually, The Change Data Capture feature (CDC) uses the transaction log and the Log Reader Agent job in much the same way as transactional replication. Hence, if the CDC jobs are not running, the log space cannot be reused, much as if there was transactional replication with the Log Reader Agent job not running. The log reuse wait description still shows Replication.

To get rid of this problem and to free the Tlog we need to execute the below command:

USE [Database_Name]
GO
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1
Lets again check the log_reuse_wait:
SELECT name,recovery_model_desc,log_reuse_wait_desc FROM SYS.DATABASES
Now, lets check the Tlog percentage use:
DBCC SQLPERF(LOGSPACE)
So we can see now that our TLog has been truncated.
Conclusion:

The reason why the log is not getting truncated is because it is waiting for the log reader to capture for CDC to make it inactive portion of the log. In our case we need to enable the clean-up and capture job of the CDC in server.
USE [database_name]
GO
EXEC sys.sp_cdc_add_job ‘capture’
GO
EXEC sys.sp_cdc_add_job ‘cleanup’
GO
Cheers,
Subhro Saha
Advertisements