MS SQL: Find total server disk space in T-SQL

Hello. Quick post today. I needed to find the total disk space for a remote server with access only to T-SQL. No PowerShell or file system sneak-peek was allowed. Actually I would send my queries to someone else run it, and only then I could look onto the results.

So starting of this post from Pinal Dave and this MSDN article I came up with a quick query. It’s not exactly elegant, but gets the job done. SQL 2008 R2+ code ahead.

SELECT distinct(volume_mount_point), 
  total_bytes/1048576 as Size_in_MB, 
  available_bytes/1048576 as Free_in_MB
FROM sys.master_files AS f CROSS APPLY 
  sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  available_bytes/1048576 order by 1

This solution was also posted on SQLServerCentral forums. That is a great place to search for such things.

Also get some familiarity with sys.dm_os_sys_info and EXEC xp_msver; – those can be of great help in such dire situations.

cya!

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to MS SQL: Find total server disk space in T-SQL

  1. tsqlfun says:

    Thank you for this query. Unfortunately, this query returns disk space informations about volumes that host database files. If a server have a volume that don’t host a database file, the query don’t dispay any information for that volume.

    • mauriciorpp says:

      Hello. Thats a good point. Unfortunately I’m not able to search for this improvement, and if the DB servers are dedicated (as they should, most of the time) this will not be a big issue. But thanks for pointing it out!

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