Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Script to Determine Free Space Prior to SQL Server Backup


By:   |   Last Updated: 2018-05-31   |   Comments (4)   |   Related Tips: More > Backup

Problem

We need to create a simple piece of T-SQL code that will pre-check the free disk drive space and estimate the database backup size to determine if there is enough space to run the BACKUP DATABASE statement for the selected disk drive.  This tool can be used to prevent errors concerning lack of disk space during the backup process and can save time of doing large backup operations that will eventually fail.

Solution

The solution involves creating a stored procedure in SQL Server that gets an input parameter for the disk drive letter where the database backup file will be created and an output bit parameter that its value will be:

  • 1 – If the drive has enough space for the database backup file (actually 15% more than the actual size of the backup) – In this case it will be OK to continue with the backup on this drive.
  • 0 – If the drive does not have enough space for the backup. In this case, don't run the backup until the disk is freed or expanded or another drive with more space is selected for the backup.

The backup estimated size is in KB and is calculated by using results taken from the sp_spaceused system stored procedure. The size of the database backup is roughly equal to the reserved space in the database as taken from the system stored procedure sp_spaceused.  This procedure calculates among other values how much reserved space there is in the database. It is shown by MVP Jonathan Kehayias in the following link: http://jmkehayias.blogspot.co.il/2008/12/estimating-size-of-your-database.html.  The drive free space estimation is calculated by executing the extended stored procedure xp_fixeddrives

This procedure returns a record set that contains the number of megabytes of free space for each physical drive associated with the SQL Server machine.  The procedure checks that the backup estimated size (KB) multiplied by 1.15 (15% size addition) is still less than the drive free space estimation (KB) and sets the output parameter value accordingly.

SQL Server Stored Procedure to Validate Space Exists for Database Backup File

This procedure needs to be created in each user database, since it queries tables local to the database to estimate the backup size. I will be checking the Northwind database, so I will create it in that database.

-- ================================================================
-- Author:      Eli Leiba
-- Create date: 04-2018
-- 05-2018 
--    1. Adding database name for check
--    2. Result is in MB
-- Description: Check if drive has enough space for DB backup
-- ================================================================
CREATE PROCEDURE dbo.DoesDBBackupHaveSpace (
   @drvLetter VARCHAR (5),
   @enoughSpaceForBackupFlag BIT OUTPUT
   )
AS
BEGIN
   DECLARE @estimatedBackSizeMB INT
   DECLARE @estimatedDriveFreeSpaceMB INT
   DECLARE @dbCheckMessage varchar(80)

   SET NOCOUNT ON

   SET @dbCheckMessage = concat ('Checking database ', DB_NAME ())
   PRINT @dbCheckMessage

   SELECT @estimatedBackSizeMB = round (sum (a.total_pages) * 8192 / SQUARE (1024.0), 0)
   FROM sys.partitions p
   JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
   LEFT JOIN sys.internal_tables it
      ON p.object_id = it.object_id

   CREATE TABLE #freespace (drive VARCHAR (5), MBFree DECIMAL (8, 2))

   INSERT INTO #freespace (
      Drive,
      MBFree) EXEC xp_fixeddrives

   SELECT @estimatedDriveFreeSpaceMB = MBFree 
   FROM #freespace
   WHERE drive = @drvLetter

   IF @estimatedBackSizeMB * 1.15 < @estimatedDriveFreeSpaceMB
      SET @enoughSpaceForBackupFlag = 1
   ELSE
      SET @enoughSpaceForBackupFlag = 0

   SELECT DatabaseName = db_name(),	
      Estimated_Back_Size_MB = @estimatedBackSizeMB,
      Estimated_Drive_Free_Space_MB = @estimatedDriveFreeSpaceMB,
      EnoughSpaceForBackupFlag = @enoughSpaceForBackupFlag

   DROP TABLE #freespace
   SET NOCOUNT OFF
END
GO

Example Using the Stored Procedure

Letís check my server that the C:\ drive has enough space for backing up the Northwind database.

We need to make sure we are in the database we want to check when we run the stored procedure.

USE Northwind
GO

DECLARE @enoughSpaceForBackupFlag bit

EXEC dbo.DoesDBBackupHaveSpace 'C', @enoughSpaceForBackupFlag OUTPUT
 
PRINT @enoughSpaceForBackupFlag
IF @enoughSpaceForBackupFlag = 1
   PRINT 'Continue to Backup...'
ELSE 
   PRINT 'Drive Space Problem...'
GO			

And the results are:

DatabaseName Estimated_Back_Size_MB Estimated_Drive_Free_Space_MB EnoughSpaceForBackupFlag
Northwind 9 49246 1

On the message tab we have this output.

Checking database Northwind
1
Continue to Backup...

This means that the C:\ drive has enough space for completing the backup of the Northwind database.

Next Steps
  • You can create this simple procedure in your application database and use it as a simple T-SQL tool for pre-checking database backup operations on databases.
  • The procedures were tested on SQL Server 2014, 2016 and 2017.


Last Updated: 2018-05-31


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, June 07, 2018 - 4:45:09 AM - Vesa Juvonen Back To Top

How about if backup compression is used (like everyone should)...?

I use a script that finds out last three actual backup file sizes and calculates approximate growth and then checks if there is enough space for that.

 


Thursday, May 31, 2018 - 10:17:11 AM - Barak G. Back To Top

Also, for automation / jobs you can check if the .bak file already exists in the required path.

The "INIT" means to overwrite the existing backup. "NOINIT" will append.

DECLARE @FileExists INT, @enoughSpaceForBackupFlag bit

EXEC @FileExists = master..xp_cmdshell 'DIR "c:\MyDB\MyDB.bak" /B', NO_OUTPUT

EXEC dbo.DoesDBBackupHaveSpace 'C', @enoughSpaceForBackupFlag OUTPUT

IF @FileExists != 1 OR @enoughSpaceForBackupFlag = 1

BACKUP DATABASE [MyDB] TO† DISK = N'c:\MyDB\MyDB.bak'†

WITH† COPY_ONLY, NOFORMAT, INIT,† NAME = N'MyDB',†

SKIP, NOREWIND, NOUNLOAD,† STATS = 10

ELSE

PRINT 'Drive Space Problem...'


Thursday, May 31, 2018 - 9:58:57 AM - Junior Galv„o MVP Back To Top

 Hi, Eli.

Great post, congrats.

Regards.

 


Thursday, May 31, 2018 - 7:43:46 AM - Keith Brockman Back To Top

 

 Thank you for your work.  I did not know about xp_fixeddrives.  As I am working on moving from SQL Server 2016 to SQL Server 2017 I am always looking for ways to improve my existing automation and will definitely be integrating what you have.  As I use compress on the backup command I will have to exam the ratio I get from the space query you provided.

 


Learn more about SQL Server tools