March 8, 2011 at 7:45 am
My transaction log and mdf backup file keep ballooning in size and DBCC check log gives
Transaction information for database 'hub_live'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (23798:744:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The db currently has snapshot replication. Transactional replication was removed by someone else 6 weeks ago. We go through the process of releasing the LSN (ie. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1), changing to simple recovery, shrinking the logs every fortnight, but then the log and backups balloon again.
I can't find any SQL Agent jobs that are to do with transactional replication (only snapshot) and logread.exe isn't running. The only job step I can find is the snapshot replication agent "Replication Snapshot" set which is
-Publisher [SVRSQL6] -PublisherDB [db_live] -Distributor [SVRSQL6] -Publication [db_live_replication] -DistributorSecurityMode 1
I couldn't locate anything either in the log files or the the event viewer.
Any assistance would be much appreciated.
Stevo
March 8, 2011 at 7:46 am
See the replication section of this article: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
March 8, 2011 at 9:21 am
Thanks for the response.
There was no Change Data Control enabled, but select name, log_reuse_wait_desc from sys.databases did flag the issue as replication.
I found a great article 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.
It seems snapshot replication that includes DDL changes can cause non distributed LSN problems as the LSN isn't released even after succesful (in every other sense) replication.
Ths seems to be a known bug.
The workaround is not to replicate schema changes OR to add sp_repldone null, null, 0,0,1 into an agent job
Many thanks,
Stevo
March 8, 2011 at 11:30 am
Yup, that post and the issue is mentioned in the article I referenced (which is why I referred you to it)
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
July 7, 2011 at 9:29 am
I have the similar issue with transactional replciation... any help..... ?
July 7, 2011 at 9:37 am
What specifically is the problem? Is your log reader agent running?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 7, 2011 at 9:50 am
Please post new questions in a new thread and give as much detail as possible.
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
July 7, 2011 at 10:51 am
Yes the log reader agent is running:
Initially i had problems shrinking the log file. Later I had used exec sp_replrestart and restated the
replication and also manually unmarked the transactions pending for replication using the following
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time
= 0, @reset = 1
and then could shrink the log
just as trouble shoot i found that the database log is not being reused due replication from following :
select name, log_reuse_wait_desc from sys.databases
but after a while I again see the same following error
The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.
The distributon agent is working fine but the log reader agent doesnot.....
It keep on failing even after restarting the agent....
One last thing... I have the sync with backup enabled between distributor and subscriber...
July 7, 2011 at 11:38 am
As Gail stated you should really start a new thread. When you do please post the link in this one so that I know where it is.
For now though, by executing the repldone you have put yourself in an out of sync condition and have removed all transactions in the log that have not yet made it to the distribution database. With that being said, I also believe that you have a metadata inconsistency with that publication or subscription which is causing the failure. If at all possible I would recommend rebuilding the publications / subscriptions and seeing if that clears things up. You can do that without initializing the subscriber (if initializing is a problem) as one of the options when you create the subscription. Regardless, due to the sp_repldone execution you will have to do some sort of manual sync to get your data in line.
Let me know how you decide to proceed. If you want to look for the inconsistencies in the metadata I can provide you some scripts but let me know that in the new thread.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 8, 2011 at 8:00 am
I have recreated the publications and subscriptions ... again its sync and running fine... Thanks 🙂
July 8, 2011 at 8:17 am
Excellent - glad that worked and thanks for the update.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 3, 2013 at 9:33 pm
same ..solved... deleted the publication and subscription then recreated it.
January 7, 2015 at 12:38 pm
Thank you very much, I got the same issue and fixed it with you guys help here.
January 8, 2015 at 10:09 am
Hello Respected Team, I have a issue:
-Production database log file has grown 120GB
-I have hourly log backups running but even than log file is not getting truncate and shrink
-I saw we have a snapshot database created under Databases-->Database Snapshots
-I have dropped that snapshot
-I have run following to commands to drop any replication though I don't have any kind of replication at this server
sp_removedbreplication [HDMPRODUCTION]
go
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
-But when I run SELECT name, log_reuse_wait_desc FROM sys.databases I still sees Replication under column log_reuse_wait_desc
-After running above commands log file got truncated but still not able to shrink it.
Any idea why I still sees Replication under column log_reuse_wait_desc?
Thanks for any help
Imran
January 8, 2015 at 10:31 am
What do you see when you run dbcc opentran(YourDBName)? Check the SPID that shows in the output.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply