December 6, 2011 at 12:14 pm
Until you get the replication issue resolved (and I've lost count of the number of people here that have had this problem), the log won't shrink and can't be reused and if it fills the disk, your database will go read-only.
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
December 6, 2011 at 12:18 pm
Is there any way I can find out when or who tried to use the replication??
December 6, 2011 at 12:26 pm
Why does it matter?
If the DB was a restored backup of a database on another server that was replicated, this can happen. If the database uses snapshot replication (or ever did, this could happen). Someone just playing with replication (adding and removing) won't break things like this.
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
December 6, 2011 at 2:02 pm
Jeff Mayer (12/6/2011)
Thanks for all the help with this! Unfortunately at this point, my hands are completely tied by IT!!! The log file is now up to 450GB.Thanks again!!
that log is growing fast. have you checked out the growth factor of the log file?
You need to bite the bullet with setting up the dummy replication then removing it.
Please confirm this is SQL 2008.
---------------------------------------------------------------------
December 6, 2011 at 2:10 pm
Agree wth George...go through the Wizard to set up a small test replication (shouldn't matter which type), replicate one table. After you've done that, undo it all. This should clear up your issue almost immediately (depending on your logging model simple/full, you may need to perform a transaction log backup...or if in simple mode, you should be able to run a shinkfile).
Right-clicking the Replication node and choosing "Disable Publishing and Distribution" may also fix your issue. If not, try drilling down through the Replication node to make sure there are no lingering local Publications/Subscriptions - if there are any, you'll need to delete those as well (and if/when prompted to connect to the publisher(s) to remove the record of the subscription there, say YES).
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 6, 2011 at 7:19 pm
We found the following query which cleared the transactions and allowed us to shrink the log:
exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
But I am still showing replication for the value of log_reuse_wait_desc. Could todays problem occur again???
December 6, 2011 at 11:58 pm
Yes, it will.
Please see my posts in the thread linked earlier for how to resolve this completely.
p.s. I'm not making vague guesses here, that has helped a lot of people over the years.
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
December 7, 2011 at 4:28 am
I really appreciate your help, and I want to use your information to resolve our issue, but I can only do so much. I am trying to put together the information so I can recommend how to resolve this issue.
December 7, 2011 at 5:42 am
Jeff, I won't be able to get any sleep till you tell me you have the growth factor of the log file under control.
🙁
---------------------------------------------------------------------
December 7, 2011 at 5:47 am
Jeff Mayer (12/7/2011)
I am trying to put together the information so I can recommend how to resolve this issue.
GilaMonster (4/10/2009)
Set the server up for replication, create a publication in the DB in question. Publish any table (not important which one) and then completely remove replication again.
Seriously, that's all that needs doing.
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
December 7, 2011 at 5:49 am
I'd trust Gail with my life if I needed to (especially in sql).
This seems like a very small step compared to that :-D.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply