Clearing the Transaction Log

Question

Clearing the Transaction Log

Viewing 6 posts - 16 through 20 (of 20 total)

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

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
All articles available on SQL.kiwi
@SQL_Kiwi

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 2

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

ziangij SSCrazy Eights Points: 8066 More actions · Answer 3

thanks Paul 🙂 of all the QOTD, i find your questions the most difficult...:-)

U J SSCrazy Points: 2423 More actions · Answer 4

I "Lost" 2 points :angry: but "gained" a very good knowledge 🙂

jrandolph SSC Veteran Points: 224 More actions · Answer 5

Another good question which made reading the relevant tech pages to answer it interesting and well worth the time.

rahulddeshmukh SSC Enthusiast Points: 112 More actions · Answer 6

good Q on Transaction Log Physical Architecture and Virtual Log Files