Data Difference In System Tables In SQL Server

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
Output Description: 
  • 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:

  1. The view sys.master_files is something new and is updated asynchronously. It doesn't updates immediately. 
  2. When you re-start your SQL Server, SQL Server will re-create tempdb based on sys.master_files. 
  3. 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. 
  4. While sys.database_files shows currently used tempdb files. Its quite possible that not all tempdb data files came online.
  5. You can read the errorlog look for any error meesage did any of the files did not come online.
  6. After you started sql server somebody might have executed SQL commands to remove or add tempdb files.

1 comment: