We need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000\2005\2008\2008R2\2012\2014\2016 and 2017 SQL Server instances.
Checking for free disk space before proceeding with a process is a wise move if disk space is tight or a high percentage of the drive is needed for the process. It is disconcerting to have a process run for hours only to fail towards the end of the process due to insufficient disk space. Although a few different options are available to check for disk space (CLR, WMI, PowerShell, etc.) in SQL Server.
Sample Stored Procedure to Assess the Free Disk Space on a SQL Server Disk Drive
In the sample stored procedure below, it is accepting a parameter for the minimum amount of megabytes (MB) free on a specific disk drive, then executing the master.sys.xp_fixeddrives extended stored procedure into a temporary table. Once the data is in the temporary table the current amount of free disk space is compared to the minimum amount of free disk space to determine if the process should continue or raise an error.
There are different ways to do the same . But I am following with a customized script which allows us to perform below things from one Script :
- Free up space on drive
- you can perform shrink operation on mdf or ldf files by using the same script
- You can also delete Old backup file on Disk which are not required .
Please go through below link to find the script :
NOTE : I have been using this script without any issues .But still please do some testing of this script before executing it in your Production.