0

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 ?

  • Have you gone through this documentation? Shrink a File – JSapkota Jan 18 '17 at 6:51
  • 1
    Please add the command you are using to your question. – James Anderson Jan 18 '17 at 8:35
  • Did you check out in StackExchange ,It's duplicate thread dba.stackexchange.com/questions/9914/… – Md Haidar Ali Khan Jan 18 '17 at 10:30
  • 1
    @MdHaidarAliKhan I don't think from what the OP has asked that this is a duplicate of that question. Hard to say for certain, but the OP's question implies he knows how to run the command, but is asking why it's not doing anything - the question you linked is asking what the command is, and about data loss. They seem quite different to me, from the information currently available (although the sparsity of information on this question is not desirable in itself). – Ian_H Jan 18 '17 at 10:35
  • 1
    @MdHaidarAliKhan No, it may be a bad question for a number of reasons, but that doesn't make it inherently a duplicate. There are fundamental differences between the question you linked and this one. I'm not really sure how you can argue that point. I do not deny that it is not researched, but that in itself doesn't make it a duplicate. "Duplicate" has a very specific meaning. – Ian_H Jan 18 '17 at 11:25
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.

  • From What process OP is try to shrink the (.MDF) file, You did not ask to him.How they are trying to do that at least before giving the answer. Let try to know the actual concern of OP. – Md Haidar Ali Khan Jan 18 '17 at 10:54
  • 1
    @MdHaidarAliKhan I feel like you haven't really engaged with my answer, which is unfair, perhaps because I disagreed with you on the duplicate thing. What reasons, other than no unallocated space, do you know for shrinkfile to complete, with no errors but not actually reduce space on disk (as the OP quite clearly did say happened)? Just look at the facts objectively. Because, with that in mind, mine is a reasonable answer. If the OP says he tried to shrink, it's reasonable to assume that's what he actually did either with dbcc shrinkfile or the gui (which is basically the same). – Ian_H Jan 18 '17 at 11:31

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.