Unable to stop MSSQLSERVER!!!

I am unable to stop MSSQLSERVER!!! Please Help!

I was attempting to drop a constraint from a table and got a lock request time out exceeded. I made sure no one was logged into the DB (exept me) and then killed (using kill spid) all of the open connections. I attempted to drop the constraint again with the same result. I thought since no one is connected to the DB I will stop and start SQL using the service manager but MSSQLSERVER changes to "Stopping..." but has yet to stop (1 hr 20 min later).

I am lost on what action to take next. Any help is much appreciated!

Thanks!
   - Marc
LVL 1
MarcGraffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
Are you able to reboot?
0
adwisemanCommented:
take a look at the SQL server logs in enterprise manager.  see what's happening.  It could be rolling back your last transaction, if the database was large enough it could take that long.  The logs will give you a percent complete if this is the case.
0
Scott PletcherSenior DBACommented:
Agree with adwiseman, it sounds like SQL was in the middle of a big update/insert/delete on that db.


>>  I made sure no one was logged into the DB (exept me) and then killed (using kill spid) all of the open connections. <<

A better method is to use ALTER DATABASE:

ALTER DATABASE databaseName
SET SINGLE_USER WITH {
          ROLLBACK AFTER integer [ SECONDS ]
        | ROLLBACK IMMEDIATE
        | NO_WAIT }

"ROLLBACK AFTER ..." waits that many seconds for processes to finish, then kills them all.
"ROLLBACK IMMEDIATE" doesn't wait.
"NO_WAIT" does the ALTER only if SQL can do it without causing a rollback or wait; otherwise the command fails without waiting.

That probably would not have helped in the current situation but can if connections are being made constantly when you try to KILL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarcGraffAuthor Commented:
Thanks so much for all of your help! ScottPletcher, your solution seems to have worked wonderfully!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.