2

There were 2 running, actually, the first one I killed went away quickly. The second is in suspended status, from sys.dm_exec_requests the percent_complete doesn't change (88.87) and the estimated_completion_time keeps going up. What can I do? There is no room left on the one mdf file drive, is that the problem? This is why my coworker started the shrink (he's on vacation now, long story why they started doing the shrinks, databases is being moved to Hadoop, and I'm new (3rd week)). Please advise.. thanks!

  • See here for an older related (though not exactly the same) answer on ServerFault. – Erik Darling Dec 4 '17 at 20:07
  • I created space on the drive by shrinking a log file. The process doesn't show any progress, status is still suspended, only the total_elapsed_time and the estimated_completion_time keep going up! (about 171 hours right now). I started compressing tables and indexes so we never have to shrink again... – MariusD Dec 5 '17 at 17:08
  • Those will continue to increase. I believe it's a basic calculation; If it's been running for 30 hours, and it's 75% done, then it will take another 10 hours to complete (time_to_date * 100/percent_complete = total_time_to_complete; subtract time_to_date for time_remaining_to_complete). In other words, that % complete isn't very good for giving you time to complete. If "% complete" changes over time, then it's making some sort of progress; if not, it's stuck for now at least. ... – RDFozz Dec 5 '17 at 21:23
  • ... To confirm - You tried killing the DbccFileCompact SPID, but it's still running. You can try killing it again - if it's already trying to stop, I don't think this does harm. Also - it shouldn't be tied up in a long rollback, because (good news here) once it successfully moves a page, it apparently commits that move. So, some space may be able to be released, and you won't be starting over from scratch if you run it again. – RDFozz Dec 5 '17 at 21:28
  • I'm a little bit afraid to try that... (killing it again). Have you done it before? I don't think we have a good backup other than the VM snapshot. This is strange, I don't think it's a rollback, it would have shown in sys.dm_exec_requests where command IN ('killed/rollback','rollback')? – MariusD Dec 5 '17 at 22:21
1

I just killed it today and it went away immediately... hope this helps someone else. Thanks!

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.