I am posting all the DMV queries that I find that can be really helpful. The first one that I want to post in from Glenn Berry, I saw him speak at the SQL Server User Group in Colorado Springs last month and it was awesome. The first thing you may want to look at if you are building your tool belt is the A DMV a Day series on SQL Server Central. Glenn is a fellow SQL Server MVP and was kind enough to re-post some of his DMV’s.
So without wasting any more time…
One that I think everyone should use as a regular job is related to how large is your database? Is it growing, where is the growth going be in a year? This is not a DMV as much but this follows the same pattern of managing you server by the code you use.
— Individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS [Available Space In MB]
I will see if I can add some more to this post to show you how I would create this, and how I would start to track the history of the database size.