Help! The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.