none
How can I find out what is enlarging tempdb?

    Question

  • I was creating some views and a clustered index on a view today.  Worked just fine on SQL 2000 development server and then tried the same thing on SQL 2005 production.  But much too my surprise I find out while waiting for the query to finish that the tempdb database on production has grown to 150 GB!!!  How could tempdb get that big when the current data file is only 4gb?  So any sql statements or methods for figuring out what is sending the tempdb size into outer space would be appreciated.  Thanks!
    Thursday, May 01, 2008 7:55 PM

Answers

  •  

    Try to use the Reports option in SQL Server 2005 and see the "Disk Usage by Table" and "All Transactions" reports.

     

    In Management Studio, right click on tempdb database and chose Reports. Log file will grow when a transaction is open; find the table in tempdb having maximum records.

     

    HTH

     

    Regards,

    Santy 

    Thursday, May 01, 2008 11:53 PM

All replies

  • Must be a run-away query, such as cartesian product.

     

    If you look in tempdb what do you see?

     

    Can you run this script and post result:

    Code Snippet

     

    USE master;

    GO

     

    SELECT

    'Microsoft SQL Server ' +

    convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' @#-- ' +

    convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' @#-- ' +

    convert(varchar, SERVERPROPERTY('Edition') ) + ' @#-- ' +

    convert(varchar, SERVERPROPERTY('EngineEdition') ), @@VERSION ;

    GO

     

    EXEC sp_dbcmptlevel AdventureWorks

     

    GO

     

     

    Thursday, May 01, 2008 8:16 PM
  • SQL Server 2005 can put a lot more stress on TempDB than SQL Server 2000 did.  Are you using any new features such as snapshot isolation?  You can run this to find out:

     

    SELECT [name], snapshot_isolation_state_desc

    FROM sys.databases

     

    How many files do you have for tempDB and how full are they?

     

    -- Query That Shows the Original and Current Size of all Files in Tempdb

    SELECT alt.filename ,alt.name ,alt.size * 8.0 / 1024.0 AS OriginalSize_MB

    ,files.size * 8.0 / 1024.0 AS CurrentSize_MB

    FROM master.dbo.sysaltfiles AS alt

    INNER JOIN tempdb.dbo.sysfiles AS files

    ON alt.fileid = files.fileid

    WHERE dbid = db_id('tempdb')

     

    Thursday, May 01, 2008 10:42 PM
    Moderator
  •  GlennAlanBerry wrote:

    SQL Server 2005 can put a lot more stress on TempDB than SQL Server 2000 did.  Are you using any new features such as snapshot isolation? 

     

    Truly so. But 150GB on a small database?

    Thursday, May 01, 2008 11:26 PM
  •  

    Try to use the Reports option in SQL Server 2005 and see the "Disk Usage by Table" and "All Transactions" reports.

     

    In Management Studio, right click on tempdb database and chose Reports. Log file will grow when a transaction is open; find the table in tempdb having maximum records.

     

    HTH

     

    Regards,

    Santy 

    Thursday, May 01, 2008 11:53 PM
  • Thanks.  We think replication and synchronous triggers caused the problem.  One database replicates and causes triggers to update another table on the server.  I locked that last table while making some indexes on a view, which I think cascaded a big block.  But they have lots of long queries on that last table, so I imagine the system locks up from that too.
    Friday, May 02, 2008 12:56 PM
  • snapshot isolation is on for master and msdb.  What does that do?
    Friday, May 02, 2008 12:58 PM
  • Microsoft SQL Server 9.00.3054.00 @#-- SP2 @#-- Standard Edition @#-- 2

    Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
    Friday, May 02, 2008 1:03 PM
  • Can you look inside (Object Explorer) what is large?

     

    When you restart the server it will go back to 0, start observing what makes it grow.

     

     

     

     

    Friday, May 02, 2008 1:49 PM