Sys.master_files showing extra data files

Posted on 2012-09-06
Last Modified: 2012-09-07
I recently moved the tempdb to a different drive and in the process totally removed 3 extra ndf files that were no longer needed.  Tempdb currently resides on the f:\.  After moving the tempdb and restarting sql to verify it worked we removed the drives U:\, g:\ and J:\ from the server.  The old place where the ndf files for the tempdb were located.  As you can see from the sys.master_files the ndf files are still being referenced.  This is a 2 node cluster and the issue is when it fails over to node 2, sql won't start saying that it can find the extra ndf files.   When I run sys.database_files on the tempdb it verifies that there is only 1 data and 1 log file.  Is there anyway to remove the entries from the sys.master_files as this is causing our cluster to be useless?
Question by:sqlguru2012
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    did you actually delete those files using ssms before restarting the sql service ?

    Author Comment

    I honestly don't remember I did this a few months back...but the server has been restarted quite a few times since then.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    you can right click on tempdb -> check the properties
    see if those files are there, if so, delete them and restart the service
    LVL 68

    Accepted Solution

    First, let's try the standard command to remove files from a database:

    ALTER DATABASE tempdb REMOVE FILE tempdev2
    ALTER DATABASE tempdb REMOVE FILE tempdev3

    --You may get an error message (since SQL won't be able to delete the file itself),
    --but SQL might still remove them from the master table (hopefully!).

    SELECT *
    FROM sys.master_files
        database_id = 2

    Author Comment

    That removed the files from the sys.master_files, with no errors.  Hopefully it'll failover correctly now.  Gotta wait for the change window to test though.  Thanks for the help.
    LVL 68

    Expert Comment

    You're welcome!  You should be OK now, but good luck when it restarts, just in case :-) .

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now