Show database procceses

SET NOCOUNT ON
DECLARE @DBName VARCHAR(50)
SELECT @DBName = ‘TransferInventory’
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID(‘tempdb..#tmpSpwho2’))
DROP TABLE #tmpSpwho2

CREATE TABLE #tmpSpwho2 (SPID INT, Status VARCHAR(100), Login VARCHAR(100), HostName VARCHAR(100), Blkby VARCHAR(100), DBName VARCHAR(100), Command VARCHAR(50), CPUTime INT,
DiskIO INT, LastBatch VARCHAR(100), ProgramName VARCHAR(100), SPID2 INT, REQUESTID INT)

INSERT INTO #tmpSpwho2 EXEC sp_who2
SELECT ‘kill ‘ + CAST(SPID AS VARCHAR(5)) FROM #tmpSpwho2 WHERE DBName = @DBName

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s