4

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.

  • 1
    If the concern is that storage is 97% full, more storage might be a better answer. – Andy Aug 24 '17 at 1:17
  • First, check the disk directly and makes sure that the SQL data and log files are actually what is taking up space. (It is very likely they are, but still important to check: it is very common to find GB of old trace files, or perfmon logs, or old SQL backups, etc.) – BradC Aug 24 '17 at 14:03
10

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);
  • 2
    Awesome level of detail! The OP may want to back up the DB first, just in case that table is important. – CaM Aug 14 '17 at 12:27
  • Very good point, I totally forgot to mention that! I assumed it's too obvious, but that's my bad... – Oreo Aug 14 '17 at 13:22
  • As soon as you shrink the data file, the effort to rebuild the index will have been a waste of time. If you have to shrink database files (and, yes SOMETIMES you have to), always perform the index maintenance following the shrink. If the file blows out to the former size after index maintenance, then something is awry, and you'll need to dig deeper. – SQL_Hacker Aug 14 '17 at 13:31
  • I totally agree, if you are doing SHRINKFILE without TRUNCATEONLY. But when you use TRUNCATEONLY, the index rebuild work doesn't get lost, since SQL Server just unlocks the empty pages at the end of the DB file, so they can be used by the OS. – Oreo Aug 14 '17 at 14:03
  • Is SHRINKFILE not a big no? brentozar.com/archive/2009/08/… – K09 Aug 14 '17 at 14:07
1

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.

0

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.

  • Other than all these reasons... brentozar.com/archive/2009/08/… – K09 Aug 14 '17 at 13:58
  • tbh, I'd assumed that all other avenues for finding wasted space had been explored and that there was a real need to free up disk space. But I agree, it's not usually a good idea unless it's totally unavoidable. – David Fowler Aug 14 '17 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.