July 24, 2012 at 12:02 am
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.comJuly 24, 2012 at 2:16 am
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.
July 24, 2012 at 2:22 am
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).
July 24, 2012 at 2:56 am
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.comJuly 24, 2012 at 4:51 am
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
July 24, 2012 at 5:15 am
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.comJuly 24, 2012 at 5:59 am
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.
July 24, 2012 at 6:32 am
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.comJuly 24, 2012 at 10:08 am
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.
July 24, 2012 at 10:51 am
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.comJuly 24, 2012 at 10:54 am
Chuck,
Well, the truncation of transaction log is the only option for this situation (which I could think off) because log reader is not reading the these transactions. I restarted the log reader agent as well but its still didnt resolve the issue. No errors were logged in either SQL ErrorLog or Replication Monitor. I got not other place where I can create the additional log file on the server.
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.comJuly 24, 2012 at 11:36 am
If it happens again do exactly what you did except for the backup log with truncate_only. Instead try this...
1. backup log [dbname] ...
2. use [dbname] dbcc shrinkfile (2) <-- assuming log file is file # 2
3. If the log doesn't shrink, repeat 1 and 2 again.
Also if this DB happens to be the primary database in a log shipping configuration, replace step 1 with "run the log shipping backup job manually".
July 24, 2012 at 12:28 pm
Chuck,
As I said, I've taken the full backup prior to performing the process. If I had to do the restore I can just restore the database from full backup. Below is timeline of our issue.
Full backup Daily 21:00.
Diff backup Twice a day 09:00 and 18:00.
Log backup every 15 minutes.
I got called out for this issue at 04:00 am. I took that full copy_only backup at 4:15am and then followed the process. If I need to restore the database then I can perform the restore from full backup and don't need hassle of restoring log backups.
Hope this clears the situation in which I thin BACKUP LOG WITH TRUNCATE_ONLY is better option.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJuly 24, 2012 at 3:07 pm
Just to add to my last message that backup log is not a option in this situation because the issue is with LogReader.
“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.”
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJuly 24, 2012 at 10:54 pm
Hi Basit,
You're right in that the issue with the Log Reader Agent will prevent the clear down of the TxLog. Howver, you've reinitialised the subscription and generated a new snapshot, meaning you don't need to replicate those older transactions from the log file anymore. This should mean you ought to be able to perform a log backup after you've done you're re-initialisation and the old log entries should be removed (I say should - there's never any guarantee with these things!).
I appreciate that time was against you on this occassion, and in that respect I think what you did was probably correct. I would just err on the side of caution as there may be others who read your article and if they don't have sufficient knowledge could land themselves in a bit of deep water because of some of the consequences. That why I've said it would have been nice to have mentioned something in the article about alerts and having the time to troubleshoot the Log Reader, and also mention about the consequences of running TRUNCATE_ONLY with the log backup.
Don't get me wrong, I'm not having a dig at you and what you're written. It takes a brave professional to write something and post on SSC with the eyes of many experts (and I am certainly not claiming to be one!) scrutinising your work. So in that respect, well done! 🙂
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply