1

I ran a shrink Database command on an SQL Server database to free up about 200GB of space, then mistakenly ran a large query select * from tbl where tbl is large, then tried to cancel that query through SSMS STOP button instead of kill.

SQL Server is now stuck, CPU at max and I can't even log in.

Is it safe to restart an SQL Server that is going through shrink db? Is there a chance I lose data? Or corrupt existing data? I don't think the select is an issue during restarts...

2

Here's my two cents.

If this is a non-production instance and assuming you're totally locked out (via DAC or other means), I'd give it a few hours and see if it works itself out, then restart the service if nothing has changed.

The SELECT query is a non-issue regarding the restart. And since shrink operations work page-by-page and are not subject to rollbacks, you're not in danger of having a super long rollback when the instance starts back up, either.

But that doesn't mean it is ideal to restart in the middle of a shrink. I don't know of any documented cases where this definitely causes issues, though. In theory it should still be able to do a clean shutdown, but SQL Server is a complex beast and this touches some of the relatively old Internals functionality that doesn't have nitty gritty documentation, so I won't say it will definitively be fine for you.

So weigh your options and costs of restarting vs. not and good luck :)

3
  • Good point to mention DAC, normally people tend to forget this approach. – Shanky Apr 17 '20 at 9:36
  • fwiw ended up restarting, doesn't look like anything was lost. to avoid this in the future, should i run shrink by itself? – Rafael Apr 17 '20 at 11:37
  • @R.M. Yes! If at all possible run shrink when nothing else is using the database. – LowlyDBA - John M Apr 17 '20 at 13:44
2

Restarting SQL Server is rarelly a good way to fix issue.

You can try to log as a dedicated admin (DAC connection) and then, you will see what is running and you may be able to kill some session.

Be aware that by killing a session, SQL will have to roll it back (which is single threaded) and there is no way to escape from this rollback. Sometime, the best solution may be to wait for it to complete. (You can kill the select as the rollback should be pretty quick)

1
  • DAC is life saver, people sometimes miss that. – Shanky Apr 17 '20 at 9:37

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.