Post reply

sp_detach

  • July 5, 2010 at 8:07 am

    #226421

    I need to do sp_detach but I can`t because says me that the database is currently in use.

    I closed all the applications that use the database.

    How can I know what process is still using the database?

    Thanks

    Viky.

  • July 5, 2010 at 8:19 am

    #1188784

    Are you connected to the database that you're trying to detach? If so change your context to master.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2010 at 8:27 am

    #1188793

    Before I try to detach I connected to the master.

  • July 5, 2010 at 8:33 am

    #1188795

    sp_who

    sp_who2

    kill

    alter database set single_user

    use master

    exec dbo.sp_detach

  • July 6, 2010 at 6:44 am

    #1189071

    I have found this quite effective for kicking out users.

    ALTER DATABASE [DATABASE NAME]

    SET OFFLINE WITH ROLLBACK IMMEDIATE

    Go

    ALTER DATABASE [DATABASE NAME]

    SET ONLINE WITH ROLLBACK IMMEDIATE

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • July 6, 2010 at 9:23 am

    #1189149

    You can also use this,

    select DB_ID ('Your DatabaseName')

    select spid from sys.sysprocesses where dbid = YourDatabaseId

    kill

  • July 6, 2010 at 1:39 pm

    #1189301

    alter database set restricted_user

    I always wished for:

    alter database set single_restricted_user

  • July 6, 2010 at 4:28 pm

    #1189370

    USE yourDb

    ALTER yourDb

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    USE master

    EXEC sp_detach 'yourDb'

    Scott Pletcher, SQL Server MVP 2008-2010

Viewing 8 posts - 1 through 7 (of 7 total)

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