Post reply

Kill procceses

  • January 6, 2012 at 6:30 am

    #146791

    I am trying to detach a database. I have killed all processes, but it still gives me an error saying I can't detach because the DB is in use?

    Thanks for any help.

  • January 6, 2012 at 7:36 am

    #1429622

    Do you have a query window open in SSMS pointing to that database? If so, just change the current db.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • January 6, 2012 at 7:52 am

    #1429639

    I have nothing open pointing to the DB. I have even tried using code:

    Use Master

    ALTER DATABASE voicelink SET MULTI_USER WITH NO_WAIT

    still get error that the db is in use.

  • January 6, 2012 at 12:16 pm

    #1429808

    How about this? I scripted it from SSMS after checking the Drop Connections checkbox on the Detach Database screen.

    USE [master]

    GO

    ALTER DATABASE [XXX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    USE [master]

    GO

    EXEC master.dbo.sp_detach_db @dbname = N'XXX'

    GO

  • January 8, 2012 at 6:51 pm

    #1430256

    djustice 20821 (1/6/2012)

    I am trying to detach a database. I have killed all processes, but it still gives me an error saying I can't detach because the DB is in use?

    Thanks for any help.

    Do you have a web service that automatically reconnects? Do you have "Asynchonus Statistcs Updates" turn on? Either will create a new connection faster than you can kill them.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 8, 2012 at 11:52 pm

    #1430288

    Do you have any logins defined which use the database as default database? Check under Security --> Logins --> Login Property --> General (SQL 2005).

    Happend to me once 🙂

  • January 9, 2012 at 3:10 am

    #1430344

    If it is a standalone db or testing db, you can stop the services and start again. But should not do if it is in production or mulple users connected. bcoz it will disconnect all the running services related to your database.

  • January 9, 2012 at 3:29 am

    #1430352

    Have you double checked that there is no processes is curently using your database.

    Select * from sys.sysprocesses where dbid=db_id('dbname') ---- run this and check.

  • January 9, 2012 at 9:27 am

    #1430595

    Check for orphaned processes. If the result is a combination of letters and numbers. Kill it.

    If the result is zeros, you need to restart the DTC (and you may also need to restart SQL Server)

    SELECT DISTINCT(req_transactionUOW)

    FROM sys.syslockinfo

    WHERE req_spid = -2

  • January 10, 2012 at 4:56 pm

    #1431502

    Exit SSMS completely ( all open SSMS sessions if any) and then start a new session in SSMS. If not detached already, then run your detach again. That should work.

  • January 10, 2012 at 11:35 pm

    #1431581

    Try this script before restore. Works for years.

    ___________________________________

    -- Kill all processes for the DB

    DECLARE @DBName VARCHAR(100)

    SET @DBName = 'MyDatabase001' -- db_name()

    DECLARE @RecordID INT

    DECLARE Cursor_Work CURSOR LOCAL

    FOR SELECT spid FROM master..sysprocesses WHERE dbid = DB_ID(@DBName)

    OPEN Cursor_Work

    WHILE 1 = 1

    BEGIN

    FETCH NEXT FROM Cursor_Work INTO @RecordID; IF (@@FETCH_STATUS <> 0) BREAK

    EXEC('KILL ' + @RecordID )

    END

    CLOSE Cursor_Work

    DEALLOCATE Cursor_Work

    GO

    ___________________________________

  • January 12, 2012 at 10:29 am

    #1432758

    Take the database offline before detaching.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply