I am writing this blog post right after I resolve this error for one of the system. Recently one of the my friend who is expert in infrastructure as well private cloud was working on SQL Server installation. Please note he is seriously expert in what he does but he has never worked SQL Server before and have absolutely no experience with its installation.
He was modifying database file and keep on getting following error. As soon as he saw me he asked me where is the maxfile size setting so he can change. Let us quickly re-create the scenario he was facing.
Error Message:
Msg 5169, Level 16, State 1, Line 1
FILEGROWTH cannot be greater than MAXSIZE for file ‘NewDB’.
Creating Scenario:
CREATE DATABASE [NewDB]
ON PRIMARY
(NAME = N'NewDB',
FILENAME = N'D:\NewDB.mdf' ,
SIZE = 4096KB,
FILEGROWTH = 1024KB,
MAXSIZE = 4096KB)
LOG ON
(NAME = N'NewDB_log',
FILENAME = N'D:\NewDB_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%)
GO
Now let us see what exact command was creating error for him.
USE [master]
GO
ALTER DATABASE [NewDB]
MODIFY FILE ( NAME = N'NewDB', FILEGROWTH = 1024MB )
GO
Workaround / Fix / Solution:
The reason for the error is very simple. He was trying to modify the filegrowth to much higher value than the maximum file size specified for the database. There are two way we can fix it.
Method 1: Reduces the filegrowth to lower value than maxsize of file
USE [master]
GO
ALTER DATABASE [NewDB]
MODIFY FILE ( NAME = N'NewDB', FILEGROWTH = 1024KB )
GO
Method 2: Increase maxsize of file so it is greater than new filegrowth
USE [master]
GO
ALTER DATABASE [NewDB]
MODIFY FILE ( NAME = N'NewDB', FILEGROWTH = 1024MB, MAXSIZE = 4096MB)
GO
I think this blog post will help everybody who is facing similar issues.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
