Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to determine free space to support shrinking SQL Server database files


By:   |   Read Comments (2)   |   Related Tips: More > Monitoring

Problem
As a DBA for several servers, I get a lot of calls about disk space. The first thing I do is look for data files I can shrink to free up some space on the disk. Some of the servers I support contain several databases and it can take a while to figure out which data file will give me the most immediate relief.

Solution
The first thing you need is the drive information. You can get this from the extended stored procedure xp_fixeddrives, which returns the drive letter and how much space is free on the drive. The next thing you need to know is how much space is available in each data file. When you pull up the information in SSMS, Microsoft uses two different DBCC calls; one for the data files and one for the log files. Instead of using separate DBCC calls, you can call the fileproperty function and pass the name of the data file and the SpaceUsed parameters. This has to be run from the context of the database in which the data files are located, so you can use the sp_msforeachdb stored procedure to return the results for all databases.

Now that you have the drive information and the space used, you can join it to the sys.databases and sys.master_files tables to return one view of all the data. It is now possible to quickly determine the files that have the most free space and where they are located.

USE MASTER

GO

CREATE TABLE #TMPFIXEDDRIVES (
  
DRIVE  CHAR(1),
  
MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (
  
DBNAME    VARCHAR(50),
  
FILENME   VARCHAR(50),
  
SPACEUSED FLOAT)

INSERT INTO #TMPSPACEUSED
EXEC'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme,
fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles'''
)

SELECT   C.DRIVE,
         
CASE 
           
WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
           
ELSE CAST(CAST((C.MBFREEAS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
         
END AS DISKSPACEFREE,
         
A.NAME AS DATABASENAME,
         
B.NAME AS FILENAME,
         
CASE B.TYPE 
           
WHEN THEN 'DATA'
           
ELSE TYPE_DESC
         
END 
AS FILETYPE,
         
CASE 
           
WHEN (B.SIZE 1024.0) > 1000
           THEN 
CAST(CAST(((B.SIZE 1024) / 1024.0AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
           
ELSE CAST(CAST((B.SIZE 1024.0AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
         
END AS FILESIZE,
         
CAST((B.SIZE 1024.0) - (D.SPACEUSED 128.0AS DECIMAL(15,2)) SPACEFREE,
         
B.PHYSICAL_NAME
FROM     SYS.DATABASES A
         
JOIN SYS.MASTER_FILES B
           
ON A.DATABASE_ID B.DATABASE_ID
         
JOIN #TMPFIXEDDRIVES C
           
ON LEFT(B.PHYSICAL_NAME,1C.DRIVE
         
JOIN #TMPSPACEUSED D
           
ON A.NAME D.DBNAME
              
AND B.NAME D.FILENME
ORDER BY DISKSPACEFREE,
         
SPACEFREE DESC
         
DROP TABLE 
#TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

After copying and pasting the above into a SQL Server 2005 query window and running all of the code the result set will look something like this:

Next Steps

  • With a little modification, you can schedule the script to shrink data files containing over X amount of free space on a drive with less than X amount of disk space.
  • In a perfect world, DBA's would just be able to ask for more drive space and not have to worry about shrinking files. It is more efficient to give the database the room it needs instead of having to shrink them all the time. Until then, I guess I will keep using this script.


Last Update:






About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources





More SQL Server Solutions











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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, July 18, 2009 - 9:03:36 PM - carlos.araque Back To Top

 Hello there!

If you have spaces in the database name i.e Adventure Works, the script doesn´t work.

I've modified the script to solve the problem.

-- **************************************************

USE MASTER

GO

CREATE TABLE #TMPFIXEDDRIVES (
  DRIVE  CHAR(1),
  MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (
  DBNAME    VARCHAR(50),
  FILENME   VARCHAR(50),
  SPACEUSED FLOAT)

INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use [?]; Select ''''[?]'''' [DBName], Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT   C.DRIVE,
         CASE
           WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
           ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
         END AS DISKSPACEFREE,
         A.NAME AS DATABASENAME,
         B.NAME AS FILENAME,
         CASE B.TYPE
           WHEN 0 THEN 'DATA'
           ELSE TYPE_DESC
         END AS FILETYPE,
         CASE
           WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
           ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
         END AS FILESIZE,
         CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
         B.PHYSICAL_NAME
FROM     SYS.DATABASES A
         JOIN SYS.MASTER_FILES B
           ON A.DATABASE_ID = B.DATABASE_ID
         JOIN #TMPFIXEDDRIVES C
           ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
         JOIN #TMPSPACEUSED D
           ON '[' + A.NAME + ']' = D.DBNAME
              AND B.NAME = D.FILENME
ORDER BY DISKSPACEFREE,
         SPACEFREE DESC
        
DROP TABLE #TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

-- **************************************************


Monday, June 02, 2008 - 11:15:03 AM - tosscrosby Back To Top

Glad you had this little disclaimer in there: "In a perfect world, DBA’s would just be able to ask for more drive space and not have to worry about shrinking files. It is more efficient to give the database the room it needs instead of having to shrink them all the time." I am not a big fan of shrinking files UNLESS you have performed a rare event, such as archiving stale data. Why risk fragmentation and performance? The price to be paid is greater than the rewards of doing so (UNLESS disk space has become critical!!). Proper planning/monitoring can prolong and/or eliminate disk contraint concerns. I snapshot all production databases every week and do comparisons monthy or quarterly, depending on the environment. Allows me to proactively assess my needs and address them before I HAVE to. - But that's just me. ;-)


Learn more about SQL Server tools