Managing Free Space can become a problem quickly, especially when you have to manage multiple database servers, in multiple environments, each server with multiple Hard Drives / LUNs. Some companies spend money on monitoring tools like SCOM, others try to manage it in-house by developing their own software/processes/reports to show free space per volume per.

You could quickly determine Free Space per HDD/LUN by executing this tsql command, but it doesn’t tell you the TOTAL space per HDD/LUN: xp_fixeddrives. It will give you the free space in MB per drive.

When I connect to a server to free up space, the first thing I do is open explorer, click on My Computer and arrange the columns in this order by dragging the columns around:

1) Name

2) Free Space

3) Total Size

This makes it easier to read. See the diagram below to get some idea. You can ask explorer to sort according to a column by clicking on the column, but sadly it doesn’t handle it well/correctly.

Warning: When checking space, be very careful not to overlook MB and think it is GB.

Space saving techniques:

- Find what is no longer used/needed and delete/archive it. E.g.

- Clean out C:\Temp and C:\Windows\Temp. Windows won’t let you delete the temp files that are currently in use. Delete the blue $...$ files in the Windows folder. Drawback is you won’t be able to rollback any Windows Updates that was applied. I would leave this to a last resort for the really desperate.

- Find out which sql backup files (.BAK/.UBAK) are lying around and delete/archive the ones that are no longer needed.

- Find out which detached database files are lying around and delete/archive them

- Shrink each of the files per database to reclaim the allocated free space. Jonathan Kehayias wrote a pretty good article called “Monitor free space in the database files” to achieve just that. You can find it at: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=FileSpaceMon

- Determine the space allocation and usage per table and then dealing with that. E.g. Dropping/Archiving tables that are not needed any longer.

This article focuses on finding (a) detached db files and (b) old backup files.

To achieve this I mainly use the DOS DIR command that searches for the .MDF, .NDF, .LDF, .BAK and .uBAK files. Then I compare the list of database files (.MDF, .NDF, .LDF) that were found against sysaltfiles to see which of these files aren’t in use by the instance. If you’re awake you’ll realize that this is only suitable for servers that has only 1 sql instance, unless you run the code against each sql instance. This is because when you run the code, it is like saying "WHICH FILES HAS THE DISKS GOT THAT ISN'T IN A PARTICULAR SQL INSTANCE". Ideally we would like to say: "WHICH FILES HAS THE DISKS GOT THAT AREN'T USED BY ANY SQL INSTANCE ON THIS SERVER". Still it is very handy on single instance servers.

Enough talk - let’s get to the good stuff. This code is compatible with SS2000, 2005 and 2008.

There is 1 pre-requisite; your SQL Server must allow DOS commands to run. So if you have prevented this, you’ll have to re-enable this functionality for the script to run. I do a quick check at the beginning of the script to determine if the pre-requisite is enabled.

The script is broken into 3 Sections listed below:

1. Gather data into temp tables
2. Reports that pulls out what we are interested in. I created 4 reports for you. Look at the examples further on:
   • REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME
   • REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME
   • REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE
   • REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST
3. Discard temp tables

Procedure to get this working: Run the code in Section 1. Then run each report in turn in Section 2. Run Section 3 to do the cleanups. Should you want to discuss anything RE this, you can contact me on: paul_els@hotmail.com

Script Section 1 – Gathering data needed for our reports

-- OBTAIN A LIST OF ALL THE DRIVES ON THE SERVER
set nocount on
CREATE TABLE #Drives (DriveLetter char(1), MBFree int)
INSERT INTO #Drives
EXEC master..xp_fixeddrives
-- DECLARE VARIABLES
DECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)
-- FIND THE FIRST AND LAST DRIVES FOR THE LOOP
SELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives
-- CREATE THE TABLE TO HOST THE LIST OF FILES
CREATE TABLE #Files (
  autono_id int NOT NULL IDENTITY (1, 1), 
  RawData varchar(255), 
  FilePath varchar(255), 
  DriveLetter CHAR(1), 
  [FileName] varchar(255), 
  FileSize varchar(17), 
  FileSizeInMB decimal(18,2), 
  FileSizeInGB decimal(18,2)
  )
WHILE @CurrentDriveLetter <= @MaxDriveLetter
BEGIN
    -- STORE THE FILES WE ARE LOOKING FOR IN THE #FILES TABLE
    -- PRINT STR('dir ' + STR(@CurrentDriveLetter) + ':\*.mdf;*.ndf;*.ldf /s')
      SELECT @EXECSTR = 'dir ' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.mdf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ndf;' + 
  CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ldf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ubak;' + 
  CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.BAK /s' -- string in the drive letter later
      INSERT INTO #Files (RawData)
      EXEC master..xp_cmdshell @EXECSTR
    -- PRINT @EXECSTR
      select @CurrentDriveLetter = MIN(DriveLetter) from #Drives where DriveLetter > @CurrentDriveLetter
END
-- CLEAN UP #FILES
update #Files
 set FilePath = REPLACE(RawData,'Directory of ','')
 where RawData like '%Directory of %:%'
update #Files
   set FilePath = SubString(FilePath, 2, 255)
 where FilePath is not null
delete from #Files
 where RawData is NULL
      or RawData = 'File Not Found'
      or RawData like '%Volume%'
      or RawData like '%File(s)%'
      or RawData like '%Dir(s)%'
      or RawData like '%Total Files Listed:%'
update #Files set [FileName] = substring (RawData, 40, 255) where FilePath is NULL
update #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULL
update #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULL
update #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULL
update #Files
   set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,
       FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024
DECLARE @autono_id int, @fp varchar(255), @drive char(1)
select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter
 from #files F1
 where FilePath is not null
 and autono_id < (select max(autono_id) from #Files where FilePath is NULL)
 order by autono_id desc
WHILE @autono_id IS NOT NULL
BEGIN
      update #Files
         set [FilePath] = @fp, DriveLetter = @Drive
       where autono_id > @autono_id and [FilePath] is NULL
      DELETE from #Files where [FileName] is null AND DriveLetter = @Drive AND autono_id > @autono_id
      SELECT @autono_id = NULL, @fp = NULL, @drive = NULL -- RESET FLAGS
      select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter
        from #files F1
       where FilePath is not null
         and autono_id < (select max(autono_id) from #Files where FilePath is NULL)
       order by autono_id desc
END
delete from #Files where FileName is NULL or FilePath like '%i386%' or FilePath like '%ia64%'

Script Section 2 – Run each report respectively

-- REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME
select 'DriveLetter' = Ltrim(rtrim(LEFT(DriveLetter,1))),
       'Potential Saving (in MB)' = LTRIM(STR(SUM(FileSizeInMB))),
       'Potential Saving (in GB)' = LTRIM(STR(SUM(FileSizeInGB)))
  from #Files
 where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)
 group by Ltrim(rtrim(LEFT(DriveLetter,1)))
 order by Ltrim(rtrim(LEFT(DriveLetter,1)))
-- REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME
select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),
       'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB
  from #Files
 where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)
   and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES
   and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB
 order by Ltrim(rtrim(LEFT(DriveLetter,1))) asc, 3 desc
-- REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE
select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),
       'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB
  from #Files
 where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)
   and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES
   and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB
 order by 3 desc
-- REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST
select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB
  from #Files
 where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)
   and right(FileName,3) = 'bak'
   and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB
 order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

Script Section 3 – Cleanup/Discard the temp tables

drop table #Files
drop table #Drives

Examples of the output of each report:

REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME

· REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME

Notice:

o It is sorted by Drive, by FileSizeInMB desc.

o Not everything that is listed can be deleted - use your own discretion.

· REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE

Notice: It is virtually the same report as Report 2, except that the sort order is from most to least.

REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

Summary

I built this script to help me to quickly identify where old sql backup files and detached db files are lying around in order to save space by getting rid of them. It is very effective at achieving this goal. There is no excuse for not setting up maintenance jobs, but in a multi DBA environment someone else may have left files lying around. The code can be extended to search for other file extensions quite easily. Further you can even precede the code with code to determine if the pre-requisite is met, e.g. are DOS commands are allowed by SQL? The code below is a step in that direction:

DECLARE @sqlversion sql_variant
SELECT @sqlversion = SERVERPROPERTY('productversion')
IF LEFT(CONVERT(VARCHAR(255),@SQLVERSION),2) <> '8.'
  BEGIN
    -- START CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION  --
                CREATE TABLE #xp_cmdshell (OptionName varchar(255), minval int, maxval int, configval int, runval int)
                INSERT INTO #xp_cmdshell
                EXEC master..sp_configure
                declare @runval int
                select @runval = runval from #xp_cmdshell where OptionName = 'xp_cmdshell'
                drop table #xp_cmdshell
                if @runval is null
                 begin
                                RAISERROR ('enable "show advanced options" before you run this code', -- Message text.
                                                  10, -- Severity,
                                                  16 -- State,
                                                  )
                 end     
                if @runval = 1
                 begin
                                print '' -- The pre-requisites are enabled, so we can continue
                 end
                else -- IF xp_cmdshell is NOT enabled
                 begin
                                RAISERROR ('enable xp_cmdshell before you run this code', -- Message text.
                                                  10, -- Severity,
                                                  16 -- State,
                                                  )
                 end
    -- FINISH CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --
  END