SQLTeam.com | Weblogs | Forums

Tempdb Files vs Sys.Master_files


#1

I have several tempdb files (16 to be exact) that show in sys.master_files, but do not show in sys.database_files. I have restarted the server/SQL Server service, but to no avail. Can anyone tell me how to remove these files. These files do not physically exist on the disk, they are just in the sys.master_files. I need to remove these files and just start over creating additional tempdb files.

Thanks.


#2

How did you get in this state?

Why not simply drop tempdb, and restart sql, tempdb should be recreated at that time


#3

I actually restarted the SQL Server Service. Tempdb was re-created, but the physical names still show in sys.master_files.


#4

Is having the names in sys.master_files causing any harm?

What is the file state?

File state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
6 = OFFLINE
7 = DEFUNCT

There's a thread about this problem here:

Dropped FileGroup/Files still show up in Sys.Master_Files

and other places.