From: esquel@sommarskog.se   
      
   aj (ronald@mcdonalds.com) writes:   
   > SQL Server 2005 SP2 9.0.3054   
   >   
   > A few times now I have had a misbehaving process that ate up way   
   > too much database temp space.  I identify the process and then   
   > kill the process in the Activity Monitor.   
   >   
   > However, it doesn't release the temp space it is using.  I have   
   > to restart the engine to get the temp space back.   
   >   
   > Why would this be happening?  How can I get around this and get   
   > the temp space back that a killed process was using?   
      
   Did you check that the spid went away? If a process has an active   
   transaction, the transaction must be rolled back before the process   
   actually goes away. A ROLLBACK can take considerable time.   
      
   Or do you mean that tempdb expanded? Unless you have set tempdb to   
   autoshrink (which is a very bad idea), tempdb will remain the size it   
   has, unless you restart SQL Server.   
      
   But if this happens frequently, what you should to is to expand tempdb   
   to the typical size it gets when you have these misbehaving queries.   
   The queries may be misbehaving, but your server is not served by spending   
   time on autogrow operations.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)