The first DMV post

Posted: June 1, 2010 in DMV Collection

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.

USE YourDatabaseName;

GO

— 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]

FROM sys.database_files;

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.

Thanks Glenn


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s