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

• rmaganti

  SSC Enthusiast

  Points: 152

  More actions

  September 16, 2008 at 9:32 am

  #192911

  All,

  I am getting the above message when I ran a query which goes like

  'SELECT TOP 200 * from table A order by col1 desc' -- There is abt 364000 records in the table..

  The tempdb is set up for unrestricted file growth and there is enough hard drive space on D:\ (350 GB)...The tempdb.mdf file in D:\Program Files\Microsoft SQL Server\MSSQL\Data is only abt 160 MB..

  I reebooted the server and execute the following

  backup transaction DatabaseName with truncate_only

  go

  checkpoint

  go

  But I am still getting the same message when I execute the above SQL statement in Query Analyzer..

  We are on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) which is SP4 and the recovery model for tempdb is set to 'Simple'

  Can you let me know what else I can or need to do at this point ?

  TIA,

  Mag

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738683

  More actions

  September 16, 2008 at 1:59 pm

  #872677

  IF you manually increase tempdb to 200GB or so, does it work?

• rmaganti

  SSC Enthusiast

  Points: 152

  More actions

  September 16, 2008 at 2:11 pm

  #872694

  Steve,

  I haven't tried that...I guess I can use

  USE master

  GO

  ALTER DATABASE tempdb

  MODIFY FILE

  (NAME = tempdev,

  SIZE = say 2048MB)

  GO

  I may not want to go up to 200 G as I have other databases...

  But can I try to shrink the database instead using

  use tempdb

  go

  dbcc shrinkfile (tempdev, 'target size in MB')

  go

  dbcc shrinkfile (templog, 'target size in MB')

  go

  Any pros or cons to either one?

  Thx,

  Mag

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738683

  More actions

  September 16, 2008 at 3:14 pm

  #872727

  I'd make it large and see.

  Shrinking the database shouldn't matter, but it does mess up your indexes (fragmentation)

• rmaganti

  SSC Enthusiast

  Points: 152

  More actions

  September 16, 2008 at 3:33 pm

  #872734

  I did increase the size of temp (tempdb.mdf) to 10 G and I still get the same message....

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738683

  More actions

  September 16, 2008 at 3:38 pm

  #872735

  364k records might be more than 10GB. Have you tried sp_psaceusde to see the size of the table?

• rmaganti

  SSC Enthusiast

  Points: 152

  More actions

  September 16, 2008 at 3:43 pm

  #872738

  Yes I did that and on sp_spaceused here are the stats

  rows reserved data index size unused

  table 364330 177792 KB 144152 KB33512 KB 128 KB

  Added Later

  I added an index on this column and the error message is gone...

  Thx............