7

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.

4 Answers 4

6

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.

1

In addition to devio's advice, consider:

  • Do you really need to do SELECT *, if you only need a few columns, spell those out; if orders is a very wide table, it makes the overhead for the sort higher; if there are blobs/text fields, they get dragged along for the ride.
  • Can you add a date selection, such as WHERE Date_Ordered > getdate() -1; even if you get more records than you need, the whole table won't have to be copied to Tempdb; you could do something like

    SELECT TOP 100 * from (select * from orders where date_ordered >getdate() - 10) order by date_ordered desc

  • But adding an index on date_ordered is your best solution by far.

1

OK, so first problem, you need more RAM in your SQL Server. How much RAM is installed in the machine.

After that you need to increase the size of the hard drive which hosts the tempdb database. This database is used for all sorts of stuff, and you need it to be fully functional for the entire SQL Server to be working correctly.

In the short term, restart the SQL Service, that will drop the tempdb database and recreate it at it's initial size. In the long term you'll need to increase the hard drives size so that the tempdb can grow to the size that it needs to be.

0

do you have enough space on your disk which contains tempdb files.

try running the same query without order by clause and see if error goes away.

You must log in to answer this question.