Hi,
it is often necessary to disconnect all open connections from a database (e.g.: if you want to delete it or take it offline). Management Studio has a checkbox to kill all connections in most of the cases where it makes sense. This is not so helpful if we want to script our stuff, so the following code suggests a way to kill all connections. (I found 90% of this piece of code on the web as a code snippet, but I forgot where... sorry for not mentioning the original source)
the only open task to execute this script is to enter the name of the db you want to disconnect the user from and enter it instead of the value db in the @dbname variable.
Kind regards,
Daniel
it is often necessary to disconnect all open connections from a database (e.g.: if you want to delete it or take it offline). Management Studio has a checkbox to kill all connections in most of the cases where it makes sense. This is not so helpful if we want to script our stuff, so the following code suggests a way to kill all connections. (I found 90% of this piece of code on the web as a code snippet, but I forgot where... sorry for not mentioning the original source)
1 2 3 4 5 6 7 8 9 10 11 12 13 | Declare @dbname sysname Set @dbname = 'db' Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) While @spid Is Not Null Begin Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) and spid > @spid End |
the only open task to execute this script is to enter the name of the db you want to disconnect the user from and enter it instead of the value db in the @dbname variable.
Kind regards,
Daniel
No comments:
Post a Comment