Transaction Log and Simple Recovery Model

Question

Post reply

Transaction Log and Simple Recovery Model

Meet George Jetson

SSChampion

Points: 11627
More actions
December 30, 2010 at 10:06 am

#243025

Regarding the transaction log file on a database set to Simple Recovery. SQL Server will truncate the transaction log file each time the database reaches a "transaction checkpoint." Other than a full backup of the database, what are some other "transaction checkpoints?"
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

Viewing 6 posts - 1 through 5 (of 5 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms189573(v=sql.90).aspx

---------------------------------------------------------------------

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

SQL automatically runs a checkpoint on the databases on a regular basis.

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

Adiga One Orange Chip Points: 27284 More actions · Answer 3

This article has the details.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Meet George Jetson SSChampion Points: 11627 More actions · Answer 4

Thank you all! As a followup, does a delete record take up more space in the transaction log than an insert and if so, is there any way to know how much more (2 times as much 3, 5?)

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

chris.s.powell (12/30/2010)
Thank you all! As a followup, does a delete record take up more space in the transaction log than an insert and if so, is there any way to know how much more (2 times as much 3, 5?)

yes! there is a way, you can test it 😀 ... and do not forget to include update statement too.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.