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:

https://sqlserveradmintutorial.blogspot.com/2018/06/t-sql-script-to-free-disk-space-in-sql.html

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.

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.