I am new to the DBA Part.

I had an 110 GB Database from which I deleted records from a 95GB sized table. Since the SQL Server Stores memory for the future, it still had 110 GB on the disc.

I ran the Shrink Database through GUI, but it was taking a lot of time. So, I cancelled it, but it remained in the greyed state. When shown from sys.sysprocesses, it shows the last batch time some 3 hours ago and is in runnable state. Can I kill the SPID?

To make it worse, Database is in multi-user mode.

Update: After three long hours, the process got rolled back on its own.

  • 1
    you have a good backup, right? RIGHT?!?! :) – Jeremy Mar 17 '16 at 12:17
  • It's normal for SHRINK DATABASE to take a lot of time. Also: you can cancel it anytime you want. So I think you don't have to be afraid, my friend. – Alex Yu Mar 17 '16 at 12:41
  • I was told that there could be missing pages if I do so – Anshul Dubey Mar 17 '16 at 13:57
  • 2
    There will be no missing pages by cancelling a shrink command, nor would there be if you killed the spid associated with the task. – Sean Gallardy - Retired User Mar 18 '16 at 3:23
  • If you have to shrink it, try shrinking in small batches and see how you go. – Lawrage Mar 18 '16 at 4:01

Do not use the SHRINK option.

It results in serious performance degradation. Also after you shrink the data file, it will later grow even more.

Please watch this video by Klaus Aschenbrenner (Microsoft Certified Master in SQL Server):

SQL Server Quickie - SHRINK

I would recommend to kill the SPID but be ready for the rollback operation. It can take another 3 hours, maybe less.

Not the answer you're looking for? Browse other questions tagged or ask your own question.