SQL Server Datafile maxsize reported incorrectly

Recently we encountered and filed a benign reporting BUG which reports the maxsize of the data file incorrectly. If you create a database & then alter the initial size of the database to be greater than maxsize & then query sys.database_files, sys.master_files, sysaltfiles the maxsize appears to be incorrectly reporting the older value of maxsize. However the maxsize is actually set to the new size within the database which can be verified by reading the File header data, it’s just reported incorrectly in the DMVs.

Further restarting the sql instance or taking the database offline & then online fixes the issue as the dmvs gets synchronized with file header at the database startup

The issue occurs only in SQL 2012 & SQL 2014 while it appears to be fixed in SQL 2016 RC0 & doesn’t occur in SQL 2008 R2 & below.

Following is the steps to repro the issue

REPRO

IF EXISTS (
SELECT *
FROM sys.databases
WHERE name = N'maxsize'
)
DROP DATABASE maxsize
GO

CREATE DATABASE maxsize
ON PRIMARY
(NAME = maxsize_data,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\maxsize_data.mdf',
SIZE = 64MB,
MAXSIZE = 1024MB,
FILEGROWTH = 10%)
LOG ON
( NAME = maxsize_log,
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\maxsize_log.ldf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 10%)

GO

USE [master]
GO
ALTER DATABASE [maxsize] MODIFY FILE ( NAME = N'maxsize_data', SIZE = 4194304KB )
GO

USE [maxsize]
GO
select * from sys.database_files
GO
select * from sys.sysaltfiles where dbid = DB_ID()
GO
Select * from sys.master_files where database_id = DB_ID()
GO

/*
SQL 2012\SQL 2014
size = 524288 maxsize = 131072
*/

--Taking the database offline & bring it back online

alter database [maxsize] set offline
GO
alter database [maxsize] set online
GO

/* Reports accurately */
use maxsize
GO
select * from sys.database_files
GO
select * from sys.sysaltfiles where dbid = DB_ID()
GO
Select * from sys.master_files where database_id = DB_ID()
GO

/*
SQL 2012\SQL 2014
size = 524288 maxsize = 52488
*/

USE MASTER
GO
DROP DATABASE [maxsize]
GO

Like a mentioned before, it is just a reporting bug & shouldn’t do any harm but just in case you see maxsize value greater than the size column field of data files don’t be surprised 🙂

Hope this helps !!!

Parikshit Savjani
Sr. Premier Field Engineer