SQL Clone
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: Administrators
Points: 1177 Visits: 1162
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 (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 53
good article ...
http://letslearnssis.blogspot.com/
Mike Dougherty-384281
Mike Dougherty-384281
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 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 Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 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 Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 117
Excellent Article Tony.
Thank you.
Tony Davis
Tony Davis
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: Administrators
Points: 1177 Visits: 1162
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
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: Administrators
Points: 1177 Visits: 1162
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-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48407 Visits: 18569
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 (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 884
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