sqlguru2012
asked on
Sys.master_files showing extra data files
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?
tempdb1.bmp
tempdb2.bmp
tempdb1.bmp
tempdb2.bmp
Aneesh
did you actually delete those files using ssms before restarting the sql service ?
ASKER
I honestly don't remember I did this a few months back...but the server has been restarted quite a few times since then.
you can right click on tempdb -> check the properties
see if those files are there, if so, delete them and restart the service
see if those files are there, if so, delete them and restart the service
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
You're welcome! You should be OK now, but good luck when it restarts, just in case :-) .