1

We have a SQL 2012 Database that is mirrored. It also has one publication with 3 subscribers. After a problem occurred (a view that couldn't replicate) during a snapshot push to one of the subscribers, I attempted to change the properties so that view was not in the list of articles to push. I clicked OK and the window hung. Upon inspection in the Activity Monitor, I have 9 different SIDs with a Wait time in excess of an hour. Most of them are "SELECTS" in the SSMS Application. I presume I can kill these with no issues. 2 are in SSMS but are "UPDATE" commands. I also have 3 SQLAgent SIDs that are hung: one is an "OPEN CURSOR" one is a "DELETE" and one is a "SELECT". There is also a "Distribution History" application SID SELECT that is hung. These SIDs are all in a "SUSPENDED" state. They all are waiting on a resource LOCK (mostly shared)

This is a critical production database and I am nervous about just killing SSMS while it's in the midst of trying to do something. Can someone recommend one of: A - kill the SSMS interface B - kill various SIDs. If so, should I only kill certain types? Which ones? C - something else.

1 Answer 1

0

No one will be able to decide for you which queries you should KILL other than yourself, because it'll depend specifically on what those queries are doing and how critical they are to your company's operations. This is regardless of what type of operation is occuring (some people might have SELECT queries with a purpose much more important than any of their UPDATE and DELETE queries).

Also keep in mind, if you kill a query that was in the middle of a large transaction, you may also be stuck waiting a while for that transaction to rollback, potentially.

Your best course of action is to download sp_WhoIsActive and run that to see all your active query processes. It'll also tell you which ones are blocked and the ID of the process that is blocking them. You may be able to find the lead blocking query that way and then you can determine if you should kill just that one query that is causing all the blocking. sp_WhoIsActive also tells you the User who ran each query (when applicable) so you can also potentially see who ran the leading blocking query and work with them to ensure it's safe to kill.

It may very well be replication is stepping on itself (I've been in that situation before) and you need to kill a replication process. Then your SSMS Publication Properties window should get unlocked and finish saving your change to your Articles. After that, you'll likely need to reinitialize your Subscribers to ensure the data is consistent. But start with my advice above first to determine what the is lead blocking query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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