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 12»»

Stairway to Transaction Log Management in SQL Server, Level 2: A Brief Overview of Transaction Log Architecture Expand / Collapse
Author
Message
Posted Thursday, June 2, 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: Monday, October 20, 2014 1:22 PM
Points: 569, Visits: 1,033
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
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, August 5, 2013 4:50 AM
Points: 74, Visits: 53
good article ...
http://letslearnssis.blogspot.com/
Post #1160948
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: Friday, October 17, 2014 1:59 PM
Points: 262, Visits: 918
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
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, July 8, 2014 1:20 PM
Points: 133, Visits: 472
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
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: Monday, February 17, 2014 9:44 AM
Points: 127, Visits: 105
Excellent Article Tony.
Thank you.
Post #1161110
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: Monday, October 20, 2014 1:22 PM
Points: 569, Visits: 1,033
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
Posted Wednesday, August 17, 2011 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:50 AM
Points: 14, Visits: 199
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
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: Monday, October 20, 2014 1:22 PM
Points: 569, Visits: 1,033
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
Posted Wednesday, August 17, 2011 6:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 17,823, Visits: 15,754
Thanks for the article Tony.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1161502
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: 2 days ago @ 2:49 PM
Points: 131, Visits: 689
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 12»»

Permissions Expand / Collapse