SP To View Old Connections 1

[outofservice]

-- Gets active database connections older than # of
-- days specified.
-- Accepts integer input parameter for # of days.
-- Place in master database

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_connections'
AND type = 'P')

DROP PROCEDURE sp_connections
GO

CREATE PROCEDURE sp_connections @days int AS
SELECT
DATEDIFF(day, last_batch, getdate()) AS Days,
sp.spid,
sp.nt_username as NTname,
sp.loginame,
sp.hostname,
sp.program_name,
sd.name as DBName,
sp.login_time,
sp.last_batch,
sp.status,
sp.dbid,
sp.cmd,
sp.lastwaittype

FROM sysdatabases sd RIGHT OUTER JOIN
sysprocesses sp ON sd.dbid = sp.dbid
WHERE DATEDIFF(day, last_batch, getdate())>= @days
and sd.name is not null

-- Lauryn Bradley 11/01/02
-- Gets active database connections older than number of
-- days
-- specified. Accepts integer input parameter for number
-- of days.
-- Place in master database

-- exec sp_connections '4'
GO

 

[Witchy]

Keep 'em coming! This is brilliant and will save me a lot of time B-)