Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Unable to stop MSSQLSERVER!!!

Posted on 2006-04-12
4
Medium Priority
?
1,575 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:MarcGraff
4 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 16442368
Are you able to reboot?
0
 
LVL 14

Assisted Solution

by:adwiseman
adwiseman earned 400 total points
ID: 16444678
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 16447036
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
 
LVL 1

Author Comment

by:MarcGraff
ID: 16464078
Thanks so much for all of your help! ScottPletcher, your solution seems to have worked wonderfully!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question