Unlimited Size of the Transaction Log File. Really?

When you create a new database in SQL Server and select Unlimited log file size, it seems that log will be unlimited, so it can grow and grow until the free space in the disk will be exhausted.

Unlimited log file size

But when you look at database file properties you’ll see another value. SSMS shows 2 097 152 MB. It’s merely 2 TB.

DMV shows another value.

Log file max size

It’s a maximum number of pages 8KB each. So it’s 2TB too.

This is the fact that described in MSDN article Maximum Capacity Specifications for SQL Server. So transaction log file can be as large as 2TB. And no more larger!

There’s another strange fact. According to the above mentioned article data file can be max 16Tb. At the same time SSMS shows unlimited size and sys.database_files shows -1.

Leave a Reply

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