GUI and the T-SQL statement are using different limitations for autogrowth in MB - by Pieter Vanhove

Status : 

 


2
0
Sign in
to vote
ID 812486 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 12/30/2013 1:38:13 AM
Access Restriction Public

Description

If you enter a large number like 9999999999 in the GUI of SQL Server autogrowth setting in % of a data file, it will change it back to the following value 2147483647, which is the maximum value of an integer. This is the same limitation when you try the following statement

ALTER DATABASE [testgrowth] MODIFY FILE ( NAME = N'testgrowth', FILEGROWTH = 2147483647%). Any value larger than 2147483647, I will get an Incorrect syntax error.

I entered 9999999999 into the MB autogrowth field and the SSMS automatically changed it into 1048576 MB. It looks like the autogrowth in MB is limited to 1 TB and not 2147483647 like the percentage growth. It’s a completely different value.

If I try a T-SQL statement ALTER DATABASE [testgrowth] MODIFY FILE (NAME = N'testgrowth', FILEGROWTH = 2147483647MB) (a larger number will give me the incorrect syntax) I got the following error message:
Msg 1842, Level 16, State 1, Line 1
The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.

Let’s see what happens when I do a T-SQL statement that is larger than 1048576MB (GUI limit) and smaller than 2147483647 (T-SQL limit).
ALTER DATABASE [testgrowth] MODIFY FILE (NAME = N'testgrowth', FILEGROWTH = 2097152 MB) completes successfully. 
Wasn’t that limited to 1TB by the GUI?

Let’s open the GUI again to see how SSMS deals with it. Well… not quite good… See attachment for error message.
So GUI and the T-SQL statement are using different limitations for autogrowth in MB.

Sign in to post a comment.