Microsoft SQL Server Myth #1

Full backup clears the transaction log which may be shrunk afterwards.


I had a whole afternoon argument about this myth with my colleague. He was so persistent. After two days he finally admitted that I’m right.

When you create a database you have to decide whether business requirements allow some data loss (let’s say one day) then you are just fine with simple recovery model and performing full or differential backups (transaction log is not preserved after transaction is committed). In case of minimal data loss like 5 minutes full recovery model is unavoidable. You would perform all three backup types: full, differential, transaction.

Log file has two portions – inactive and active. In active portion of the log are currently running uncommitted transactions. When transaction is committed and all transactions which started before are committed as well transaction is logically moved into inactive portion of the log. When transaction log backup occurs all transactions within inactive part of the log are flushed into backup file.

Full backup – backups all data pages as they are.

Differential backup – backups all data pages that were changed since last full backup

Transaction backup – backups all transactions from inactive part of transaction log since last transaction log backup.


About Jiří Hubáček

Experienced consultant and blogger for Microsoft SQL Server and related products. Likes cats, workouts and delicious food. Enjoys sailing, traveling, time spent with wife and friends.
This entry was posted in Microsoft SQL Server Myths and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s