We're running SQL Server 2000. In our database, we have an "Orders" table with approximately 750,000 rows. We can perform simple SELECT statements on this table. However, when we want to run a query like SELECT TOP 100 * FROM Orders ORDER BY Date_Ordered DESC, we receive the following message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is
full. Back up the transaction log for
the database to free up some log
space.
We have other tables in our database which are similar in size of the amount of records that are in the tables (i.e. 700,000 records). On these tables, we can run any queries we'd like and we never receive a message about 'tempdb being full'.
To resolve this, we've backed up our database, shrunk the actual database and also shrunk the database and files in the tempdb system database, but this hasn't resolved the issue.
The size of our log file is set to autogrow.
We're not sure where to go next. Are there any ideas why we still might be receiving this message?
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is
full. Back up the transaction log for
the database to free up some log
space.
Best Answer
According to this article, the temp DB is used if sorting required more memory than SQL Server has allocated.
If you sort over a column that is not indexed, the database server will perform a full table scan and keep track of all Date_Ordered values (and primary key values) of all records in the table.
Create an INDEX on Orders.Date_Ordered to speed up sorting and reduce memory usage.