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

Clearing the Transaction Log Expand / Collapse
Author
Message
Posted Tuesday, June 1, 2010 12:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
Great question. This is what the QOD is all about. A great explanation of the answer as well.

Converting oxygen into carbon dioxide, since 1955.

Post #930927
Posted Tuesday, June 1, 2010 8:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
Thanks for the great question! I am learning so much more from these questions and their explanations.
Post #931062
Posted Wednesday, June 2, 2010 11:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,819, Visits: 2,563
Got it wrong.

Thanks for a good explanation.
Post #931531
Posted Wednesday, June 2, 2010 11:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:11 PM
Points: 6,604, Visits: 8,910
I got lucky... I got it right. Partly from discounting some, and the rest by what didn't make sense.
I also agree... excellent explaination of the answer.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #931553
Posted Wednesday, June 2, 2010 2:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:21 AM
Points: 1,676, Visits: 1,757
This is a great question, thank you Paul. It would have been very difficult to answer it correctly, but fortunately for me, incorrect options were clearly incorrect, and therefore it was not too difficult to not answer the question incorrectly:

B. VLFs are physically deleted? No, VLF is, well, VLF, a fragment
C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that. Even formatting the drive does not actually overwrite data with zeroes.

With above out of the way, All of the above and Answers A and C have to go too.

This leaves only A and D to seriously consider, and while this is true that the only way to shrink the file is to first have it inactive VLFs (Status = 0 when running dbcc loginfo()) bunched in its tail, the log file shrinks when dbcc shrinkfile is called.

Thus, answer A is the only answer which is not incorrect.

Oleg
Post #931669
Posted Wednesday, June 2, 2010 3:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:03 AM
Points: 11,194, Visits: 11,167
Oleg Netchaev (6/2/2010)
C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that.

Hey Oleg,

Log files are initialised with zeroes (there's some complexity around that - see Paul's blog and Jonathan Kehayias' entries:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx

Windows uses zero-initialised memory pages too (see Demand Zero Pages).

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #931704
Posted Wednesday, June 2, 2010 4:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:21 AM
Points: 1,676, Visits: 1,757
Paul White NZ (6/2/2010)
Oleg Netchaev (6/2/2010)
C. VLFs are overwritten with zeroes? No, in Windows nothing is overwritten with zeroes as there is no need to do that.

Hey Oleg,

Log files are initialised with zeroes (there's some complexity around that - see Paul's blog and Jonathan Kehayias' entries:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/13/does-the-tempdb-log-file-get-zero-initialized-at-startup.aspx

Windows uses zero-initialised memory pages too (see Demand Zero Pages).

Paul

Thank you Paul, it is good to know. Usually, Windows does not need to write any zeroes (when you delete files or format drive, just the records from allocation table are deleted and this is good enough), but initializing a log file with zeroes does make a lot of sense.

Since the question was about clearing existing log file, I think that my assumption about not overwriting existing inactive fragments with zeroes is still OK. If not then I guess I got lucky while dismissing options which I perceived as incorrect. In any case, I got my 2 points out of the question posted by Paul Randall, and to get those is never a trivial task.

Oleg
Post #931737
Posted Wednesday, June 2, 2010 9:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,034, Visits: 369
thanks Paul of all the QOTD, i find your questions the most difficult...
Post #931811
Posted Thursday, June 3, 2010 3:45 AM


SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, May 2, 2014 7:18 AM
Points: 457, Visits: 345
I "Lost" 2 points but "gained" a very good knowledge


_____________________________________________

Kindest Regards,
- Tushar
MCP,MCDBA(SQL 2000),MCTS(SQL 2005),MCITP(SQL 2005)

" Most Good Experience Comes From Judgement,
Most Judgement Comes From Bad Experience "
Post #931951
Posted Sunday, June 6, 2010 10:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, May 3, 2013 5:36 AM
Points: 220, Visits: 53
Another good question which made reading the relevant tech pages to answer it interesting and well worth the time.
Post #933244
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse