SQL Server Performance

SQL 2005 Activity Monitor

Discussion in 'SQL Server 2008 General DBA Questions' started by michellesbs, Dec 28, 2010.

  1. michellesbs New Member

    Hi,
    Anyone can help on below :
    1) Why The Activity Monitor still listed out a lot of ProcessID which run few days ago ?
    2) is it still holding any incomplete task in memory ?
    3) how to clear all these ProcessID instead of Kill process one by one ?
    4) if clear all these ProcessID will it cause any corrupted ?
    Thanks.
  2. satya Moderator

    Just a clarification required, is this a SQL2008 Q or SQL2005 Q. As you have posted a SQL 2005 related Q in 2008 forum section.
    Also it seems you are firing away lots of questions on managing your fort, good that you are on trace. Anyways answering your list as follows:
    1) Why The Activity Monitor still listed out a lot of ProcessID which run few days ago ?
    That SPID may be an obsolete one whereSQL Server will never attempt to kill/disconnect a connection unless it is instructed programatically or Server services are restarted.
    2) is it still holding any incomplete task in memory ?
    May be or may be not, you can check whether any open transactions exist by using DBCC OPENTRAN statement. FOr sure the memory is clogged with this obsolete process if nothing is happening.
    3) how to clear all these ProcessID instead of Kill process one by one ?
    Why to kill or clear the ProcessID when these are initiated from the application, may be check the statements or TSQL that is running to clear our once a transaction/task is finished.
    4) if clear all these ProcessID will it cause any corrupted ?
    Yes if there is a transaction that is runnign in half-way thru may cause a ROLLBACK and sometime leaves the data corrupted too.

Share This Page