SQL Server Performance

Temp DB Full

Discussion in 'Getting Started' started by rashmi_1234, Feb 21, 2011.

  1. rashmi_1234 New Member

    HiEven after running the Shrink TempDB and Resolve_TempDBSpaceIssue JOBS regularly , Temp DB gets full again and again.What is the exact reason adn solution for this .
  2. ismailadar New Member

    Hi there can be lot of reasons but mostly large resultsets involving unions,espacially order by or group by, temp tables. on the other hand any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb.You can find out about temp tables you can run tjhis query against temp dbUSE
    tempdb GO SELECT OBJECT_NAME(id) ojeName, rowcnt
    FROM tempdb..sysindexes WHERE OBJECT_NAME(id) LIKE '#%' ORDER BY rowcnt DESC
    After that you can drop temp tables that are not needed any more also you can check sysprocesses in order to find any uncommitted and not rolled back transaction.
  3. Luis Martin Moderator

    [quote user="rashmi_1234"]
    HiEven after running the Shrink TempDB and Resolve_TempDBSpaceIssue JOBS regularly , Temp DB gets full again and again.What is the exact reason adn solution for this .
    [/quote]
    Do you have 1 tempdb for each processor?
  4. RamJaddu Member

  5. satya Moderator

    What kind of free disk space problem you have where TEMPDB is located?
    I wouldn't recommend to SHRINK the tempdb on regular basis, as it is used extensively by SQL Server since version 2005 onwards.

Share This Page