Couple of days back Kanika introduced me with awkward scenario she was facing.
Thanks for screen shots Kanika.
She got 3 different no. of files from different system tables in sql server and properties window.
- Select * FROM sysfiles
- Select * FROM sys.database_files
- Select * from sys.master_files where database_id = 2
- select * from sys.sysaltfiles where dbid =2 order by fileid
- check database properties from GUI
- Physically checked tempdb files on disk
- 1 Set of ouput from : sysfiles and sys.database_files – Showing 3 files (2 data and 1 log file)
- 2nd Set of output from : sys.master_files and sys.sysaltfiles – Showing 7 files (5 data and 1 log file)
- 3rd set of output from database properties (2 data and 1 log file)
- 4th Set of output physically checked files 5 files (3 data file and 2 log file)
Physically verified files:
Now Question is why? What could be reason for same
Few Justifications I get on tech-net are:
- The view sys.master_files is something new and is updated asynchronously. It doesn't updates immediately.
- When you re-start your SQL Server, SQL Server will re-create tempdb based on sys.master_files.
- The sys.master_files tell you about any tempdb data file which was there on your system (the number of tempdb files) with which your server have started.
- While sys.database_files shows currently used tempdb files. Its quite possible that not all tempdb data files came online.
- You can read the errorlog look for any error meesage did any of the files did not come online.
- After you started sql server somebody might have executed SQL commands to remove or add tempdb files.