Why do I get "The log file for database 'tempdb' is full"

Question

Let we have a table of payments having 35 columns with a primary key (autoinc bigint) and 3 non-clustered, non-unique indeces (each on one int column).

Among the table's columns we have two datetime fields:

1. payment_date datetime NOT NULL

2. edit_date datetime NULL

The table has about 1 200 000 rows. Only ~1000 of rows have edit_date column = null. 9000 of rows have edit_date not null and not equal to payment_date Others have edit_date=payment_date

When we run the following query 1:

select top 1 *
from payments
where edit_date is not null and (payment_date=edit_date or payment_date<>edit_date)
order by payment_date desc

server needs a couple of seconds to do it. But if we run query 2:

select top 1 *
from payments
where edit_date is not null
order by payment_date desc

the execution ends up with The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

If we replace * with some certain column, see query 3

select top 1 payment_date
from payments
where edit_date is not null
order by payment_date desc

it also finishes in a couple of seconds.

Where is the magic?

EDIT I've changed query 1 so that it operates over exactly the same number of rows as the 2nd query. And still it returns in a second, while query 2 fills tempdb.

ANSWER I followed the advice to add an index, did this for both date fields - everything started working quick, as expected. Though, the question was - why in this exact situation sql server behave differently on similar queries (query 1 vs query 2); I wanted to understand the logic of the server optimization. I would agree if both queries did used tempdb similarly, but they didn't....

In the end I mark as the answer the first one, where I saw the must-be symptoms of my problem and the first, as well, thoughts on how to avoid this (i.e. indeces)

Answer 1

This is happening cause certain steps in an execution plan can trigger writes to tempdb in particular certain sorts and joins involving lots of data.

Since you are sorting a table with a boat load of columns, SQL decides it would be crazy to perform the sort alone in temp db without the associated data. If it did that it would need to do a gazzilion inefficient bookmark lookups on the underlying table.

Follow these rules:

1. Try to select only the data you need
2. Size tempdb appropriately, if you need to do crazy queries that sort a gazzilion rows, you better have an appropriately sized tempdb

Answer 2

Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth. Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:

• any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
• if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
• DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
• DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
• large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;
• any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;

• use of an ODBC DSN with the option 'create temporary stored procedures' set can leave objects there for the life of the connection.

  USE tempdb GO

      SELECT name 
          FROM tempdb..sysobjects 
  
      SELECT OBJECT_NAME(id), rowcnt 
          FROM tempdb..sysindexes 
          WHERE OBJECT_NAME(id) LIKE '#%' 
          ORDER BY rowcnt DESC
  

The higher rowcount, values will likely indicate the biggest temporary tables that are consuming space.

Short-term fix

DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')
DBCC INPUTBUFFER(<number>)
KILL <number>

Long-term prevention

-- SQL Server 7.0, should show 'trunc. log on chkpt.' 
-- or 'recovery=SIMPLE' as part of status column: 

EXEC sp_helpdb 'tempdb' 

-- SQL Server 2000, should yield 'SIMPLE': 

SELECT DATABASEPROPERTYEX('tempdb', 'recovery')
ALTER DATABASE tempdb SET RECOVERY SIMPLE

Reference : https://web.archive.org/web/20080509095429/http://sqlserver2000.databases.aspfaq.com:80/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html
Other references : http://social.msdn.microsoft.com/Forums/is/transactsql/thread/af493428-2062-4445-88e4-07ac65fedb76