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 ««12345»»»

Managing Transaction Logs Expand / Collapse
Author
Message
Posted Friday, October 31, 2008 4:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
GilaMonster (10/31/2008)
Chirag (10/31/2008)

Thank you Gail. That explains things clearly. So would the same apply to BLOB data. Would BLOB data be brought into memory and then modified?


Exactly the same. The query processor can only operate on pages in memory. The query processor has no knowledge of the disks and no ability to interact with the disks. That's what the storage engine does.


Thank you once again.


"Keep Trying"
Post #594864
Posted Friday, October 31, 2008 5:10 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
Outstanding! If only BOL had more of this kind of informative and understandable overview material...
(How did you get so smart, Gail?)
Post #594871
Posted Friday, October 31, 2008 5:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 1,262, Visits: 3,420
Good stuff here about logs! Nice article Gail!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #594888
Posted Friday, October 31, 2008 6:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:20 PM
Points: 2,897, Visits: 5,978
Nice Article Gail, I did have one question though. Perhaps it's just me but it's been something I've seen here at SSC a few times in recent months...

what was your last sentence supposed to say? All I see is
I hope this has clarified some of the details of what the


Again nice concise explanation of the log.

thanks.
-Luke.


To help us help you read this

For better help with performance problems please read this
Post #594896
Posted Friday, October 31, 2008 6:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 7, 2014 2:30 AM
Points: 54, Visits: 31
A great article.

One question about transaction log. I have my databases in full recovery and I don't run backup logs. Every night I do a full backup and only if this step is right then I truncate the backup log.
Never mind a data lose between full backups.
I always have thought that if the db is broke then I can run a backup log, after that to restore the full backup from the last day and at the end to restore this last log backup.

What do you think about this method?

Many thanks
Post #594907
Posted Friday, October 31, 2008 6:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:20 PM
Points: 2,897, Visits: 5,978
What happens when "the db's broke" and you can't run a log backup? Say for instance if you lose a disk controller, or disks or a fire engulfs your server etc...

what then?

If you take a nightly Full backup and take log backups periodically throughout the day that you store on another machine you have a much better chance of recovering more data than you would with your current schedule.

Worse yet, what happens when you do your full backup, and it's invalid, corrupted or something else? now you're out 2 days worth of data.

But then again I'm just a bit on the paranoid side when it comes to these things...


To help us help you read this

For better help with performance problems please read this
Post #594917
Posted Friday, October 31, 2008 7:22 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 @ 4:03 PM
Points: 42,485, Visits: 35,554
Luke L (10/31/2008)
Nice Article Gail, I did have one question though. Perhaps it's just me but it's been something I've seen here at SSC a few times in recent months...

what was your last sentence supposed to say? All I see is
I hope this has clarified some of the details of what the



Blame the editor. ;) The last sentance is supposed to read

I hope this has clarified some of the details of what the transaction log is, how it’s used and how it should be managed.



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 #594943
Posted Friday, October 31, 2008 7:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
Very nice article Gail. Clear, concise, and to the point. Sounds like a follow-up on how the checkpoint and lazy writer work would be good.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #594944
Posted Friday, October 31, 2008 7:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 7, 2013 6:16 AM
Points: 90, Visits: 350
From all of the replies, Gail, you seem to have scratched an itch that a lot of people have (including me!)

One thing I'm not absolutely clear on though... you mentioned the "daisy chain of logs" in recovery. If I have a full backup every night and have transaction log backups every 6 hours, in case of a recovery need, do I need every log backup along with the last full backup or only the latest log backup along with the last full backup?

Thanks again for a good article!

Bob
Post #594948
Posted Friday, October 31, 2008 7:33 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 @ 4:03 PM
Points: 42,485, Visits: 35,554
Karrasko's Co. (10/31/2008)

What do you think about this method?


I think it won't do what you think it will. If you truncate the transaction log then you can neither take a tran log backup nor do any type of point-in-time restore afterwards until another full or diff backup is run.

If your DB failed and you tried to run a log backup, you would get this error (SQL 2005)
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

On SQL 2000, it's worse. The log backup will succeed, but it will not be usable. The truncate throws away log records. In order to restore a log backup, the log chain must be intact. Because the log records are missing, the log chain is not intact.

If you have any form of disaster with the backups run like that, you will be loosing up to a day's data. If that's acceptable, set the DB to simple recovery, because that's essentially what you're in now. If not, set up regular log backups.



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 #594956
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse