How to free up disk space to the OS, ideally without using SHRINKFILE?

Question

Our server admin is asking me to free up disk space on our SQL server, since the drive where the SQL datafiles reside is at 97% usage.

I have found a table that has grown out of control. It now contains over 300 million rows, so I will set a job to delete these overnight.

• What are my options to then free up the disk space?
• Will the deletion of those rows free up space for other tables to use?

I would like to avoid SHRINKFILE as it is not recommended.

Answer 1

First, I would confirm what the server admin is seeing, by running some free-space diagnostics on your SQL Server:

/* Get SQL Server Drive Usage Stats */
IF OBJECT_ID('master.sys.dm_os_volume_stats') IS NOT NULL
    SELECT vs.volume_mount_point AS Drive, vs.file_system_type AS [Type]
        ,vs.logical_volume_name AS LogicalName
        ,MAX(CAST(  1.0*vs.total_bytes  / 1073741824 AS DECIMAL(18,2)))AS[Drive Size (GB)]
        ,CAST(SUM(  1.0*size) / 128 / 1000 AS DECIMAL(18,2)) AS [SQL Size (GB)]
        ,MAX(CAST(  1.0*vs.available_bytes/1073741824 AS DECIMAL(18,2)))AS[Free Space (GB)]
        ,MIN(CAST(100.0*vs.available_bytes/vs.total_bytes AS DECIMAL(5,1)))AS[Free Space (%)]
    FROM master.sys.master_files AS f WITH (NOLOCK)
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
    GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name
    ORDER BY 1
    OPTION (RECOMPILE);

SELECT Drive
    ,ISNULL([ROWS], 0) + ISNULL([LOG], 0)
        + ISNULL([TempROWS], 0) + ISNULL([TempLOG], 0)
        AS 'TotalUsed (MB)'
    ,[ROWS] AS 'Data (MB)'
    ,[LOG] AS 'Logs (MB)'
    ,[TempROWS] AS 'TempData (MB)'
    ,[TempLOG] AS 'TempLogs (MB)'
FROM (
    SELECT LEFT(Physical_Name, 3) 'Drive'
        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc 'FileType'
        ,SUM(size) / 128 'SizeMB'
    FROM master.sys.master_files
    GROUP BY LEFT(Physical_Name, 3)
        ,CASE WHEN database_id = 2 THEN 'Temp' ELSE '' END + type_desc
    ) Results
PIVOT(SUM(SizeMB) FOR FileType IN ([ROWS], [LOG], [TempROWS], [TempLOG])) pvt
ORDER BY 1
OPTION (RECOMPILE);

Then check which files are the biggest/worst on the problem drive:

/* Get Individual Database Stats */
SELECT mf.database_id 'DB_ID'
    ,DB_NAME(mf.database_id) 'DBName'
    ,d.state_desc 'DBState'
    ,d.recovery_model_desc AS RecoveryModel
    ,CASE WHEN Log_ReUse_Wait_Desc = 'NOTHING' THEN ''
          ELSE Log_ReUse_Wait_Desc END AS LogReUseWait
    ,mf.[File_ID]
    ,mf.NAME 'LogicalName'
    ,mf.type_desc 'Type'
    ,mf.Physical_Name
    ,mf.state_desc 'FileState'
    ,CAST(size / 128.0 + 0.5 AS INT) AS SizeMB
    ,CAST(max_size / 128.0 + 0.5 AS INT) AS MaxSizeMB
    ,CASE is_percent_growth
        WHEN 0 THEN CAST(growth / 128 AS VARCHAR(10)) + ' MB'
        ELSE CAST(growth AS VARCHAR(10)) + ' %' END AS 'AutoGrowth'
    ,CASE 
        WHEN d.STATE <> 6 /* 6 = OFFLINE */
        AND mf.type_desc = 'ROWS'
        AND mf.database_id <> 2 /* not TempDB */
            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))
               + ';   DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))
               + ',1,TRUNCATEONLY);
GO'
        ELSE '' END AS 'ShrinkTruncateOnlyCommand'
FROM [master].sys.master_files mf
LEFT JOIN [master].sys.databases d ON d.database_id = mf.database_id
WHERE 1 = 1
    AND d.STATE <> 6 /* 6 = OFFLINE */
    AND mf.database_id > 4 --user DBs only
    --AND mf.database_id <= 4 --system DBs only
ORDER BY SizeMB DESC, DB_NAME(mf.database_id), [file_id]
OPTION (RECOMPILE);

Before you go the SHRINKFILE route, you should ask yourself some higher level questions, like:

• Why does the server admin need more free space on that drive?
• What will happen to my DB and its data if the file takes up the whole drive and cannot grow when needed? (hint: it's Bad Stuff.)
• Should the server admin add space/drives to my SQL Server box, so I don't have to SHRINKFILE right now?

Since you have found a table with data that can (hopefully) be deleted (check with the people who use that data!), go ahead and delete the data, possibly in batches if you are worried about locking the table for a long time:

DELETE FROM <table> WHERE <pick the primary keys to delete in this batch>

If there are useful rows left in your table after getting rid of the old/useless ones, I would rebuild the table's Primary Key:

USE YourDB;

SELECT s.[Name] + '.' + t.[Name] AS ObjectName
    ,'ALTER INDEX [' + i.[Name] + '] ON [' + s.[Name] + '].[' + t.[Name] + '] REBUILD'
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
    AND i.type = 1 --clustered index
    AND OBJECTPROPERTY(t.[object_id], 'IsUserTable') = 1
JOIN sys.schemas s ON s.schema_id = t.schema_id
    --AND s.[Name] = 'YourSchema'
    AND t.[Name] = 'YourTable'
ORDER BY 1

...and finally shrink the database file. I would try using the TRUNCATEONLY option first, as it's faster/safer than a normal SHRINKFILE which rearranges pages within the DB file:

/* Get Individual Database Stats */
SELECT d.Name DB
    ,mf.type_desc
    ,CASE 
        WHEN d.STATE <> 6 /* 6 = OFFLINE */
        AND mf.type_desc = 'ROWS'
        AND mf.database_id <> 2 /* not TempDB */
            THEN 'USE '+QUOTENAME(DB_NAME(mf.database_id))
               + '; DBCC SHRINKFILE(' + CAST(file_id AS VARCHAR(2))
               + ',1,TRUNCATEONLY);'
        ELSE ''
        END AS 'ShrinkTruncateOnlyCommand'
FROM sys.master_files mf
LEFT JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.Name = 'YourDB'
    AND mf.type_desc = 'ROWS' --data only
ORDER BY DB_NAME(mf.database_id), [file_id]
OPTION (RECOMPILE);

Answer 2

Unused space resulting from deleting rows can often be reused by other tables within the database as long as entire pages/extents are emptied (likely with a mass delete). Consider reorganizing the table after the delete to maximize free space available to other objects.

Delete operations on heap tables may not release space (making it available to other objects) if the delete is performed without a table lock, or a row versioning isolation level is enabled. You may need to rebuild the heap to release space (if rows remain) or use TRUNCATE TABLE instead (to remove all rows).

There is no need to shrink files afterward unless you need space on the volume(s) for other purposes. In that case, you could execute DBCC SHRINKFILE with the TRUNCATE_ONLY option so that unused space is released but data pages are not moved (introducing fragmentation). That should be done after reorganizing tables to maximize benefit.

Answer 3

Shrink file's your only option here, deleting the rows will just leave a ton of empty space in the files.

In this case I don't see anything wrong with using shrink file, that what it's for. It only becomes a problem when people misuse it to keep their files shrunk right down to the bare minimum which in turn will force the files to constantly have to grow out.