SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to the Transaction Log


Introduction to the Transaction Log

Author
Message
JacekO
JacekO
SSChasing Mays
SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)SSChasing Mays (652 reputation)

Group: General Forum Members
Points: 652 Visits: 615
magarity (11/7/2009)
Since all I have is a dev server I wish the logs would go away automatically. I understand the importance of production systems' logs but I have no need for them - if there's a problem of any kind, I just reload my test files in barely a minute. From my POV logs hanging around after commit just clog up space, dang things.


If you use the Simple recovery mode than you do not have to worry about the logs on the development server. The SQL Server will deal with them automatically. Unfortunatelly (or fortunatelly - depending on your point of view) you must have a transaction log for each database on SQL Server. You may not have a need for them but they are critical to the SQL Server's reliability.

---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.

Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1635 Visits: 2384
JacekO (11/9/2009)
One of the biggest advantages of Full recovery mode is the ability to perform 'point-in-time' restores.

My mileage varies here. For me unplanned server restarts are the big one. Having a customer call to say that the cleaning person unplugged the SQL server box to use the vacuum, again, and there was no data loss is kind of a big thing. The point-in-time thing can come close if you restore you last full and then the differentials up to the point in time. How frequent are your backups? Is a full every day and logs every 15 minutes enough? Is one per minute way too much?

Here is a question for someone who has the time: Can I run a query that shows if there is any un-backed-up changes in my database?

ATBCharles Kincaid
James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 8754
Charles Kincaid (11/9/2009)
My mileage varies here. For me unplanned server restarts are the big one. Having a customer call to say that the cleaning person unplugged the SQL server box to use the vacuum, again, and there was no data loss is kind of a big thing. The point-in-time thing can come close if you restore you last full and then the differentials up to the point in time. How frequent are your backups? Is a full every day and logs every 15 minutes enough? Is one per minute way too much?

Here is a question for someone who has the time: Can I run a query that shows if there is any un-backed-up changes in my database?


Ok, now don't make fun of this query please...I made this all up as I just went along trying to see if I could come up with something along the lines of what you are wondering if is even possible, Charles...I'm sure a TSQL whiz on this site can really clean this up and make much more efficient...with that said, try this out...


SELECT * FROM #LogInfo
WHERE ([CURRENT LSN]) > (SELECT [Current LSN]
FROM #LogInfo
WHERE [Checkpoint Begin] = (SELECT TOP(1) [Checkpoint Begin]
FROM #LogInfo
WHERE [Checkpoint Begin] IS NOT NULL
ORDER BY [Checkpoint Begin] DESC))



Basically what this is doing is copying the LOG file contents into a temporary table (#LogInfo). Then it will find the last "Checkpoint Begin" date and use that LSN against the table and provide you with all the LSNs that are after that LSN.

The concept here is that

A) if you get back 10 or less rows then your pretty much have a completely backed up database/log (# of rows will change depending on type of backup returned, also these few "default" rows appear to be header type rows for the next portion of the log), and

B) if you've done backups of the database (FULL or DIFF) then the log creates a new "Checkpoint Begin" entry...so you are only concerned with the latest data from after that LSN.

Now, I did all of this on a database using FULL backup...so, could not be same for SIMPLE. Also, I did not test this out very well, so I'm sure there are some bugs with the logic and/or TSQL. But, it should get the ball rolling...to a script that would give details of what you are wondering.

Hope this helps!
James

P.S...I created this on SQL 2008; should work just fine on SQL 2005 as I don't see anything that isn't 2005 compatible...SQL 2000 users probably need find another method (I'm not sure but I think there was a table called "syslog" that contained this data in SQL2000...anyone confirm?)

[EDIT] Also, this script needs to be ran from within the database the log contents being inspected are. In case it needs to be said, and to CMA here, this script is untested and should only be ran from within development environments. The speed of the script is dependant on the size of the log file; in otherwords if your log file is Gigabytes in size it will take a long time to run! [/EDIT]

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 8754
Here is a simplified version of my last query...

SELECT * FROM #LogInfo
WHERE ([CURRENT LSN]) > (SELECT TOP (1) [Current LSN]                                                         FROM #LogInfo                                                         WHERE [Checkpoint Begin] IS NOT NULL                                                                                                                         ORDER BY [Checkpoint Begin] DESC)



I'm not at my desktop, so I didn't test this yet to ensure it didn't get broken after revising; use at your own risk.

Hope this helps,
James

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86655 Visits: 45254
JacekO (11/9/2009)
The only difference in those two modes is how long SQL Server holds on to this data, not in the data logged itself. In Simple recovery mode the log is self truncated after a checkpoint. In Full recovery mode a DBA must maintain the log and use BACKUP command to manage it.
The only mode that logs different data to the log is the Bulk Logged.


In simple recovery, bulk operations are also minimally logged. Not clearly stated in BoL, but see the intro paragraph here: http://msdn.microsoft.com/en-us/library/ms191244.aspx, also implied here: http://msdn.microsoft.com/en-us/library/ms191484.aspx

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86655 Visits: 45254
James_DBA (11/9/2009)
While the FULL recovery model will log more details to the log, the SIMPLE recovery model will log minimal details....


Only if those inserts could be minimally logged. Otherwise the logging will be the same

but, where the performance is loss is when these transactions are committed. The FULL recovery model will continue to add transactions to the list in the log; while the SIMPLE recovery model will continously clear (TRUNCATE) the log upon each checkpoint.


Why do you say there'll be a performance loss on commit if the log is getting cleared (of inactive portions only) on a checkpoint?
Got some tests that show the performance degradation?

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


Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1635 Visits: 2384
Thanks James. I'm having a great deal of trouble seeing your script. Must be this silly IE 8.

ATBCharles Kincaid
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13998 Visits: 15944
Why do you say there'll be a performance loss on commit if the log is getting cleared (of inactive portions only) on a checkpoint?
Got some tests that show the performance degradation?


Looking at it rationally rather than empirically, I'd say that since a log file is written to sequentially, any activity (such as truncation) that moves the disk heads away from the end of the file is likely to have some impact on performance. I haven't done any tests to ascertain how noticeable such an impact is.

John
James_DBA
James_DBA
Mr or Mrs. 500
Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)Mr or Mrs. 500 (576 reputation)

Group: General Forum Members
Points: 576 Visits: 8754
Charles Kincaid (11/10/2009)
Thanks James. I'm having a great deal of trouble seeing your script. Must be this silly IE 8.


That sure did a horrible formatting job...

here is the script again...without the formatting by SSC.

--START SCRIPT

SELECT * FROM #LogInfo
WHERE ([CURRENT LSN]) >
(
SELECT TOP (1) [Current LSN]
FROM #LogInfo
WHERE [Checkpoint Begin] IS NOT NULL
ORDER BY [Checkpoint Begin] DESC
)

--END SCRIPT

Thanks,
James

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86655 Visits: 45254
John Mitchell-245523 (11/10/2009)
Looking at it rationally rather than empirically, I'd say that since a log file is written to sequentially, any activity (such as truncation) that moves the disk heads away from the end of the file is likely to have some impact on performance.


True, but unless the log is the sole and only file on the physical disk (no other tran logs, no data, no backups, etc) then the head will be moving to write the other files. Also a log backup will move the head, as will a transaction rollback (as far as I know) as both have to read the log.

I'd just like to hear the reason behind the claim and any proof.

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


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