September 26, 2014 at 9:52 am
Log reuse wait for database is replication. But no replication has ever been setup??
This means that transactions continue to hang around despite it being simple and despite successful backups and transaction log backups. Result is a massive log file. Other articles recommend:
exec sp_repldone null, null, 0,0,1
which gives:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
So how can I tell SQL that I don't want any replication and to please throw away all transactions that have been written to the database.
(SQL 2008 R2 Express SP2)
September 27, 2014 at 5:54 pm
OK. SLight hange to the question. I detached the database preserved a copy of the mdf and ldf then attached without the ldf and a have a new log file. But this is assuming that all the transactions remaining in the log file were only waiting on the mysterious replication (that should never have been there in the first place). What I am not clear on is, whether or not, when - select name, log_reuse_wait_desc from sys.databases - shows "replication", does that mean it is just waiting on replication? Is there some sort of precendence on the value here because surely a transaction can be waiting on more than one thing before the space can be reused?
September 28, 2014 at 4:32 am
It means that log segments can't be reused because of replication. Doesn't necessarily mean replication is the only reason, just that replication is a reason.
Btw, deleting a log file is the fastest way to destroy a database entirely. Don't make a habit out of it. I also suspect it hasn't fixed the problem. Check in a day or so, I suspect you'll see replication back as a log reuse wait.
You said no replication is configured. Are you using CDC?
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 28, 2014 at 5:20 pm
Thanks Gail,
I had checked DBCC OPENTRAN (Distributed was 0 but very large number Non distributed) and then dm_exec_sessions first and there were no long running transactions and no-one was logged in so I believe that there was no risk of losing transactions not yet written to the database. (I hope that was sufficient?).
Re: CDC is also not possible on SQL 2008 R2 Express edition - or is it? When I execute - SELECT [name], database_id, is_cdc_enabled FROM sys.databases - nothing is enabled. So the cause of the waiting on replication is still a mystery. I see other posts indicating a possible bug in SQL 2005 (this database was previously on 2005 a little while ago so I wonder if the problem happened then and has remained?). 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
Thanks again.
September 29, 2014 at 1:38 am
No, that wasn't sufficient. You got lucky this time.
Yes, that bug is probably what you have. Check whether there's still replication-related events.
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 29, 2014 at 12:59 pm
DavidB 36561 (9/28/2014)
Thanks Gail,I had checked DBCC OPENTRAN (Distributed was 0 but very large number Non distributed) and then dm_exec_sessions first and there were no long running transactions and no-one was logged in so I believe that there was no risk of losing transactions not yet written to the database. (I hope that was sufficient?).
Re: CDC is also not possible on SQL 2008 R2 Express edition - or is it? When I execute - SELECT [name], database_id, is_cdc_enabled FROM sys.databases - nothing is enabled. So the cause of the waiting on replication is still a mystery. I see other posts indicating a possible bug in SQL 2005 (this database was previously on 2005 a little while ago so I wonder if the problem happened then and has remained?). 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
Thanks again.
Just out of interest what do the following return
select * from msdb..MSdistributiondbs
select * from msdb..MSdistpublishers
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2014 at 5:02 pm
Hi Perry,
Sorry for the slow response - I have been off for a few days. Those do not work for me. Are you abbreviating something there? I don't see any MSdistributiondbs tables in msdb database? Do these only get created when replication is first setup?
However - exec sp_get_distributor gives installed=0, distribution server=null, distribution db installed=0, is distribution publisher=0 has remote distribution publisher=0. This is all as I would expect - there is no replication setup so transactions should not be waiting on replication.
Gail's comment that my checks were insufficient is a little concerning. Is there further checks I should have done to ensure that transactions were ONLY waiting on replication?
Currently database is normal and log_reuse_wait_desc is NOTHING as expected so problem has not returned.
Cheers
October 6, 2014 at 5:58 am
I mean your checks to see if it was safe to delete the log file were inadequate...
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply