Sql-server – ‘Tempdb’ is full warning message in SQL Server 2005

sql serversql-server-2005

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?

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

Related Solutions

Sql-server – SQL Server 2005 Huge DB issue

Shrinking the database will allow for you to give back some space to the O/S, but that space comes at a performance cost as the shrink activity fragments the database. You can fix this by rebuilding your indexes, but that usually causes the space to be reclaimed. And the ides that a shrink would be a solution is false, because you are not addressing the growth issue itself, you are merely trying to put a bandage on a sucking chest wound.

Placing your files on disks that can be expanded easily is your best bet. External storage works well (or a SAN, if possible). You cannot use a UNC path for your database files, but you could use DFS to mount a drive and point to a network location and everything would work fine.

HTH

Sql-server – Query Running Differently on SQL 2005 vs SQL 2008R2

I'd like you to rework the query.

You have sargability issues, and are even using scalar function calls in there, which will be hurting the query too. You may want to make a FullName computed column on Table2 and put an index on that, making sure your index INCLUDEs FirstName and LastName. You should also add indexes that help the other

Also, make an inline table-valued function to do your "RemoveNonLetter" functionality, and rework your query to use that, probably using APPLY as I've done here.

And definitely check that bug that Paul's answer refers to.

INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FullName)) AS fn (FullName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FamilyName)) AS famn (FamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.Child1Name)) AS c1n (Child1Name)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyName)) AS sfn (StepFamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyNameChild2)) AS sfnc2 (StepFamilyNameChild2)
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FullName = fn.FullName
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FamilyName = famn.FamilyName AND Alias2.Child1Name = c1n.Child1Name
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.StepFamilyName = sfn.StepFamilyName AND Alias2.StepFamilyNameChild1 = sfnc2.StepFamilyNameChild2
 ) 
 AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )
;

Best Answer

Related Solutions

Sql-server – SQL Server 2005 Huge DB issue

Sql-server – Query Running Differently on SQL 2005 vs SQL 2008R2

Related Question