sysfiles and master_files out of sync

• russasaurusRex

  SSC Rookie

  Points: 49

  More actions

  February 28, 2013 at 5:38 am

  #274584

  Hi all,

  I apologise if this has been raised before, but I couldn't find it using search...

  I've come across a situation on one of my servers where the entries in sysfiles and master_files are out of sync for the master database.

  As far as I was aware this is a situation that should not be able to happen, and all tests I've done on my dev server with changing the logical file name (admittedly not on the master database) have failed to recreate it.

  Here is some TSQL I'm using:

  use master;

  go

  --reports master log file logical name as 'master_log'

  select name, fileid

  from sysfiles

  order by fileid;

  --reports master log file logical name as 'mastlog'

  select name, [file_id]

  from sys.master_files

  where database_id = db_id()

  order by [file_id];

  select FILEPROPERTY('master_log', 'SpaceUsed');

  select FILEPROPERTY('mastlog', 'SpaceUsed');

  and the result sets I'm seeing:

  name fileid

  ---------- ------

  master 1

  master_log 2

  name file_id

  ---------- -----------

  master 1

  mastlog 2

  ----------

  68

  ----------

  NULL

  Server is Win2k8 with SP1, and SQL Server instance is 2k8R2 with SP1.

  Any help and/or advice around how things could of got in to this state would be much appreciated!

  Russell