From: esquel@sommarskog.se   
      
   aj (ronald@mcdonalds.com) writes:   
   > I think I had a fundamental misunderstanding of how tempdb operates.   
   > I was thinking that, as processes used temp space, the size of the   
   > tempdb .mdf files would increase, and then when the process ended   
   > and the space was freed, the mdf files would decrease in size.   
   >   
   > But instead tempdb space acts just like other regular database space   
   > in that the mdf files increase in size, and stay at that size even as   
   > the amount of temp space allocated within that file goes up and down.   
   >   
   > So I want to be alerted when a misbehaving process is using way too   
   > much temp space and trouble is brewing.  Right now, my alert is based on   
   > the "Data File(s) Size (KB)" Counter for the tempdb instance.  (See   
   > the alert dialog).   However it looks like that is not what I want.   
      
   There is the DMV sys.dm_db_task_space_usage which returns page allocation   
   and deallocation activity by task for tempdb. However, I don't have any   
   good idea how to set up an alert built on this view. (I don't work   
   with setting up alerts.)   
      
      
      
      
   --   
   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)