How to Restrict Database for One User in SQL Server 2008 R2?

How do I get SQL Server to resume cleaning up SPIDs?

• Recently our SQL Server 2008 R2 has began to stop cleaning up defunct SQL processes as show by and EXEC sp_who2.  From reading I know a ghost process should run every 10 seconds and clean these process up.   But when our system get busy the processes start to accumulate and because they are not released they consume more and more resources until I restart the database.  Most of these process are users doing development under SQL Server Manager.  I've asked the developers to voluntarily limit themselves to 4 open windows but it doesn't seem to be helping.  I've tried killing spids but I'd have to type all day and I'm afraid of killing an active one.

• Answer:

  If the processes are not being released because your developers are leaving connections open, that's pretty standard behavior. If a connection is open, it won't be cleaned up by any internal processes because it's supposed to be left open.   As far as I know, the ghost cleanup process if for removing deleted records (see this entry from Paul Randal: http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Ghost-cleanup-in-depth.aspx) not with removing completed connections.   Instead of using sp_who2, use the Dynamic Management Objects (DMO) to get a better understanding of exactly what's going on. You can join sys.dm_exec_requests to sys.dm_exec_sql_text to understand exactly what SQL statements are being run by the active connections to see if they're simply sitting there open or are actively running a process.   I'd suggest adding a default connection timeout and see if that helps remove processes that are no longer active.