I had a query from a colleague this morning regarding the size of a database on one of our SQL Server 2005 clusters. He wanted to know whether it had run out of space, as the database is not configured to grow automatically.
I queried the 'sys.database_files' view on the database in question in order to determine it's current size, only to find what I thought to be a mismatch between what values were being returned in the size, max_size and growth fields and those returned from the SSMS GUI. Upon reading the MSDN article for sys.database_files, it appears that the values that are returned don't represent the actual file size but the file size in 8KB pages. Therefore I've concocted a SELECT statement which can be executed against a database and will return what I think is the more pertinent values in their expected form.
SELECT name as [SQL Logical Name], physical_name as [File Path],
size * 8/1024 as [Current Size (MB)],
max_size * 8/1024 as [Maximum Size(MB)],
growth * 8/1024 as [Growth Increment (MB)]
FROM sys.database_files
As someone who dabbles with SQL (usually when something's went wrong), I find the above T-SQL very useful for assessing database sizes.
No comments:
Post a Comment