Managing Transaction Logs

Question

Managing Transaction Logs

Viewing 15 posts - 46 through 60 (of 128 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Phil Harbour (2/11/2009)
This can't be true otherwise how could you rollback an uncommitted transaction which i understand to be a transaction without a closing commit statement in the transaction log?

Using the log buffer?

When are the log records written to disk?

The log records have to be hardened on disk before the transaction is considered complete. The log buffer may be written to disk earlier if other transactions commit and force a write to disk.

The log records have to be committed on disk before the data changes are on disk and before the transaction is complete. That, as far as I'm aware, is the only requirement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Not sure how I missed this when it was first published... very well done!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EllieDBA SSCommitted Points: 1786 More actions · Answer 3

Wonderful points, as usual THNAKS Gail! exactly to the point and useful

Ellie

tk.taunton1 Grasshopper Points: 13 More actions · Answer 4

Hello, I am one of those 'newbies' to SQL administration.

I am confused about about T-Log truncation.

My MSSQL 2005 db is set to Full Recovery mode. I have a maintenance plan task 'Backup T-Log' that runs every 15 min.

As far as I understand these T-Log backups 'truncate' the T-Log automatically. This is bad practice - I am correct?

My question - how can I have my T-Log backup task run and do the backup BUT not do the truncation?

Please advise. Thanks,

Tom.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Backing up the transaction log DOES not truncate the log. It frees up space on the log file for re-use. It basically says, "Now that these transactions have been backed up, allow them to be overwritten".

In order to truncate the transaction log you have to explicitly use the with Truncate_Only option.

Jack Corbett
Consultant - Straight Path Solutions
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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

tk.taunton1 (3/18/2009)
As far as I understand these T-Log backups 'truncate' the T-Log automatically.

Yup. They back up the inactive log records, write them to the log backup file and then discard those records that have just been backed up to allow the space to be reused.

This is bad practice - I am correct?

No. Truncating without backing up is bad practice as it breaks the log chain, prevents you from doing any more log backups and preventing you from doing point-in-time restores to any time thereafter.

With a log backup, you've 'archived' those log records before deleting them and, should it be necessary to restore the backup, it can be done to any time by using a combination of a full backup and subsequent log backups.

It's like deleting a lot of rows from an important table (just truncating the log), vs moving those rows to an archive table and then deleting them (backup with truncate)

My question - how can I have my T-Log backup task run and do the backup BUT not do the truncation?

You can do that, but if you do there will be nothing clearing log records out and your log will grow until it fills the disk.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

tk.taunton1 Grasshopper Points: 13 More actions · Answer 7

Thanks, now I understand - I guess I missed something in my readings and that caused the uncertainty.

Don't mean to be a 'pain', but, can you tell me where the TRUNCATE-ONLY setting is set? I quickly took a look at my maintenance plan for T-Log backup and FULL backup, and SHRINK DATABASE task and I cannot see that setting. I just want to make sure that it has not been set ON.

Thanks again for you helpful response.

Tom.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

tk.taunton1 (3/18/2009)
I quickly took a look at my maintenance plan for T-Log backup and FULL backup, and SHRINK DATABASE task

Ack! Shrink? Why?

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

and I cannot see that setting. I just want to make sure that it has not been set ON.

I don't think you can set it in a maint plan. If a log backup file is being created, then the backup does not have the truncate_only option.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

tk.taunton1 Grasshopper Points: 13 More actions · Answer 9

I quickly took a look at my maintenance plan for T-Log backup and FULL backup, and SHRINK DATABASE task

Ack! Shrink? Why?

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/%5B/i%5D

Shrink - I know...that wasn't me (someone else had configured it before I arrived because it was recommended by our ERP vendor). I will be changing it soon. I knew that much...:)

And I cannot see that setting. I just want to make sure that it has not been set ON.

I don't think you can set it in a maint plan. If a log backup file is being created, then the backup does not have the truncate_only option.

Makes sense - very good info.

Thank you for your help

Tom.

harini.hanu Old Hand Points: 385 More actions · Answer 10

Hi Gail this is very much useful article basics were explained very clearly can u throw some light on how query is processed in sql server for example if we submit query to sql server wat exactly hapens .. could u please explain the basics in this query processing

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

I think that's a topic for a different thread. If you're interested, there's a good description in Itzik's book Inside SQL Server 2005: T-SQL Querying.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

harini.hanu Old Hand Points: 385 More actions · Answer 12

thank you gail i will go through that book can we find this book in safari books online

ed-674698 Valued Member Points: 69 More actions · Answer 13

Thanks so much for your time and effort, and in help you’ve offered those of us who are less mentally endowed… Would you allow one more question?

As stated previously “To restore, you need the full backup and then all of the log backups, in sequence, since that full backup” can I deduce that a full backup will also truncates the transaction log? If not would you be so kind and explain how that works?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

ed (7/10/2009)
“To restore, you need the full backup and then all of the log backups, in sequence, since that full backup” can I deduce that a full backup will also truncates the transaction log?

It does not. The only things that truncate the transaction log are log backups, explicit truncations or a switch to simple recovery.

If not would you be so kind and explain how that works?

How what works? The full backup or the backups required to restore?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

ed-674698 Valued Member Points: 69 More actions · Answer 15

Sorry, I'm a little slow... If one does a full back up, doesn't all of the preceding log information get incorporated into the full backup, with the next log backup capturing the net changes since the full backup, without the need for the logs that preceded the full backup?