none
tempdb full RRS feed

  • Question

  • Hi, my temp db is full everday, what is the reason for that? How can i resolve and plz provide me some steps.
    Wednesday, November 10, 2010 2:42 PM

Answers

  • Hi,

    Which SQL Server version/edition are you using? What’s size set to tempdb?

    In SQL Server 2005/2008, tempdb is heavily used by many features such as temporary tables created by users, worktables hold intermediate results created internally by SQL Server, DBCC CHECKDB/CHECKTABLE, Cursors, Database Mail, online index creation, index creation and many other features. As a result, the tempdb sizing and configuration is critical for optimal functioning and performance of SQL Server.

    To work around this issue, make sure you configured a proper size for tempdb database according your workloads; also tuning your queries to cut down usage of tempdb. A well-documented article on Books Online which is about tempdb in SQL Server 2005 most of which also applies to SQL Server 2008, see Working with  tempdb in SQL Server 2005 (http://technet.microsoft.com/en-us/library/cc966545.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, November 12, 2010 7:26 AM
    Moderator

All replies

  • There is a performance counter you can use to determine why it is growing so much.

    I suspect somebody is doing large updates (on many rows) or deletes, a good article on the technique to finding why it is growing is http://www.simple-talk.com/sql/database-administration/tracking-tempdb-growth/


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, November 10, 2010 2:50 PM
    Moderator
  • Hi,

    Which SQL Server version/edition are you using? What’s size set to tempdb?

    In SQL Server 2005/2008, tempdb is heavily used by many features such as temporary tables created by users, worktables hold intermediate results created internally by SQL Server, DBCC CHECKDB/CHECKTABLE, Cursors, Database Mail, online index creation, index creation and many other features. As a result, the tempdb sizing and configuration is critical for optimal functioning and performance of SQL Server.

    To work around this issue, make sure you configured a proper size for tempdb database according your workloads; also tuning your queries to cut down usage of tempdb. A well-documented article on Books Online which is about tempdb in SQL Server 2005 most of which also applies to SQL Server 2008, see Working with  tempdb in SQL Server 2005 (http://technet.microsoft.com/en-us/library/cc966545.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Friday, November 12, 2010 7:26 AM
    Moderator