Tuesday, March 9, 2010

You can force the DB offline and drop connections

"sp_who" is using to find number of connection to data base

You can force the DB offline and drop connections with :

EXEC sp_dboption N'yourDatabase', N'offline', N'true'

Or

ALTER DATABASE [yourDatabase] SET OFFLINE WITH
ROLLBACK AFTER 60 SECONDS

OR

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

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

in reference to: ahamed travel agencies cochin - Google Search (view on Google Sidewiki)

No comments:

Post a Comment