How to quickly disconnect all connections to the current database

How to quickly disconnect all connections to the current database

I used to like to execute this script, and then execute this script when I want to disconnect all connections, but when I reinstall sqlserver, I haveCodeLibrary

It is inconvenient to find the script and execute it. In fact, the database can be detached to disconnect all connections.

 1   Use  Master  2   Go  3   Set Ansi_nulls On  4   Set Quoted_identifier On  5   Go 6   7   Alter   Proc   [  DBO  ] . [  Sp_killallprocessindb  ]   @ Dbname   Varchar ( 100  )  8   As   9      If   Db_id ( @ Dbname ) =   Null   10           Begin  11               Print   '  Database dose not exist  '  12           End  13       Else   14          Begin  15               Declare   @ Killspid   Varchar ( 30  )  16               Declare Tmpcursor Cursor  17               For  18                   Select    '  Kill  '  +   Convert ( Varchar , Spid) As  Spid  19                   From  Master .. sysprocesses  20                   Where     Db_name (Dbid) =   @ Dbname  21                           And Spid <>  @ Spid        --  Terminate a process that is not the current process (not equal to the current process)  22                           And Dbid <>   0             --  The Database ID is not 0.  23               Open  Tmpcursor  24               Fetch   Next   From Tmpcursor  25   26   Into   @ Killspid        --  Select into @ killspid  27               While   @ Fetch_status   =   0   28                   Begin  29                       Exec (@ Killspid  )  30                       Fetch   Next   From  Tmpcursor  31   Into   @ Killspid  32   33                   End  34   35               Close  Tmpcursor  36              Deallocate  Tmpcursor  37           End  38          39          40   --  Exec sp_killallprocessindb 'database to be accessed' 

But tonight I found that database separation is faster, but the only inconvenience is that after database separation, you need to re-attach the database

Click OK. All connections are disconnected.

However, when there are many data files or transaction log files in the database, unlike me, there is only one data file and one transaction log file.

When attaching a log file, you must write an SQL statement to specify the location of the data file and log file.

 1 -- Syntax  2 Exec [sys]. [sp_attach_db] @ dbname = NULL ,-- Sysname  3 @ Filename1 = N '' , -- Nvarchar ( 260  )  4 @ Filename2 = N '' , -- Nvarchar ( 260  )  5 @ Filename3 = N '' , -- Nvarchar ( 260  )  6 @ Filename4 = N '' , -- Nvarchar ( 260  )  7 @ Filename5 = N '' , -- Nvarchar ( 260  )  8 @ Filename6 = N '' , -- Nvarchar ( 260  )  9 @ Filename7 = N '' , -- Nvarchar ( 260 )  10 @ Filename8 = N '' , -- Nvarchar ( 260  )  11 @ Filename9 = N '' , -- Nvarchar ( 260  )  12 @ Filename10 = N '' , -- Nvarchar ( 260  )  13 @ Filename11 = N'' , -- Nvarchar ( 260  )  14 @ Filename12 = N '' , -- Nvarchar ( 260  )  15 @ Filename13 = N '' , -- Nvarchar ( 260  )  16 @ Filename14 = N '' , -- Nvarchar ( 260 )  17 @ Filename15 = N '' , -- Nvarchar ( 260  )  18 @ Filename16 = N '' -- Nvarchar ( 260 )

 1   --  Location of the specified file  2    Exec   [  Sys  ] . [  Sp_attach_db  ]   @ Dbname   = Dlgpos, --  Sysname  3        @ Filename1   = N '  C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ dlgpos. MDF  ' , --  Nvarchar (260) 4        @ Filename2   = N '  C: \ Program Files \ Microsoft SQL Server \ mssql.1 \ MSSQL \ data \ dlgpos_1.ldf  '   --  Nvarchar (260)  5   6    --  --------------------------------------------  7    --  Or if there are more than 16 data files and log files, we recommend that you use the following statement: 8    Create   Database Dlgpos For  Attach  9    On   --  Data Files  10   (  11 Name =   '' , --  Logical name of the data file  12 Filename =   '' , --  Data File Storage path  13 Size = 1 MB, --  Initial size  14 Maxsize = 10 MB, --  Maximum Size  15 Filegrowth = 1 MB -- Growth Rate  16   )  17    Log   On   --  Log Files  18   (  19 Name =   '' , --  Log File logical name  20 Filename =  '' , --  Log File Storage path  21 Size = 1 MB, --  Initial size  22 Maxsize = 10 MB, --  Maximum Size  23 Filegrowth = 1 MB --  Growth Rate 24 )

 

-----------------------------------------Gorgeous split line-----------------------------------------
It seems that the problem cannot be solved by using gui. Finally, we should use the Daxia method of Xiaoxiang.

 1   Use  Master  2   Go  3   Alter   Database   [  Gposdb  ]   Set Single_userWith   Rollback  Immediate  4   Go  5   --  Check whether there are user connections  6   Select   *   From SYS. [  Sysprocesses  ]   Where   Db_name ([  Dbid  ] ) =  '  Gposdb  '  7   Go  8   Alter   Database   [  Gposdb  ]   Set  Multi_user  9  Go 

 

Although SQL statements are used, the method is simple. You can disconnect all the connections to the database in four sentences !!

Thanks again !!

If you have anything to add, orArticleIncorrect. You are welcome to make a picture !!