SQL Server – Shrink Data MDF File on Low Disk Space

shrinksql serversql-server-2005

Before proceeding, I know the cons of doing this and have read many questions on same. But since we are here on urgent need and adding disk would take over a couple of days and the DB in question being not that critical, so is there a way I can release the unused space from database.

Below screenshot says unused space as 36 GB, but when I execute sp_spaceused it says unused as some 400 MB but unallocated much more.

enter image description here

I waited for almost 4 hours, trying to shrink in chunks, but not even a single MB got shrinked.

Is there a way this can be done or how can I manage the unallocated space?

Please advise.

Best Answer

Your sp_spaceused provides 2 sets of the data:
- DB Size + Unallocated space - These numbers include BOTH: Data and log file; - Total statistics of RESERVED space for all objects within the database;

I bet your 36 GB of free space are in the Log file.

For real numbers use following query:

SELECT 
    f.file_id as "File Id",
    f.physical_name as "Physical Name",
    CAST(CAST(ROUND(f.Size/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS SizeMB,
    CAST(CAST(ROUND(FILEPROPERTY(f.name, 'SpaceUsed')/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS UsedSpaceMB,
    RIGHT('  ' + CAST(CAST((1 - FILEPROPERTY(f.name, 'SpaceUsed') * 1./ f.size) * 100 as DECIMAL(5,2)) as VARCHAR(6)),6) AS FreeSpacePrc
    ,  CASE f.file_id WHEN 2 THEN 'Log File' ELSE IsNull(g.name,'N/A') END as [File Group]
FROM sys.database_files as f with (nolock) 
LEFT JOIN sys.filegroups as g with (nolock) ON f.data_space_id = g.data_space_id

Might happen you still have some opened transactions, which hold you from shrinking individual files. See who hols them, close them. Reboot server if necessary. Shrink individual files while nobody accessing the database. switch it temporarily to Single User if necessary:

ALTER DATABASE <Your database> SET SINGLE_USER
Related Question