August 11, 2010 at 12:03 pm
Hello,
I am running SQL Server 2005 SP3 on a 2 Node cluster on Windows 2003.
There is one Publication replicated with a nightly snapshot.
The replicated database is also the publisher in a Log Shipping solution using Quest's LiteSpeed for SQL Server, which takes tlog backups every 15 minutes.
When I first set this up a couple years ago, I disabled the Queue Reader and Log Reader agent jobs, figuring (assuming) that they were not necessary and a waste of resources.
To my knowledge I have never had an issue as a result, but today I ran across an interesting problem. The transaction log for this DB was not flushing out or reusing space, and was nearly full of the 28 GB allocated.
A check of dbcc loginfo revealed 107 of 115 virtual log files were in use with a status of 2. A check of DBCC OPENTRAN showed no open transactions but did show that there was a non-replicated transaction:
[font="Courier New"]
Transaction information for database 'MyDB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : Oldest non-distributed LSN : (306661:1852016:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/font]
I tried to get some details out of fn_dblog, but I was already in to my depth with dbcc opentran and cancelled execution after 45 minutes of reading the log file.
Curious to see if there was an issue with the replication agent being disabled I re-enabled the Log Reader and started it up. I could see in activity monitor that it was making its way through it's steps but I was impatient and didn't want to have to wait for an autogrowth, so I bumped the log allocation up another GB.
Shortly thereafter I ran DBCC LOGINFO again and only 2 of the (now) 122 VLFs were active and the log file usage had dropped to about .1 %. Surprisingly (to me, at least) the subsequent tlog backup files were really small - between 1 MB and 30 MB. I had feared that some gigantic transaction was getting committed and would require a 30 GB backup.
So, all is well now, and the Log Reader agent is still running. My questions are:
Has anyone seen behavior like this before?
Is the Log Reader agent really necessary for Snapshot Replication?
Did re-enabling the Log Reader agent resolve this issue? Or did the file growth operation kick something else into place?
Thanks!
Sincerely,
Dan B
August 11, 2010 at 12:06 pm
...and, I have the results of the two DBCC LOGINFO executions if that is of use to anyone.
September 21, 2010 at 1:35 pm
Noone? 😀
Well I am not willing to risk it, so for now I will leave the Log Reader Agent running. Doesn't seem to be causing any performance issues.
I suppose it is a better idea to simply switch to transactional replication as the size of the snapshot continues to grow.
-D
September 21, 2010 at 1:40 pm
No, you do not need or want the log reader running for snapshot. Are there supposed to be any transactional replication publications on that database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2010 at 1:46 pm
No, I don't think so... Not yet at least.
Is it possible there is an orphan somewhere?
I have poked around a bit in the distribution database but nothing stands out.
-Dan B
September 21, 2010 at 1:48 pm
...Can running the log reader cause problems with snapshot publications?
September 21, 2010 at 2:00 pm
Half-configured transactional replication. Wish I knew why. Easy to fix
Create a transactional replication publication
Publish a single table (a small one)
Stop the log reader
New query (in that DB) and run exec sp_repldone
Drop the publication
That should clean everything up and remove the log reader job (which has no business there anyway)
Check DBCC OpenTran, reference to distributed and nondistributed LSNs should be gone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2010 at 2:22 pm
Heh... now I remember why this is snapshot.
It is a PeopleSoft database and PeopleSoft doesn't use or support explicit primary keys. Sigh.
So scrap what I said about moving to transactional...
However, I am working on the fix you proposed now in test. Thanks!
-Dan B
September 22, 2010 at 10:03 am
Gail,
Your steps worked like a charm!
Incidentally, I ran sp_repldone as follows:
exec sp_repldone null,null,0,0,1
Once I executed this, DBCC Opentran no longer included the distribution info.
And, once I deleted the transactional publication the Log Reader agent was deleted.
Woo hoo!
I still have a Queue Reader agent. Should that have been deleted in this process?
Thanks for the help!
-Dan B
September 22, 2010 at 10:18 am
Offhand, not sure.
If you think it might be transactional replication with queued updates, then repeat the procedure, but select a transactional publication with queued updates and see if, by deleting it after, the queue reader is cleaned off too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2010 at 11:11 am
No luck so far. I have tried a few different permutations, but the queue reader persists.
I'll keep poking around and see what I find, but I am less worried about this one than the Log Reader (perhaps mistakenly.)
Thanks!
-Dan B
September 24, 2010 at 12:03 pm
Gail,
Thanks again - I just noticed you helped someone else with the same problem only a few weeks ago!
http://www.sqlservercentral.com/Forums/Topic977853-146-1.aspx
Some sort of half-replication epidemic.
😀
-Dan B
November 15, 2010 at 9:16 am
Well... I am stumped.
It just happened again, this time with no log reader present.
All VLFs marked as active and full tlog errors being thrown left and right.
No open transactions, but the same replicated transaction information is listed again:
[font="Courier New"]Transaction information for database 'MyDB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : Oldest non-distributed LSN : (306661:1852016:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/font]
So, relying on the previous fix from Gail I ran sp_repldone and issued a checkpoint and the VLFs all appeared to go back to inactive status.
Note that during this period when the log was "full", none of the log backup jobs failed, and all were nice and small (< 20 MB), while the log itself had grown to 48 GB.
Is it correct to state that I should never see the "Replicated Transaction Information" in DBCC OPENTRAN with only snapshot replication in use? Any thoughts on what could be triggering this change?
Thanks,
Dan B.
November 15, 2010 at 11:04 am
Could it be the snapshot agent that creates or marks the transaction(s) as replicated?
I had thought that it was the log reader agent that did that.
Wishing I had run sp_replcmds before sp_repldone to see what, in particular, was marked...
Not sure what that would have shown.
-Dan
November 15, 2010 at 1:38 pm
skrilla99 (11/15/2010)
Could it be the snapshot agent that creates or marks the transaction(s) as replicated?
No, transactions are marked as to-be-replicated when they're initially logged, if SQL thinks they're needed. It is the log reader that marks them as replicated[/quote]
To 'permanently' fix the problem, you may need to do the whole create publication, drop publication thing. Monitor for a few days, see what happens.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply