Transaction Log Backups – How long to keep them?

Introduction In this post I will give you a tip on how long it would be good to keep transaction logs. To understand why, basics of point-in-time recovery from backups are explained. If you are in a hurry: keep transaction

Posted in Transaction log

Sort records without ORDER BY?

How often you see some smartie “optimizes” the query by removing ORDER BY, justifying that query always goes by that index, and index is in desired order for the result? Or they say: “I executed this query thousands of times

Posted in Uncategorized

Do you measure query IO with SET STATISTICS IO ON ?

Introduction In our tuning work and often in presentations we see people use SET STATISTICS IO ON as a handy way to measure IO, especially logical reads. But, not many people know that it skips measuring IO from certain types

Posted in Uncategorized


Are you still using READ COMMITTED transaction isolation level? Default transaction isolation level on all SQL Server versions (2000-2014) has serious inconsistency problems, by design. Not many people are familiar how and why is it happening. I am writing this

Posted in Uncategorized

Disk failure probability on a large number of disks

Introduction We either have or will experience a drive failure. Doesn’t matter if we talk about HDD, SSD, or PCIe disks, any storage disk drive will fail eventually. But how probable it really is, especially if we have many drives?

Posted in Storage

Transaction log myths

There are some myths widely spread about transaction log that are to be debunked here: Full/diff backup will clear the transaction log FALSE. Only transaction log backup in full and bulk_logged recovery model, or checkpoint in simple recovery model will

Posted in Transaction log

Procedure with Execute as login?

Sometimes we need a low-privileged user to do a specific administration task or task that require some server-level permissions (such as VIEW SERVER STATE, ALTER TRACE etc). Of course, we do not want to give that account server-level privilege, because

Posted in Security and encryption

Set transaction log size and growth

Introduction What is the best practice, how to properly set-up transaction log initial size, growth, and number of VLFs? There are many articles on the net on that theme, and some of them are suggesting really wrong concepts, like setting

Posted in Transaction log

Transaction log Truncate vs Shrink

Transaction log truncate – why it didn’t shrink my log ? (Truncate vs Shrink) Introduction One term that makes the most confusion between people dealing with sql server transaction log is “log truncation”. In this article I’ll explain what log

Posted in Transaction log

Transaction log survival guide: Shrink 100GB log

Introduction You got yourself in situation where transaction log has massively grown, disk space became dangerously low and you want a quick way to shrink it before server stops ? There are many articles on the net on that theme,

Posted in Transaction log

M.Sc. Vedran Kesegić

M.Sc. Vedran Kesegić

A Random Thought

The worst place in the world to store your backups is on the same portion of the I/O subsystem as the databases themselves.