September 5, 2010 at 4:00 am
It means that replication (specifically transactional replication) is preventing the log space from been reused. Is this DB the publisher for a transactional replication publication?
What's the output of DBCC OPENTRAN
I can't tell you how big the log should be, I know just about nothing about your DB. You're the one who knows it best.
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 5, 2010 at 4:04 am
GilaMonster (9/5/2010)
It means that replication (specifically transactional replication) is preventing the log space from been reused. Is this DB the publisher for a transactional replication publication?What's the output of DBCC OPENTRAN
I can't tell you how big the log should be, I know just about nothing about your DB. You're the one who knows it best.
Is this DB the publisher for a transactional replication publication? I don't know, as far as I'm aware there is no replication set up on this server
This is the output of DBCC OPENTRAN
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (110803:36:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
September 5, 2010 at 7:28 am
From the looks of that there's another half-setup publication.
Can you check with someone that would know whether or not this server is supposed to be involved in replication at all?
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 5, 2010 at 7:54 am
It's definately not supposed to be set up in any kind of replication
September 5, 2010 at 10:37 am
Ok... This fix sounds a little odd, but it does work.
Create a transactional replication publication and publish one table (a small one)
Once the publication has been created, stop the log reader (you can do that from replication monitor
In a query window run
exec sp_repldone
Wait a couple minutes here
Delete the publication that you just created
Once that's done, run DBCC OPENTRAN again and see if the reference to distributed and non-distributed LSNs has gone away.
If so, try again to shrink the log to a reasonable size.
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 5, 2010 at 11:33 am
Okay Gail I've done as you said and the reference to the to the distributed and non-distributed LSNs seems to have gone away
Also log_reuse_wait_desc now = NOTHING
So I guess I now have to try and shrink the log files again. Is it okay to do that while the database is active?
September 5, 2010 at 11:49 am
I went ahead and did the shrink anyway. I am very happy to report that the log file has now gone from 160GB to 3MB so it looks like that little trick of yours did the job Gail, thankyou very much, I owe you a large drink 🙂
September 5, 2010 at 12:20 pm
Glad it's working. Be sure that you have log backups running, and manage the size over time, watching the growth and balance that with the backups you need.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 5, 2010 at 2:08 pm
Steve Jones - Editor (9/5/2010)
Glad it's working. Be sure that you have log backups running, and manage the size over time, watching the growth and balance that with the backups you need.
Will do Steve, cheers 🙂
September 6, 2010 at 9:20 am
You should grow your log files now from the 3mb to something more appropriate. This is essential to help avoid fragmented vlfs. Check out the articles by Kimberly Tripp in my sig.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply