Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tony Davis » Stairway to Transaction Log Management in SQL...
Add to briefcase
15 posts, Page 1 of 2
12»»

Stairway to Transaction Log Management in SQL Server, Level 2: A Brief Overview of Transaction Log Architecture Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tony Davis
Posted Thursday, June 02, 2011 8:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948
Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 2: A Brief Overview of Transaction Log Architecture
Post #1118801
qutub
Posted Wednesday, August 17, 2011 12:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 27, 2013 4:05 AM
Points: 74, Visits: 52
good article ...
http://letslearnssis.blogspot.com/
Post #1160948
Mike Dougherty-384281
Posted Wednesday, August 17, 2011 6:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 204, Visits: 704
Where's the link to the next level?

I felt the same disappointment as getting to the end of an hour-long episode of a serial TV show: "Aw man, gotta wait till next week? I want more now!"

I'll be anticipating your next article. :)
Post #1161079
imSQrLy
Posted Wednesday, August 17, 2011 6:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 11:42 AM
Points: 133, Visits: 438
If you do find that you have too many vlogs in your tlog how can you reduce the number of vlogs.What comes to mind is set the database to simple, shrink the tlog, set to full and then resize the tlog appropriately. Is there a more direct way and if not, does the way i outlined work?

I support a lot of systems that have databases for our software but we do not manage the databases. Many database start out with the default 1MB default log size with 10% auto growth. I would expect these sites would be susceptible to the problems described with large numbers of vlogs.

Jimmy

"I'm still learning the things i thought i knew!"
Post #1161084
Jagadish Kumar Punnapu
Posted Wednesday, August 17, 2011 7:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:33 AM
Points: 122, Visits: 99
Excellent Article Tony.
Thank you.
Post #1161110
Tony Davis
Posted Wednesday, August 17, 2011 7:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948
Mike - it's great to hear you're looking forward to the rest of the articles. I hope you enjoy them! The first five levels can be found here. Bear in mind that if you get through these quickly, then you really will have a bit of a wait to read more .

imSQrLy - yes, that should work, though it might not be necessary to switch to SIMPLE, rather than just taking a log backup in FULL. Anyway, it's basically the way I'd do it, and is also the way Kimberly Tripp recommends: see Tip 8 in her "8 Steps to better Transaction Log throughput" article.
Cheers,
Tony.
Post #1161112
alpesh-419376
Posted Wednesday, August 17, 2011 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 6:11 AM
Points: 14, Visits: 196
Excellent Article! and very well explained. still I have very simple question.

I may be sounding asking a basic question but wanted to make some things clear here.

what happens when the last lets say n'th VLF(VLFn) is full with the logs and logs between VLF3 to VLFn are all active and full with logs. where will be the next LSN generated? will it be in VLF1 and VLF2 if they are inactive/reusable state? if yes what will be the LSN for those new logs? highest/lowest?

Thanks,
Alpesh Pradhan
Post #1161150
Tony Davis
Posted Wednesday, August 17, 2011 9:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948
Hi Alpesh,

I should stress that I'm giving a simplified view of what actually happens, but yes the active log will "wrap around" so that that MaxLSN is now in VLF 1.

Tony.
Post #1161274
SQLRNNR
Posted Wednesday, August 17, 2011 6:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:37 PM
Points: 18,853, Visits: 12,437
Thanks for the article Tony.


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1161502
michaelgowan
Posted Thursday, August 25, 2011 10:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 10, 2013 12:39 PM
Points: 131, Visits: 444
imSQrLy (8/17/2011)
If you do find that you have too many vlogs in your tlog how can you reduce the number of vlogs.What comes to mind is set the database to simple, shrink the tlog, set to full and then resize the tlog appropriately. Is there a more direct way and if not, does the way i outlined work?

I support a lot of systems that have databases for our software but we do not manage the databases. Many database start out with the default 1MB default log size with 10% auto growth. I would expect these sites would be susceptible to the problems described with large numbers of vlogs.


Don't set the db to simple or you'll break the log chain. If your db is full or bulk, take a tran backup and then shrink/resize the log.
Post #1165567
« Prev Topic | Next Topic »

Add to briefcase
15 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.