I am kind of new to SQL Server.
I am trying to shrink my MDF files, however I am unable to do it.
There is no error message. Any idea why this is the case ?
Asked
Active
Viewed 118 times
1 Answers
0
Without more more information it might be hard to diagnose this - however, usually the only reason why a shrink wouldn't work (but completes, no errors and doesn't hang) is because there is no free space to release anywhere.
The only way to reduce the size on disk therefore is to delete data.
Run sp_spaceused to find out how much your database is actually taking up. If it's occupying all (or most) of the space on disk, then your shrink operation won't do anything - there's nothing to shrink.
NB (almost tempting to put this at the top, but I thought I'd answer the question first):
If this is on a dev environment, then you're probably OK to play around and try this out.
However, if you're doing this in production - don't.
It's a long quoted (for good reason) fact that shrinking a db is pretty much the worst thing you can do. Performance suffers for the sake of space that you could very well just use up again at some point - and disks are cheap.
Don't take my word for it Brent Ozar, Paul Randal, Stack Exchange Q, Other SE Q (there's plenty more examples saying the same thing).
Ps. Check out SQLServerCentral and Stack Exchange Q for more options of checking space used.
