December 4, 2003 at 11:19 am
Can someone help....trying to see if their is a way to use T-SQL to monitor all logins that are not used for an extended period of time?? Any ideas would be helpful.
Thanks!
December 4, 2003 at 12:37 pm
The only way I know of doing this is to turn on AuditLevel property to allow succesful logins to be reported in the ERRORLOG file. You then need to read all the ERRORLOG files to determine the last time the user logged on.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:qa.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 4, 2003 at 3:33 pm
audit
December 5, 2003 at 7:53 am
As I understand your question, you want to know how to get rid of "orphaned connections" on a regular basis. You can look at the sp_who sp and see how the spid and the last batch job are returned from the query, and use this to select the users that have a last batch job older than, say 15 minutes. You can return these users to a cursor or a temp table and then kill them if you think this is appropriate. You can then schedule this sp to run at certain times every day to clean up this problem.
I would spend a fair amount of time on trying to figure out how the orphaned sessions originate though, since if an application is causing them, then the way that the application connects to the database needs to be looked at.
John.
December 5, 2003 at 8:02 am
As I understand your question, you want to know how to get rid of "orphaned connections" on a regular basis. You can look at the sp_who sp and see how the spid and the last batch job are returned from the query, and use this to select the users that have a last batch job older than, say 15 minutes. You can return these users to a cursor or a temp table and then kill them if you think this is appropriate. You can then schedule this sp to run at certain times every day to clean up this problem.
I would spend a fair amount of time on trying to figure out how the orphaned sessions originate though, since if an application is causing them, then the way that the application connects to the database needs to be looked at.
John.
[/quote]
John -
Thanks looking at sp_who, I didn't even think of that this will get me going!
Thanks Again!
December 5, 2003 at 9:56 am
Unlike sp_who2, the sysprocesses table contains the login time along with the time the last batch was executed. For example,
SELECT sp.spid AS 'Spid',
sp.loginame AS 'Login Name',
sp.login_time AS 'Login Time',
sp.last_batch AS 'Last Batch'
FROM master..sysprocesses sp
ORDER BY sp.login_time DESC
If you use something like this make sure you exclude the system processes that are always logged in.
Jeff
"Keep Your Stick On the Ice" ..Red Green
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply