SQL Server Database, kill active processes and take it offline

Use the following TSQL to kill the active processes on a database and take it offline.

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'databasename'

DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

EXEC(@SQL)


ALTER DATABASE [databasename]
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
Author Paul Hayman

Paul is the COO of kwiboo ltd and has more than 20 years IT consultancy experience. He has consulted for a number of blue chip companies and has been exposed to the folowing sectors: Utilities, Telecommunications, Insurance, Media, Investment Banking, Leisure, Legal, CRM, Pharmaceuticals, Interactive Gaming, Mobile Communications, Online Services.

Paul is the COO and co-founder of kwiboo (http://www.kwiboo.com/) and is also the creator of GeekZilla.

Add Comment

Name
Comment
 

Your comment has been received and will be shown once it passes moderation.