What Last_Batch means? - SQL Server Forums

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

SQL Server Administration (2005)

What Last_Batch means?

Author

Topic

CanadaDBA

583 Posts

Posted - 2008-09-11 : 11:03:23

Does only running a SELECT statemnet updates Last_Batch in master.dbo.sysprocesses?

Canada DBA

CanadaDBA

583 Posts

Posted - 2008-09-11 : 11:28:11

Well, I found this: http://technet.microsoft.com/en-ca/cc825503.aspx

I want to detect if a connection has been idle for more than 60 minutes. I have licnese limitation for an application and want to drop idle connections. What should I do to detect this status?

Canada DBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-11 : 11:41:34

Investigate the new DMV views available for SQL Server 2005.

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

baghul
Starting Member

2 Posts

Posted - 2009-03-13 : 12:40:14

quote:
Originally posted by CanadaDBA

Well, I found this: http://technet.microsoft.com/en-ca/cc825503.aspx

I want to detect if a connection has been idle for more than 60 minutes. I have licnese limitation for an application and want to drop idle connections. What should I do to detect this status?

Canada DBA

This is what I use to kill orphan Mssearch process id's, seems to work nicely

--declaration

DECLARE @MSSEARCH_SPID VARCHAR(5)
DECLARE @STMT VARCHAR(500)

--use a cursor to loop through the sysprocesses table

DECLARE MSS_CURSOR CURSOR FOR
SELECT SPID
FROM MASTER.DBO.SYSPROCESSES
WHERE LASTWAITTYPE = 'MSSEARCH'
AND DATEDIFF(MINUTE,LOGIN_TIME,LAST_BATCH) > 60

--open a cursor

OPEN MSS_CURSOR
FETCH NEXT FROM MSS_CURSOR INTO @MSSEARCH_SPID

--use a while loop to fetch records from the cursor

WHILE @@FETCH_STATUS=0
BEGIN

--use the SQL KILL command

SET @STMT='KILL ' + @MSSEARCH_SPID
PRINT @STMT
EXEC(@STMT)

FETCH NEXT FROM MSS_CURSOR INTO @MSSEARCH_SPID
END

--close the cursor

CLOSE MSS_CURSOR
DEALLOCATE MSS_CURSOR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources