Find the Drive Space,free and percentage Using T-SQL in SQL Server

Introduction

xp_fixeddrives only provide how much disk space is free on each drive but not the maximum space of the drive & percentage of free space. If you need to find out Drive Space for SQL server with Drive Space, Free space and percentage of free space then we need to create a stored procedure ‘sp_diskspace‘.

Ole Automation Procedures

But !!!! before that, you need to enable Ole Automation Procedures feature. Ole Automation Procedures feature must be disabled unless specifically required and approved. To more about visit: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ole-automation-procedures-server-configuration-option?view=sql-server-2017

To enable Ole Automation Procedures feature we may follow below steps

Ole Automation Procedures is enabled.

sp_diskspace

Now we will create ‘sp_diskspace’ with the below script.

Download sp_diskspace in txtsp_diskspace in zip 

After successfully execution below messages will appear.

And we can also find the stored procedure in the MASTER database.

To run that stored procedure we need to execute below query

The result will be displayed like below snapshot.

Same output by a script

Now if you don’t want all this HUNGAMA!! of creating store procedures and enabling  Ole Automation Procedures feature then another simple solution is waiting for you. We can also get our desire result by running a simple script also. Simply run the below script. But the disadvantage is that below script will display only that disk status which contains only SQL Server files/data.

 


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *