December 11, 2007 at 1:34 am
Hi folks !
Let me explain what I would like to do:
Can you imagine sql server 2005 with one production database and with about 100 windows authenticated users, then one application (fat client) runnig on local PCs that is connecting via ODBC sql connection.
This app is economical software and the users (workers) are accountants and traders working on invoices, .. etc.
The problem that I have is that some of the users are logged into client but not working at all with application, sometimes about 1/2 or 1 hour. As I can see in the activity monitor under sql management studio 2005, those users (clients) are holding connections with sql server, but they are idle and only reporting sleeping status.
I know that is not so easy to find out if user is only in progress of writing invoice, or if someone only leaved his PC for a short time, or if someone leaved for holiday and left his PC turned on with running app. I would like to detect users whose didn't make any operation on SQL server for a longer time, for example about 30 minutes. Is that possible ?
The next step would be killing those users, whose are not working for a long time and only simulate their work.
Thanx for any advice !
December 11, 2007 at 5:16 am
If you run the system stored procedure sp_Who2 you will see a LastBatch column indicating the last time a batch was executed by the spid. This is probably your best indicator.
I would recommend not killing these processes unless they are actually causing a problem. There is nothing wrong with an application remaining connected to the database server. In fact, repeated disconnects and re-connects kills the performance of a lot of applications. Killing a process for an application that expects to remain connected could have some ugly consequences at the user interface. You would also really need to know if there were any open transactions for that connection as you would be rolling back any uncommited work.
On a side note, if your company is this interested in how many of their people are sitting idle and not doing any work, they need to hire more effective managers. I would think a better way to determine if people are working is to guage the amoun of work they get done, not the amount of time they sit idle.
December 11, 2007 at 10:32 am
You can always set the idle time to a desired time-limit. Usually this is done for security reasons and not really for performance-related issue. SQL Server 2005 can handle that.
Regards,
SQL Server Database Administrator
December 11, 2007 at 9:36 pm
I agree with Michael on this one. Usually the application tier will handle session length; however, in your case you have both application connections and users logged directly into SSMS. You could create a procedure that kill connections that meet certain criteria by capturing sp_who2 results.
One should note and correct me if I am wrong, sp_who2 is still an undocumented procedure and may or may not be available in future releases of SQL.
December 11, 2007 at 9:55 pm
sp_who2 is documented and can be used. Querying sysprocesses is not recommended.
You can kill them from SQL Server, but be sure this won't cause issues with the application. Some aren't designed well and might "blow up" if the connection is killed from the server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply