SQL SERVER – FIX: ERROR Msg 5169, Level 16: FILEGROWTH cannot be greater than MAXSIZE for file

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)
About these ads

2 thoughts on “SQL SERVER – FIX: ERROR Msg 5169, Level 16: FILEGROWTH cannot be greater than MAXSIZE for file

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s