Removing tempdb file

• shahgols

  SSCrazy Eights

  Points: 9874

  More actions

  February 8, 2005 at 12:28 pm

  #102379

  Hi everyone,

  I have two log files for tempdb and wanted to remove one of them. What is the best approach in doing this? Thanks in advance for your help.

• sa24

  SSCrazy Eights

  Points: 8027

  More actions

  February 8, 2005 at 1:25 pm

  #541249

  I don't see much issues with this. tempdb is always in simple recovery mode and you should be able to drop the files. soemthing you might want to do is make the 1st log file big enough so that when you are tring to drop the second file, no transactions are being written to it.

• DCPeterson

  SSCoach

  Points: 15266

  More actions

  February 8, 2005 at 1:27 pm

  #541250

  ALTER DATABASE tempdb

  REMOVE FILE templog2

  where templog2 is the logical name of the extra log file, not the physical name.

  /*****************

  If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

  *****************/

• air_turtle

  Mr or Mrs. 500

  Points: 516

  More actions

  February 9, 2005 at 3:33 am

  #541330

  step 1. clear the transaction log from any transactions

  backup log tempdb with truncate_only

   

  step2. make sure the file is empty (size=0), so that you can call ALTER DATABASE

  dbcc shrinkfile (TempLog_FileNo2, EMPTYFILE

   

  step3

  alter database TempDb

  remove file TempLog_FileNo2

   

  
  Andrey

• EdVassie

  SSC Guru

  Points: 60510

  More actions

  February 9, 2005 at 5:09 am

  #541335

  It has been a while since I last did this, but my recollection is that after issueing the ALTER DATABASE tempdb REMOVE FILE you get a message saying that the change to tempdb will take effect after SQL is next restarted.

  Therefore you would need to plan to cycle SQL server as part of making this change.

   

  Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
  

  When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

• shahgols

  SSCrazy Eights

  Points: 9874

  More actions

  February 9, 2005 at 5:12 pm

  #541493

  Thanks for the info everyone! Very helpful.