My Transaction Log File Is How Big?

SQL Server

How bizarre

So there’s this funny thing about SQL Server: many units of measure boil down to 8k pages. That’s the size of a data page, so when you measure reads, or size, sometimes the only thing you can do is convert that to MB or GB, or if you’re super lucky, TB.

But transaction logs don’t have pages. Not 8k, not any other kind. Log files are full of Virtual Log files, or VLFs. These VLFs hold transactions, and can vary in size if you have your logs set to grow by a percentage.


Which brings up a curious bit of documentation for sys.database_files and sys.master_files.

So… that’s how big?

Ah, right.

Options to convert

All of these queries give me correct results. The math has been around forever in a billion blog posts, and it’s not that interesting. Dividing by 128 or multiplying by 8 and then dividing by 1024 will get you to the same place.

Where does it come from?

Running sp_helptext on sys.database_files and sys.master_files sheds some light on things. Namely, that it doesn’t want light shined (shone?) upon it.

Trying to query any of these system views will fail miserably.

You have my permission to deny

Really, the only hint is sitting in a function that… you guessed it, we don’t have access to!


What was the question?

Given the amount of potential confusion that it could cause, it’s an odd choice for Microsoft to store log files in sizes apparently converted to 8k pages. It makes querying the data a bit easier, which I appreciate, and perhaps it’s just the documentation that could use an update.

Thanks for reading!

Previous Post
Performance Tuning? Plan the Work, Work the Plan.
Next Post
Things Your SQL Server Monitoring Software Should Tell You

2 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.