2

I was left with a scenario where my Database Initial size is 370GB.

When I checked the EXEC sp_spaceused, I can see almost 360GB of unallocated space .

Does unallocated space means unused space? If it is unused space, why this is causing the issue for storage?

What options do I have apart from shrinking the .mdf file with 360GB of unallocated space?

Also, as this is a huge database, what level of increments I can mention in the place of 'x' below - can I start from 10,000 MB every time I shrink?

USE Demo
GO
DBCC SHRINKFILE (N ‘Demo’, x)
GO

Is there anything else I should consider in this type of situations?

Would adding a new data file help?

  • There's been so much written about shrinking databases already. What makes your situation different? Sell me on this. – WorldStar SQL May 11 '18 at 11:24
  • I accept your point. My only concern is 360GB unallocated space. What does it mean and what would have created that internal unallocated space which is not reflecting with drive . Just want to know . I read bit about Shrinking .mdf , though it is recommended not to due to performance hit by fragmentation, but how about situations like, does shrinking works in this situation ? – DBOn May 11 '18 at 12:35
  • 360 GB internal unused space , just want to regain it back either by Shrinking file or is there any other way ? – DBOn May 11 '18 at 12:36
5

Do you know everything that goes on with this app / database?

Imagine this scenario:

  • There's a Very Important Person at your company that needs to get (daily | monthly | quarterly | annual) financial reports
  • These reports are generated by a scheduled job
  • This job generates hundreds of GBs of data in the database - creating work tables, crunching numbers, etc
  • Afterwards, the job "cleans up" after itself by dropping (or truncating + rebuilding) the work tables
    • This results in a lot of unused space in the database - but the size of the files doesn't change
  • One day, a DBA comes along and shrinks this database
  • Some day in the future, this reporting job tries to run - but it grinds to a halt as the file has to grow over and over and over
  • Maybe the job even fails, or is delayed so long that it starts to affect performance during normal business hours
  • Very Important Person is mad, because they don't have their important reports, and everyone is complaining to them about the system being slow

You don't want that to happen, do you?


All that being said, if you're sure that the database:

  • really doesn't need this space, and
  • will never use this space because it doesn't gain data that quickly, and
  • you have an actual need for the space on this drive (paying by the GB to a cloud vendor, expensive SAN that other apps need space on, etc),

then by all means, shrink it.

Maybe there was a typo in the filesize when this database was first created (someone tacked a 0 onto the end of "37" - poof, 370 GB database file).

Maybe there was a one-time data load / deletion that will never happen again that caused the DB to grow to this size.


Make sure to give some thought to the "target size" you use with SHRINKFILE if you do this. You say it's only 10 GB of used space, but I wouldn't immediately set it to 10 GB - you are putting some data into this database, right?

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.