Post reply

Remove TempDB data files

  • August 18, 2017 at 2:48 am

    #339329

    Hi all,

    I have accidentally added 2 directories for TempDB data files (D:\ and E:\) now i have duplicate files in both drives. How can i remove either one of the drive and just keeping one drive? These are new setup hence files has no data. Thanks in advance!

  • August 18, 2017 at 2:57 am

    #1955778

    What is the configuration of your TempDB now?  Are all of the files from sys.database_files shown in D or E or a mixture?

    If they are all in one specific directory the other isn't used and can be removed without issue.

    If they are mixed between D and E you will need to modify the filelocation and restart the instance to take effect then you can remove the unneeded location.

  • August 18, 2017 at 3:14 am

    #1955782

    Thanks Anthony! It is mixed between D & E. You mean i need to modify the file location to my desire drive  first (E:\) and remove unwanted files in D:\ Would it cause any error?

  • August 18, 2017 at 3:17 am

    #1955783

    oceanics8 - Friday, August 18, 2017 3:14 AM

    Thanks Anthony! It is mixed between D & E. You mean i need to modify the file location to my desire drive  first (E:\) and remove unwanted files in D:\ Would it cause any error?

    Run your alter database modify file commands to modify all files which are on D and modify them so they are on E.  Stop the SQL service, start the SQL service, upon starting it will create the missing files on the E drive.

    Once SQL has started ok, you can remove everything in D.

  • August 18, 2017 at 3:22 am

    #1955784

    Thanks Anthony! Hope there is no error after removing files! TGIF enjoy...

  • August 20, 2017 at 11:26 am

    #1955913

    Riic - Friday, August 18, 2017 3:22 AM

    Thanks Anthony! Hope there is no error after removing files! TGIF enjoy...

    You could always just rename them until you are sure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 20, 2017 at 11:32 am

    #1955915

    Riic - Friday, August 18, 2017 3:22 AM

    Thanks Anthony! Hope there is no error after removing files! TGIF enjoy...

    Run the alter database, then restart SQL. Once SQL's up, then, and only then, try to delete the files. If you can delete them, then they're not in use and it's safe to do so as they're no longer in use.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply