SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Process to truncate transaction log of replicated database


Process to truncate transaction log of replicated database

Author
Message
Basit Farooq
Basit Farooq
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 818
Comments posted to this topic are about the item Process to truncate transaction log of replicated database

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 740
It's one of those areas where you need to have extra vigilance over the management of your transaction logs. Change Data Capture can have the same effect as the Log Reader agent is used to capture changes to your database.

Though the steps taken in the article will help clear the transaction log, I would have liked some mention on what steps can be taken to avoid such situations arising. Granted, if you only got a handful of MB's left on your drive you need to take quick decisive action, but it would be better to set up alerts so you would be notified that there is latency between the transactions on the publisher being applied to the subscriber. That should given you more time to troubleshoot the issues with the log reader agent and hopefully resolve those issues first.

Reinitialising the subscription and taking a new snapshot can be a very intensive process that can have significant implications on performance so use this with caution.
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 740
Sorry I've just forgotten to mention something...

One of your steps is to clear the transaction log using BACKUP LOG ... WITH TRUNCATE_ONLY. That shouldn't be necessary as the committed transactions should be cleared once replication has be removed. If it doesn't then a straightforward backup of the log should clear it all out.

By using the TRUNCATE_ONLY option you are breaking the backup chain which will only be initialised again with a full or differential backup. Therefore, after you've reinitialised replication your log file will continue to grow until a successful log backup can be taken (which will only be after one of the aforementioned backups have taken place).
Basit Farooq
Basit Farooq
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 818
Hi Phil,

Thanks for your comments. We are backing up the transaction log every 15 minutes. This article has been written for particular situation that is mention below:

According to SQL Server BOL (http://support.microsoft.com/kb/317375):

“These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them. If an issue with the log-reader task prevents it from reading these transactions in the publisher database, the size of the transaction log may continue to expand as the number of non-replicated transactions increases.”

I think this is the known issue.

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com
noeld
noeld
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12310 Visits: 2048
First I would disagree with "this is a known issue"

In the vast majority of the cases you do not need to do this.
I had used transactional replication _a lot_ and time and time again time outs occurring one way or another and fixing those takes care of the problem.
In other cases very large transactions could cause this too and once again there are many things that can be done, like adding another log file temporarily, etc.

My suggestion is that you should clarify in the article that such procedure is to be performed as a last resort!!


* Noel
Basit Farooq
Basit Farooq
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 818
I've not said that this is a known issue, I said I think this is a known issue. Yes this needs to be done as last resort. In my article, I've included a little description explaining the background of the issue which we experienced. Indeed in SQL Server, There are several different options to perform the same task and our duty as an expert is that to best judge which option is best to resolve the issue quickly and efficiently.

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1642 Visits: 740
These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them. If an issue with the log-reader task prevents it from reading these transactions in the publisher database, the size of the transaction log may continue to expand as the number of non-replicated transactions increases.


Hi Basit,

The bit I've highlighted from the BOL extract is the important section. Essentially it won't matter how frequently you back up the TxLog, as long as the transactions are marked for replication they won't be removed and will continue to build up until the Log Reader Agent unmarks them.

That's why I'm saying, though what you've put in your article is correct and will clear out the transaction log (although I would still stay clear of truncating it!), it would be worthwhile mentioning that as a first port of call you should try to investigate the reason why the Log Reader Agent is not able to do its job properly. Otherwise the likelihood is you're only going to find yourself in the same position later down the line.
Basit Farooq
Basit Farooq
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 818
Hi Phil,

I agree with you but due to time constraint I could not investigate this issue in production. Prior to performing the process, I did performed the backups of msdb on both pubs and subs, distributer, publisher and subscriber databases. Once the issue fixed in production, I did restored these backups in our Test enviornments to investigate what caused the issue. Unfortunately we could not rectify the cause of this issue.

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com
chuck.hamilton
chuck.hamilton
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 396
I agree with Phil's comment. I dont see why "WITH TRUNCATE_ONLY" is necessary. This breaks the log chain and renders the publishing database unrecoverable. If you do this you should immediately take a full or differential backup to make the database recoverable again.

Keep in mind too that beginning with SQL 2008 that option has been removed. BOL says "The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued". The suggested means to forcefully truncate a log with no backup is to switch the database to SIMPLE recovery model then back to FULL. Of course this also renders the DB unrecoverable and an immediate backup should be taken.

The better thing to do would probably be just run a normal log backup and ensure that the log got shrunk.
Basit Farooq
Basit Farooq
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 818
Chuck,

Well, the truncation of transaction log is the only option for this situation (which I can think off at that time) because log reader is not reading the these transactions. I did restarted the log reader but it's not replicating the data. No errors were logged in either SQL ErrorLog or Replication Monitor.

I did some post resolution investigation on our test environment by restoring the backups of production but unable to came to conclusion why this happened.

If you know any betterway of resolving this issue then please share :-)

FYI - I fully understand the consequences of log truncation and that is why I took the full backup of the database prior to following the process.

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search