SQL Server - How to identify and monitor disks, free and used total disk space

Views: 3.166
Reading Time: 8 minutes

Hello people,
All right with you ?

In this post I will demonstrate a very interesting and common feature in the everyday life of a DBA, which is monitoring disk space on the server. As a DBA, you should always have control over server disk space, so you don't let that space reach critical levels and you have to try to resolve at the last minute, with the database at risk of stopping for lack of data. Disk Space.

This post is a complement to my post. SQL Server - How to identify and monitor total free disk space used by database datafiles, where I demonstrated how to identify, analyze and monitor the database space disk space.

Disk information using DMV's

In this first example, I will demonstrate how to identify disk space information using the sys.master_files view and DMV dm_os_volume_stats (The dm_os_volume_stats view was made available from SQL Server 2008 R2 SP1).

Result:

Disk Information Using xp_fixeddrives

In this example, I will demonstrate how to return free disk space on drives using the simple xp_fixeddrives procedure.

Result:

Disk Information Using OLE Automation

In this third example, I will demonstrate how to identify disk space information using Scripting.FileSystemObject and the GetDrive method, as well as the xp_fixeddrives procedure.

Result:

Disk Information Using xp_cmdshell and WMIC

In this example, I will demonstrate how to identify disk space information using the Windows Management Instrumentation Command-line (WMIC) binary and the Stored Procedure xp_cmdshell.

Result:

Disk Information Using CLR

In this last example, I will demonstrate how to identify disk space information using the CLR.

Result:

Monitoring free disk space

Guys, now that I've demonstrated several ways to check disk space on your server (there are several others on the internet), I'll show you how to monitor disk space on your server and send alerts via email. In this example, I will use the solution from the first example, as it is simple and practical to implement, but you can create this monitoring with any of the solutions presented above.

To assist in generating the HTML code, I will use stpExporta_Table_HTML_Output, but could also use fncExporta_Query_HTML, available at:

To send the email, I will use the Stored Procedure msdb.dbo.sp_send_dbmail, from SQL Server DatabaseMail. If you have not set up or did not know how to do it, learn more by visiting SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).

Monitoring source code:

Example of use:

Result:

That's it folks!
To the next.
Hug.

SQL Server - how to identify query identify view monitor monitor disks, total disk space, free and used disk space used available command line command line transact sql query tsql

SQL Server - how to identify query identify view monitor monitor disks, total disk space, free and used disk space used available command line command line transact sql query tsql