Technology Musings
Here’s a quick little statement you can use to verify the size and growth settings for a SQL Server database. It doesn’t spell everything out for you, but if you’re aware of some common conventions (-1 = unlimited, 0 = no growth), it’s a fast and easy way to verify settings before issuing your ALTER DATABASE command. The filename is commented out by default, but provided just-in-case.
SELECT DB_NAME() [DATABASE] , name [Logical Name] , CEILING(SIZE/128.0) [SIZE (MB)] , CASE maxsize WHEN -1 THEN maxsize ELSE CEILING(maxsize/128.0) END [MaxSize (MB)] , CASE WHEN (status & 0x100000) = 0 THEN CEILING(growth/128.0) ELSE growth END [Growth] , CASE WHEN (status & 0x100000) = 0 THEN 'MB' ELSE '%' END [% or MB] --, filename FROM sysfiles
Some example output:
Database Logical Name Size (MB) MaxSize (MB) Growth % or MB ReportServer ReportServer 18 -1 1 MB ReportServer ReportServer_log 177 2097152 10 %