Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild the log file to reduce VLFs Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 9:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 24, 2014 6:50 PM
Points: 405, Visits: 556
Hi all

So I've been poking around one of the databases and as part of my ongoing health checks, I reviewed the VLF numbers with a quick DBCC LOGINFO.

This particular database has 628... *sigh*

So I start to look to rebuild this, and when I take my log backup, it gives me a warning that there are replication publications on this.

Now in this case, the subscriber replication agent pops up once or twice a day and pulls everything across. And to complicate things, there are 2 subscribers.

So I'm looking for suggestions on how I can rebuild this log file to reduce the number of VLFs without breaking the subscriptions and causing huge amounts of pain.

Suggestions? Or do I just leave it alone. Performance is acceptable, so this is, in part, optimisation for the sake of optimising.
Post #1388919
Posted Tuesday, November 27, 2012 12:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:15 PM
Points: 43,017, Visits: 36,179
Find a quiet time when the active portion of the log is at the beginning of the log file. Shrink the log as small as possible. Grow it back to full size in the number of operations that will give you the VLF count that you want.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1388986
Posted Tuesday, November 27, 2012 12:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 24, 2014 6:50 PM
Points: 405, Visits: 556
Between the two different subscriptions to replication, I'm not sure when that'll be!

Thanks for the advice.
Post #1388995
Posted Tuesday, November 27, 2012 2:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 9:34 AM
Points: 82, Visits: 657
Here is a good post I used last month we are VLF issue with over 1000

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Thanks
Post #1389418
Posted Tuesday, November 27, 2012 4:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 24, 2014 6:50 PM
Points: 405, Visits: 556
I've read Kimberlys article and the follow up (http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx)

What I was more interested in getting some help with was managing the delayed replication, which appears to be preventing me from shrinking the log file.
Post #1389465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse