Troubleshooting‎ > ‎

SQL Error: tempdb Full

posted Dec 13, 2011, 11:50 AM by Eric Patrick   [ updated Dec 13, 2011, 7:31 PM ]
Standard configuration for our production SQL servers includes a dedicated drive for tempdb, SQL server's 'scratchpad' database. Our configuration follows Microsoft's recommended best practices.

With a complex query, DTA encountered the following SQL error:

Could not allocate space for object 'dbo.SORT temporary run storage:  150581025964032' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The issue was caused by a FROM clause sub-query doing three things:
  • wanted to use an index different than the outer query, 
  • included an ORDER BY clause in the sub-select, and
  • was on a table with 10's of millions of rows (or more)
This essentially forced SQL to do an ORDER of the entire table in tempdb.

The fix is to have the outer query and sub-query use the same index.

Wrong version was:

SELECT ...
FROM Debt (NOLOCK)
INNER JOIN Ledger (nolock)
ON Ledger.LedgerID = ( ... )
INNER JOIN LedgerItem (nolock) -- this uses PK_LedgerItem
  ON LedgerItem.LedgerItemID = ( 
    SELECT  Top 1 LI.LedgerItemID
    FROM  LedgerItem LI (nolock)
    WHERE  LI.LedgerID = Ledger.LedgerID -- this uses IX_LedgerItemByLedgerID 
      AND   ...
            ORDER BY Li.PostedDate DESC, Li.LedgerItemID DESC 
  )

to this:
  
SELECT ...
FROM Debt (NOLOCK)
INNER JOIN Ledger (nolock)
ON Ledger.LedgerID = ( ... )
INNER JOIN LedgerItem (nolock)
ON LedgerItem.LedgerID = Ledger.LedgerID -- this uses IX_LedgerItemByLedgerID
AND LedgerItem.LedgerItemID = (
SELECT  Top 1 LI.LedgerItemID
FROM  LedgerItem LI (nolock)
WHERE  LI.LedgerID = Ledger.LedgerID -- this also uses IX_LedgerItemByLedgerID 
AND ISNULL(LI.LedgerTemplateItemID,1) not in (55,56,57,175) 
AND ...
ORDER BY Li.PostedDate DESC, Li.LedgerItemID DESC
)
WHERE ...
Comments