July 15, 2011 at 10:02 am
Hi,
Our main database backup recently started ballooning (rising from 35gb to 75gb) and then started preventing subsequent backups due to space issues on the server. The transaction log had risen to 45gb, the db is in FULL recovery mode and when I tried shrinking the tran log through the GUI it stated that there was only 9% free of the 45gb and it wouldn't actually free this anyway. I then started looking at the replication side of things and discovered that the distribution db was not being backed up so I created a maintenance plan and backed it up and also set up IsSyncWithBackup on the distribution db (http://msdn.microsoft.com/en-us/library/ms152560(v=SQL.100).aspx). So after restarting the SQL service, backing up distribution then the main db I have succeeded in reducing the backup size back to 35gb but the log file is still 45gb and is refusing all attempts at shrinking (inc switching to simple mode) even though the GUI states the log file has 87% free space.
It looks like there are still transactions in the distribution db preventing the log on the main db from shrinking but I can't see how to remedy this. Transaction logs are being backed up every 15 minutes and I am not sure if this is related but these files are pretty big, sometimes 2.5gb for no obvious reason and oddly enough straight after I did a back up of the main db today the next tran log was 5gb which seems unusual. I was wondering whether reinitializing the subscription might resolve this, I have tried running the snapshot agent to synchronise the dbs but this did not change anything.
Set up:
64bit SQL2008 on Windows 2008 cluster Server replicating to same set up.
Any help would be much appreciated.
Thanks,
Liam
July 16, 2011 at 9:44 am
Check the log_reuse_wait_desc column in sys.databases for that database. If it shows up as REPLICATION, then you have an issue with replication not completing so those transactions cannot be marked as reusable.
As for how large the transaction logs are - when you run your backups, does that include a step to rebuild/reorganize indexes? If so, that would cause additional transactions that have to be backed up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2011 at 10:28 am
The following link suggest using Red Gate's Backup Utility:
You might want to download the trial version and give it a shot.
Do you have open transactions? Is CDC enabled?
I hope this helps. 🙂
Regards,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 17, 2011 at 3:33 am
Thanks both for your help, very useful. sys.databases showed as ACTIVE_TRANSACTION, so I ran DBCC OPENTRAN which showed this:
Oldest active transaction:
SPID (server process ID): 7s
UID (user ID) : -1
Name : tran_sp_MScreate_peer_tables
LSN : (549:456:1)
Start time : Jul 15 2011 2:12:01:687PM
SID : 0x01
E.g. since I last restarted SQL service. It is a bit odd as I ran dbcc opentran as part of my initial investigations and it did not show any open transactions. A bit of research then led me to this article about SP2 and log size with replicated dbs.
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/b77799d2-a931-467a-928d-49426d83d5ce/[/url]
Which seems to be exactly the problem I am having, I will work through this when I am back in the office tmw and post results.
Liam
July 19, 2011 at 8:43 am
Fixed!!
I fixed this in two steps:
1) Recent upgrade to SP2 did not install correctly on one of the nodes as the registry value from link was 0, working through the post corrected this.
After this was done the transaction log was still the same size and would not shrink. As there were no active transactions (and CDC is not enabled) I assume there were orphaned transactions in the distribution database that could not be marked as committed. So
2) Mark all replicated transactions as complete using these steps:
-Stop log reader agent
-EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
-Close connection used to run code above
-Reinitialise all subscriptions (No way around this as log reader agent will not start at this point)
-Start log reader agent
After which a shrink through the GUI reduced the log size to 5gb.
Liam
July 22, 2011 at 2:51 am
It is also worth noting that you will lose all indexes (and statistics) on the subscription database bar primary keys after reinitialising, I am just recreating from a back up now!
Script here: http://ask.sqlservercentral.com/questions/16646/create-script-for-indexes
Needs minor adjustments to get statistics from sys.stats
December 5, 2011 at 3:39 am
We upgraded last week to SP3 and lo behold the same problem occurred. MS have now posted a resolution on 22nd Sept: http://support.microsoft.com/kb/2509302. We are going to run through this tonight, I will post results tmw.
From the MS post it looks like all we had to do was wait a bit longer between starting the SQL server and SQL agent when upgrading to SP3 in the first place. So the previous steps including reinitialising replication may not have been necessary!
December 5, 2011 at 4:42 am
I think the problem may arise when trying to apply the SP to the passive node rather than the active node, which is how we have been doing it. This post suggests avoiding upgrading the passive node even though this means the whole cluster is offline for a longer period of time: http://sqlblog.com/blogs/linchi_shea/archive/2009/10/04/pause-the-passive-node-to-apply-sql2005-service-pack-or-hotfix.aspx.
MS say you can update the passive node but you need to remove the passive node as a possible owner for the SQL service while you upgrade (not a step we had performed): http://support.microsoft.com/kb/958734.
It looks to me like the problem we are experiencing on the passive node is because the SQL server agent is being allowed to start even though the SQL service on the passive node has not finished applying the SP, maybe it is checking the actual clustered SQL service which was running all along and that this would be avoided by removing the passive node as a possible owner. Does anyone have any thoughts or guidance on applying SPs to the passive node?
December 5, 2011 at 6:46 am
Bad News: Posted to wrong thread.:crazy:
Good News: Issue with Replication Wait Type has been resolved, Publications not need and removed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2011 at 7:15 am
What thread were you referring to? It does sound like good news 🙂
December 5, 2011 at 9:17 am
liam.gavin (12/5/2011)
What thread were you referring to? It does sound like good news 🙂
This is the thread:
http://www.sqlservercentral.com/Forums/Topic1211238-357-4.aspx#bm1213204
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 6, 2011 at 6:58 am
Running through the MS post (http://support.microsoft.com/kb/2509302) removed the tran_sp_MScreate_peer_tables tran from dbcc opentran and I was then able to shrink the transaction log. I'd still be interested if anyone has any guidance on rolling out SPs on the passive nodes....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply