Dec
27
2012

Size of Transaction Log Increasing and cannot be truncated or Shrinked, log_reuse_wait_desc = replication

Today while troubleshooting log full issue, came across following scenario where two sql servers are in different domain and both are part of simple transactional replication where log file full was on subscriber, after investigation found that replication is working fine and no transaction are in undistributed state still log file is not shrinking, we tried changing recovery model to simple, etc but still issue didn’t get resolved. Finally we manually executed script to mark undistributed transactions as distributed to release space after full backup. Please find troubleshooting steps and solution below.

First of all run following to identify the wait type of log reuse,

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases

In my case it showed “Replication” for my database, then I began below troubleshooting.

Troubleshooting steps:

1. Run DBCC OPENTRAN, it will show results as,

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (xxxxx:xxx:x)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Even when I close the query and a few hours later run the same DBCC OPENTRAN command, the results are the same.

It means due to undistributed replication transactions log file is growing.

 

2. Run following query against distributor,

select databasepropertyex(‘distribution’,
‘IsSyncWithBackup’)

 

If output is 0, then check log reader agent, it should be running. If output is 1 then ensure log reader is running and log backups is scheduled every hour to suppress log growth else log at subscriber will grow until backup succeeds

 

3. Run following query against your publisher database,

select databasepropertyex(‘your pub db’,
‘IsSyncWithBackup’)

 

If output is 0, then check log reader agent, it should be running. If output is 1 then ensure log reader is running and log backups is scheduled every hour to suppress log growth else log at subscriber will grow until backup succeeds

 

4. Change recovery model of database to simple (in our case issues is with subscriber hence changing recovery model is allowed and have less impact)

 

5. If above also doesn’t release space, then initiate a full backup (in our case it did the trick, not sure why but it released log file and cleared space)

 

6. If all above tips doesn’t solve issue, then do following (ensure that replication is working fine, there is no latency and log reader is clean with no errors and all transactions are distributed by distributor as if you execute below there are chances that you may lose un-distributed transactions and may have to reinitialize replication),

 

Use [Your subscriber database]

Go

Execute sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

GO

 

–In short this also can be executed —exec sp_repldone null, null, 0,0,1

 

This query will mark all pending transactions as completed in blocked log file thus releasing log space. Initiate full backup of database immediately to release space.

 

A good explanation of issue is give on: http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

 

 

Additional errors you may see in SQL error log:

Error: 9002, Severity: 17, State: 2

The transaction log for database ‘mydatabase’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

 

Queries which may come handy when checking if transaction is still distributed or not,

Source: http://www.replicationanswers.com/Transactional.asp

 

How can I write a script to show which commands are waiting to be downloaded?

To find numerical summaries, there is the view MSdistribution_status and the system stored procedure sp_replmonitorsubscriptionpendingcmds. To read the actual commands there is the proc sp_browsereplcmds. This will return all commands but takes an optional parameter @xact_seqno_start. In the script below I show how this parameter can be used to get pending commands, once the blue parameter values are replaced.

 

declare @xact_seqno varbinary(16)

select @xact_seqno = max(xact_seqno)

from MSsubscriptions

inner join MSpublications

on MSpublications.publication_id = MSsubscriptions.publication_id

inner join MSdistribution_history

on MSdistribution_history.agent_id = MSsubscriptions.agent_id

Where subscriber_db = ‘testsub’

AND Publication = ‘testtrans’

 

declare @str varchar(255)

set @str = master.dbo.fn_varbintohexstr (@xact_seqno)

set @str = left(@str, len(@str) – 8)

 

if exists(select object_id(‘tempdb..#trancommands’)) drop table #trancommands

 

create table #trancommands

(xact_seqno varbinary(16) null,

originator_srvname sysname null,

originator_db sysname null,

article_id int null,

type int null,

partial_command bit null,

hashkey int null,

originator_publication_id int null,

originator_db_version int null,

originator_lsn varbinary(16) null,

command nvarchar(1024) null,

command_id int)

 

insert into #trancommands

exec sp_browsereplcmds @xact_seqno_start = @str

select * from #trancommands where xact_seqno > @xact_seqno

 

 

Useful Resources:

http://msdn.microsoft.com/en-us/library/ms152548.aspx — Replication administration

http://www.sqlservercentral.com/articles/Transaction+Logs/72488/ –T-log architecture

http://msdn.microsoft.com/en-us/library/ms345414.aspx –explanation of log_reuse_wait_desc output (factors that can delay log truncation)

http://www.sqlservercentral.com/Forums/Topic1224280-1550-2.aspx

http://www.sqlservercentral.com/Forums/Topic1074808-391-1.aspx#bm1138238

http://stackoverflow.com/questions/12542089/how-to-close-a-sql-server-2008-transaction

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b/

http://saveadba.blogspot.ca/2012/01/error-9002-severity-17-state-2.html

http://mssqlwiki.com/2012/05/18/transaction-log-for-the-database-is-growing-and-system-spid-is-holding-open-transaction/

http://blogs.msdn.com/b/sqlsakthi/archive/2012/03/14/re-configure-replication-after-restoring-publication-database-from-backup-replication-scenario.aspx -publication database restored from old date causing replication failure


 

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

3 Comments + Add Comment

  • How do you recommend that I “check log reader agent”?

    • Yes, it is one important piece to be checked in such cases as it is responsible for monitoring the transaction log of each database configured for transactional replication, in our case it was found to be running. As I said, our case was a bit different as we Pub and subscriber are in two different forests so we keep getting this issue and solution is to check match the records and then manually mark them replicated.

  • Awesome post.

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 108 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

View Post by Categories

%d bloggers like this: