Geek asks and answers

Why am I getting "The log file for the database" tempdb "is full"

We have a payment table that has 35 columns with a primary key (autoinc bigint) and 3 non-clustered, non-unique indexes (each with one int column).

Among the table columns, there are two datetime fields:

  • payment_date datetime NOT NULL

  • edit_date datetime NULL

The table has about 1,200,000 rows. Only ~ 1000 rows have an edit_date = null column. 9000 lines have edit_date not null and not equal 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

enter image description here

Server

It takes a few seconds to do this. But if we run query 2 :

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

enter image description here

"tempdb" . , .

* , . 3

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

enter image description here

.

?

1 , , . , 2 tempdb.

ANSWER , , - , . , : sql - ( 1 2); . , tempdb , ...

, , , , ( )

+5
2

, tempdb, sorts joins, .

, SQL , temp db . , gazzilion .

:

  • , .
  • tempdb , , gazzilion, tempdb
+5

tempdb , , . , tempdb #temp. tempdb, temp. , tempdb :

  • , , SQL tempdb;
  • , tempdb, ;
  • DBCC CheckDB ( " " ) tempdb - on , ;
  • DBCC DBREINDEX DBCC " tempdb" set tempdb;
  • , /, , , , , tempdb;
  • , , , tempdb;

  • ODBC DSN " .

    tempdb       GO

        SELECT name 
        FROM tempdb..sysobjects 

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

rowcount, , , , .

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


-- 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

: http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html
: http://social.msdn.microsoft.com/Forums/is/transactsql/thread/af493428-2062-4445-88e4-07ac65fedb76

+4

More articles:


All Articles