Home 2017 March SQL Server: Use xp_fixeddrives Procedure to monitor the Disk Space of Server

SQL Server: Use xp_fixeddrives Procedure to monitor the Disk Space of Server

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

You can use xp_fixeddrives system stored procedure to find the free space SQL Server fixed Disks. The SQL Server admin can easily find the free space of disk by looking to file explorer of Server.

But we should use this stored procedure to set the automatic alert on free disk space of Server hard disk so that DBA can take the decisions accordingly. Due to insufficient free hard disk space, we can prevent the accidental failure or SQL Server.

Below is a small demonstration, I created a sample table to store the result of xp_fixeddrives procedure which we can use for a further alert.

Get the information of Server Disk free space:

Create a sample table for storing the result of xp_fixeddrives:

Execute xp_fixeddrives and store the result in tbl_SQLServerDiskFreeSpace:

Anvesh Patel
SQL Server Interview: Use DBCC SQLPERF to monitor the Transaction Log File Size for all DatabasesSQL Server: fix error 3183 RESTORE detected an error on page (0:0) in database as read from the backup set
Comments: 3
  1. Bruce
    March 22, 2017 at 10:22 pm

    I love when this one gets trotted out every couple of years. The biggest thing everyone avoids is the ugly truth: the call to xp_fixeddrives must run as a SysAdmin level security right. Otherwise you get no results.

    Trying to use this in automation suddenly becomes a lot harder because you now have to give your automation access to a very dangerous set of rights on your server. It’s even worse if your automation doesn’t have a way to store encrypted passwords, and can not run in the context of a Windows domain user account.

    If you are running on SQL 2008 R2 or later, then you have access to a DMV called: sys.dm_os_volume_stats

    I have never used it because I am stuck on 2008 (pre R2) servers, but you can search Google to find lots of articles on this exact topic.

    It’s not to say that this is not a really cool officially undocumented procedure. It is fast and simple. It just requires elevated rights to work.

    ReplyCancel
    • Anvesh Patel
      March 23, 2017 at 6:23 pm

      Hey Bruce, thanks for adding this, yes only problem with sysadmin level permission….

      ReplyCancel
  2. Bruce
    March 22, 2017 at 11:34 pm

    An alternate way to leverage this for automation, is to use it within a SQL Agent job. Depending on how you have setup security on your SQL Agent process, it likely runs as a SysAdmin equivalent.

    If you create a job that runs xp_fixeddrives for you, you could have it write to a monitoring database on the server. Then any automation could poll that table.

    I am going that route for my solution. I have added a simple integer identity column to my results (plus a datetime filed) that my automation can use as an ascending value to use in a reporting system import process. I can flush out old data when I feel it is getting to large.

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

March 9, 2017 3 Comments SQL Server, , , , , , , , , , , , , , ,