Setting MAXSIZE on datafiles using Management Studio in SQL Server
When attempting to set a MAXSIZE value for a non-logfile
group datafile, any value greater than 2Tb (2,097,152 MB) will result in
the following error:
"Msg 5040, Level 16, State 1, Line 1 MODIFY FILE failed. Size is greater than MAXSIZE"
This seems to be a bug in the SQL Server Management Studio. The size restriction IS applicable when dealing with logfile datafiles but it is rather confusing why this restriction is applied to datafile files.
Workaround:
The simple solution is to apply the restriction as a Tranact-SQL command
ALTER DATABASE [DATABASE NAME]
MODIFY FILE
(NAME = [filename],
MAXSIZE = [size]Mb);
GO
"Msg 5040, Level 16, State 1, Line 1 MODIFY FILE failed. Size is greater than MAXSIZE"
This seems to be a bug in the SQL Server Management Studio. The size restriction IS applicable when dealing with logfile datafiles but it is rather confusing why this restriction is applied to datafile files.
Workaround:
The simple solution is to apply the restriction as a Tranact-SQL command
ALTER DATABASE [DATABASE NAME]
MODIFY FILE
(NAME = [filename],
MAXSIZE = [size]Mb);
GO
Comments