Managing Free Space

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

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:

Gather data into temp tables

Reports that pulls out what we are interested in. I created 4 reports for you. Look at the examples further on:

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

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:

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.