Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1736
  • Last Modified:

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
0
sqlguru2012
Asked:
sqlguru2012
  • 2
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
did you actually delete those files using ssms before restarting the sql service ?
0
 
sqlguru2012Author Commented:
I honestly don't remember I did this a few months back...but the server has been restarted quite a few times since then.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you can right click on tempdb -> check the properties
see if those files are there, if so, delete them and restart the service
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Scott PletcherSenior DBACommented:
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
ALTER DATABASE tempdb REMOVE FILE tempdevX

--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
WHERE
    database_id = 2
0
 
sqlguru2012Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
You're welcome!  You should be OK now, but good luck when it restarts, just in case :-) .
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now