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

Introduction to the Transaction Log Expand / Collapse
Author
Message
Posted Monday, November 9, 2009 11:27 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
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.
Post #815990
Posted Monday, November 9, 2009 12:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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?


ATB

Charles Kincaid

Post #816029
Posted Monday, November 9, 2009 3:08 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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/
Post #816167
Posted Monday, November 9, 2009 7:49 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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/
Post #816279
Posted Tuesday, November 10, 2009 7:11 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 @ 3:21 PM
Points: 42,495, Visits: 35,566
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 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 #816483
Posted Tuesday, November 10, 2009 7:14 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 @ 3:21 PM
Points: 42,495, Visits: 35,566
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 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 #816490
Posted Tuesday, November 10, 2009 8:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Thanks James. I'm having a great deal of trouble seeing your script. Must be this silly IE 8.

ATB

Charles Kincaid

Post #816579
Posted Tuesday, November 10, 2009 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 5,242, Visits: 9,497
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
Post #816588
Posted Tuesday, November 10, 2009 12:28 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:38 PM
Points: 473, Visits: 8,736
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/
Post #816747
Posted Tuesday, November 10, 2009 1:19 PM


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 @ 3:21 PM
Points: 42,495, Visits: 35,566
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 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 #816779
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse