Checking database file sizes without a GUI

In: SQL Server


19 Aug 2008

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	%

And an image from SSMS to verify:
File growth in GUI

  • Share/Bookmark

Comment Form

Categories

Archives

  • Kurt Hitchen: A friend has let me "see" there calendar in Google Calendar, but because their calendar is set to pr [...]
  • bugoy8: https://www.google.com/calendar/dav/nba_13_%4cos+%41ngeles+%4cakers#sports@group.v.calendar.google.c [...]
  • Speaker: Wasn't able to understand how to access the shared folder in the quest OS from this tutorial. Hmmm [...]
  • Elena Kuznetsova: I have killed an hour to figure out how to sync Google contacts' birthday and I have this done! U [...]
  • Andy: I have been trying to figure this out forever.....Thank you ever so much!! [...]


This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 United States.
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 United States.