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.

  • It may help to visually look at this information in SQL Server Management Studio by right-clicking on the database, going to Reports, then Standard Reports, and finally Disk Usage. How are you trying to shrink the file? – Antoine Hernandez Aug 12 '16 at 23:06
  • Did you attempt ShrinkDB or ShrinkFile? – Sean says Remove Sara Chipps Aug 13 '16 at 4:06
  • Please post the exact shrink command that you executed. – Scott Hodgin Aug 13 '16 at 10:25
  • Does your database have a lot of LOB data? LOB data can significantly slow down a shrink operation. sqlskills.com/blogs/paul/… – AMtwo Aug 13 '16 at 16:51
  • @SeanGallardy, Shrink file – BeginnerDBA Aug 15 '16 at 13:34

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:

    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:

  • File Id Physical Name SizeMB UsedSpaceMB FreeSpacePrc File Group 1 D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf 63384.625 26452.438 58.27 PRIMARY 2 L:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ABC_1.ldf 577.000 31.016 94.62 Log File – BeginnerDBA Aug 15 '16 at 13:34
  • As you can see, you have more than 30Gb in your Log file and surprisingly 26Gb in the data file. Will add some info to the answer right now. – Slava Murygin Aug 15 '16 at 13:57
  • ,I believe its 30MB. I am not sure how you calculated that as 30 GB on log file. Out of 63 GB on data file there is only 26 used and rest free – BeginnerDBA Aug 15 '16 at 14:29
  • My fault. It looks like my bet was wrong. Missed the dot. You'd better provided results within a question. Also, provide commands you've used to shrink individual files. – Slava Murygin Aug 15 '16 at 14:44

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.