Redefine Transaction log size restricion fails

Question

I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue:

The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the capacity of the drive.

I have tried to set it to some other value e.g. 85 GB (something between actual TL size and max drive capacity) as well as to unrestricted.

Neither of those settings is getting persisted. The original restriction setting returns again and again. Why is it impossible to change?

(Note: All DBs are running in Compat Lvl 80)

I ran the command:

ALTER DATABASE myDB 
MODIFY FILE ( NAME = N'myDB_Log', MAXSIZE = UNLIMITED)

against several servers. Some of them afterwards show MAXSIZE=-1. Others show max_size=268435456 in sys.database_files. The ones having -1 also show the correct radio button selected in the dialogue.

Julien Vavasseur · Accepted Answer · 2016-02-04T16:02:42.540

If you look at Maximum Capacity Specifications for SQL Server, you will see that the maximum size for transaction log is 2 TB:

SQL Server Database | Maximum sizes/numbers    | Maximum sizes/numbers  
Engine object       | SQL Server (32-bit)      | SQL Server (64-bit)  
 ...                | ...                      | ...
File size (log)     | 2 terabytes              | 2 terabytes

Then once converted to MB:

2 TB <=> 2.048 GB <=> 2.097.152 MB

According to ALTER DATABASE File and Filegroup Options, Unlimited means that the value is set to the maximum file size:

MAXSIZE { max_size| UNLIMITED }
...
UNLIMITED
Specifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

Either of these query should set it to unlimited:

USE [master]
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydb_log', MAXSIZE = 2097152MB)
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydb_log', MAXSIZE = UNLIMITED)

Using this query once set to UNLIMITED:

SELECT max_size, max_size/1024*8 FROM sys.database_files

Output for row 2 (LOG) should be:

maxsize     | maxsize in MB
268435456   | 2097152

268435456 is the number of 8 KB pages
2097152 is the same number converted to MB (<=> 2 TB)

Although maximum size cannot be set to a negative value, sys.database_files may show -1 for maximum size:

Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.

The maximum value for MAXSIZE using ALTER DATABASE ... MAXSIZE ... is 2147483647 pages (<=> 16777208 MB => 16 TB) but with a LOG file and a size above 2097152 MB, it fails with this error:

Msg 5150, Level 16, State 2, Line 1
The size of a single log file must not be greater than 2 TB.

Besides 2 TB is also probably an old limitation due to MBR:

Master boot record
... Since block addresses and sizes are stored in the partition table of an MBR using 32 bits, the maximum size as well as the highest start address of a partition using drives that have 512-byte sectors (actual or emulated) cannot exceed 2 TiB−512 bytes (2,199,023,255,040 bytes or 4,294,967,295 (232−1) sectors × 512 (29) bytes per sector). Alleviating this capacity limitation was one of the prime motivations for the development of the GPT.
Source: Wikipedia

Redefine Transaction log size restricion fails

1 Answers1