Managing Transaction Logs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Gail Shaw » Managing Transaction Logs

122 posts, Page 2 of 13

««1 2 3 4 5 »»»

Managing Transaction Logs

Rate Topic

Display Mode

Topic Options

Author

Message

ChiragNS

Posted Friday, October 31, 2008 4:54 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

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

Jim Russell-390299

Posted Friday, October 31, 2008 5:10 AM

SSC-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

Dugi

Posted Friday, October 31, 2008 5:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:48 AM
Points: 1,252, Visits: 3,367

Good stuff here about logs! Nice article Gail! Cool

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #594888

Luke L

Posted Friday, October 31, 2008 6:22 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:42 AM
Points: 2,891, Visits: 5,858

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

Karrasko's Co.

Posted Friday, October 31, 2008 6:39 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 29, 2009 2:16 AM
Points: 54, Visits: 23

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

Luke L

Posted Friday, October 31, 2008 6:45 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:42 AM
Points: 2,891, Visits: 5,858

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

GilaMonster

Posted Friday, October 31, 2008 7:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 37,732, Visits: 29,998

Luke L (10/31/2008)

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

Jack Corbett

Posted Friday, October 31, 2008 7:24 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Bob Bridges-173967

Posted Friday, October 31, 2008 7:27 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 07, 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

GilaMonster

Posted Friday, October 31, 2008 7:33 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 37,732, Visits: 29,998

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 »

122 posts, Page 2 of 13

««1 2 3 4 5 »»»

Permissions