SQL Server TSQL Script to Free Disk Space

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: