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 (https://blog.sqlauthority.com)
2 Comments. Leave new
What is the reason for setting MAXSIZE? What happens when the production database reach it?
If MAXSIZE is not set, still you will reach to the same state once the database consumes complete available disk space of production database/server.
Anyone knows how to set to recycle database space automatically. In other words by setting a flag it should delete the old data and make room for new data. As per my knowledge this can be achieved by sliding window, but handling is a problem. Like creating partitions and set up job which will remove old partitions and add new partitions on regular interval.