Before we proceed with several tasks that run on SQL Server 2000, 2005, 2008, 2008R2, 2012, 2014, 2016, and 2017 SQL Server instances, we need to check for a required amount of free space.
If disk space is limited or a large portion of the drive is required for the procedure, checking for free disk space before starting it is a good idea. It’s inconvenient to have a process run for hours only for it to fail at the end owing to a lack of disc space. Although there are a number various ways to check for disk space in SQL Server (CLR, WMI, PowerShell, etc.).
A Stored Procedure to Determine the Free Disk Space on a SQL Server Disk Drive.
Accepting an input for the minimum amount of megabytes (MB) free on a certain disk drive, the sample stored procedure below executes the master.sys.xp fixeddrives extended stored procedure into a temporary table. The current amount of free disk space is compared to the minimal amount of free disk space to determine whether the process should continue or produce an error once the data is in the temporary table.
There are numerous approaches to accomplish the same goal. However, I’m using a customized script that allows us to do the following with just one script:
- Free up space on your drive.
- Using the same script, you may shrink mdf or ldf files.
- You can also remove any old backup files on your hard drive that are no longer needed.
Please see the following URL for the script:
NOTE : I’ve been using this script for a while now and have had no problems with it. However, before putting this script into production, please test it first.