How to find Idle sessions in SQL environment.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find Idle sessions in SQL environment.

Author

Topic

shaileshpatangay
Starting Member

4 Posts

Posted - 2009-06-29 : 09:50:15

Following are list of columns, which T-sql would return.

Spid : System Process Id info, which is effected by blocking.
Loginame : Login Name for the spid, which is causing the blocking.
CPU : CPU in milliseconds.
Physical_IO : Total Number of Reads and writes.
Memusage : Memory Usage.
Login_time : When this session started.
last_batch : Last batch date time info.
IdleHrs : Number of Hours, this process has been Idle.
Hostname : Computer name from where this process has been initiated.
Programname : Program Name from where this spid is causing blocking.
Cmd : Sql Text, contains first 256 characters.

TSQL Statement
set nocount on

create table #IdleSessions(

spid int,loginame varchar(500),cpu int,physical_io bigint

,memusage int,login_time datetime,last_batch datetime,IdleSessionMin int

,hostname varchar(500),program_name varchar(1000),cmd varchar(3000)

,EventType varchar(500),Parameters varchar(500))

declare @spid int,@loginame varchar(500),@cpu int,@physical_io bigint

,@memusage int,@login_time datetime,@last_batch datetime,@IdleSessionMin int

,@hostname varchar(500),@program_name varchar(1000),@cmd varchar(3000)

DECLARE cur_IdleSession CURSOR

READ_ONLY

FOR select

spid,loginame,cpu,physical_io,memusage,login_time

, last_batch,datediff(mi,last_batch,getdate()) as IdleSessionMin

, hostname,program_name

from master.dbo.sysprocesses with (nolock)

where spid > 50

and datediff(mi,last_batch,getdate()) > ${IdleSessionMin}

and dbid = db_id('${DBSERVER.DBNAME}')

DECLARE @name varchar(40)

OPEN cur_IdleSession

FETCH NEXT FROM cur_IdleSession INTO

@spid,@loginame,@cpu,@physical_io,@memusage,@login_time

,@last_batch,@IdleSessionMin,@hostname,@program_name

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

Set @cmd = 'DBCC INPUTBUFFER ('+convert(varchar,@spid)+')'

Insert into #IdleSessions(EventType,Parameters,cmd)

EXEC (@cmd)

update #IdleSessions set spid = @spid

,loginame = @loginame

,cpu = @cpu

,physical_io = @physical_io

,memusage= @memusage

,login_time= @login_time

,last_batch=@last_batch

,IdleSessionMin=@IdleSessionMin

,hostname=@hostname

,program_name=@program_name

where spid is null

END

FETCH NEXT FROM cur_IdleSession INTO

@spid,@loginame,@cpu,@physical_io,@memusage,@login_time

,@last_batch,@IdleSessionMin,@hostname,@program_name

END

CLOSE cur_IdleSession

DEALLOCATE cur_IdleSession

select spid,loginame,cpu,physical_io,memusage

,login_time,last_batch,IdleSessionMin,hostname

,program_name,cmd

from #IdleSessions

where cmd not like ('%${ListOfCmd}%')

drop table #IdleSessions

Parameters

${IdleHrs} : This would be user entered parameter, which is used to gather process information. Default Value for this parameter = 24.

${DBSERVER.DBNAME} : This is a system entered parameter, which would input the database name.

${ListOfCmd} : This parameter would be user inputted entry, which is used for exclusion purposes.User would be inputting multiple values seperated by comma (“,”).

Default input for this parameter is empty string, which means no exclusions.

Note : The Default sevierity Level for this alert is 333.

Testing this Alert:

1. Open query analyzer on the testing server.

2. Execute some sql query and keep it query analyzer Idle for ${IdleHrs}.

3. Make sure that this alert (MS Idle Sessions) is scheduled.

4. Find that this alert should raise a ticket.

5. For testing exclusion list use parameter ${ListOfCmd} in the alert parameters.

cheers
shailesh patangay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 09:57:22

This?

select db_name(isnull(resource_database_id,1)), ec.session_id
from sys.dm_exec_connections ec
left join sys.dm_exec_requests er
on ec.connection_id = er.connection_id
left join sys.dm_tran_locks tl on tl.request_session_id = ec.session_id and resource_type='DATABASE'
where er.connection_id is null

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources