Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2005. it's a running plant, Everyday large number of data posted into SQL Server 2005

SELECT
  TOP 5000 [T_idx], [T_TAG], [T_Date]
FROM
  [house].[dbo].[total]
ORDER BY
  T_Date DESC

From SQL Server 2005 I executed above script, and observed below error message:

5000 row effected

Msg 9002, Level 17, State 6, Line 1
The log fie for database 'tempdb' is full. Backup the transaction log for the database to free up some log space.

and the 'tempdb' file size 860 Mb and my hard disk free size 9.4 GB. Is this space is enough or do I need to free-up some space?

share|improve this question
3  
I think you should set auto grow parameter for tempdb log file so it will grow until the disk volume is full. Why is tempdb full, and how can I prevent this from happening? –  valex Jan 10 at 12:51
    
Thankfully this is no longer the exact text for this error message; it no longer gives the incorrect advice to back up tempdb's transaction log. Not sure which version this changed in. –  Aaron Bertrand Jan 10 at 16:47
    
i have SQL2005 SP3, i got the "The log file for database 'tempdb' is full. Backup the transaction log for the database to free up some log space" message when i am executing the script. –  venkat Jan 12 at 15:48
add comment

migrated from stackoverflow.com Jan 10 at 12:57

This question came from our site for professional and enthusiast programmers.

1 Answer

Without a suitable index, SQL Server has to perform a full sort of the data to satisfy your query. You can verify this is occurring by looking for a Sort or Top N Sort operator in the execution plan:

Sort

A suitable covering index that would avoid the need to sort in your query is:

CREATE NONCLUSTERED INDEX nc1
ON dbo.total (T_Date)
INCLUDE (T_idx, T_TAG);

When an explicit sort is needed, if SQL Server does not (or cannot) allocate sufficient memory to perform the sort entirely in memory, it will use tempdb. The allocation of sort run space in tempdb is a logged operation, which directly contributes to tempdb log space usage.

In addition, the sort starts a system transaction in tempdb to ensure sort allocations are correctly undone in case the transaction aborts. This open transaction can prevent tempdb log space being reused until the sort completes. Executing DBCC OPENTRAN (tempdb) may show an open transaction with sort_init in the description.

You should also review the SQL Server product documentation and this article to determine how to configure your tempdb database optimally for your workload.

share|improve this answer
    
By using .BAT file we are exporting data from SQLServer 2005 to .TXT file. so that user can see the data for his records. The bat file contains same code as stated above. in addition to that total_Log.ldf file 32.6 GB is there. when we are running the bat file and executing the code in SQL we are getting the same warning message. –  venkat Feb 3 at 23:28
    
@venkat Did you try adding the suggested index? –  Paul White Feb 4 at 1:57
    
Dear Paul White, could you please tell me how to add above suggested code in my script, i am new to SQL, please help me....... CREATE NONCLUSTERED INDEX nc1 ON dbo.total (T_Date) INCLUDE (T_idx, T_TAG); SELECT TOP 5000 [T_idx], [T_TAG], [T_Date] FROM [house].[dbo].[total] ORDER BY T_Date DESC –  venkat Mar 7 at 10:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.