Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to Transaction Log Management in SQL Server, Level 2: A Brief Overview of Transaction Log...


Stairway to Transaction Log Management in SQL Server, Level 2: A Brief Overview of Transaction Log Architecture

Author
Message
Tony Davis
Tony Davis
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: Administrators
Points: 643 Visits: 1152
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
qutub
qutub
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 53
good article ...
http://letslearnssis.blogspot.com/
Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 944
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. Smile
imSQrLy
imSQrLy
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 473
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!"
Jagadish Kumar Punnapu
Jagadish Kumar Punnapu
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 111
Excellent Article Tony.
Thank you.
Tony Davis
Tony Davis
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: Administrators
Points: 643 Visits: 1152
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.
alpeshpradhan
alpeshpradhan
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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
Tony Davis
Tony Davis
SSChasing Mays
SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)SSChasing Mays (643 reputation)

Group: Administrators
Points: 643 Visits: 1152
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.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22934 Visits: 18262
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

michaelgowan
michaelgowan
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 876
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search