Unable to stop MSSQLSERVER!!!

Posted on 2006-04-12
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!

   - Marc
Question by:MarcGraff
    LVL 29

    Expert Comment

    Are you able to reboot?
    LVL 14

    Assisted Solution

    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.
    LVL 68

    Accepted Solution

    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
              ROLLBACK AFTER integer [ SECONDS ]
            | 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.
    LVL 1

    Author Comment

    Thanks so much for all of your help! ScottPletcher, your solution seems to have worked wonderfully!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now