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
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 !!!
Sr. Premier Field Engineer