Inconsistency between sys.master_files and sys.database_files

• phil_mw60

  SSChasing Mays

  Points: 606

  More actions

  November 30, 2015 at 10:43 am

  #322566

  Hi,

  We have a strange situation in one of our SQL 2012 servers whereby a particular database keeps ending up in an inconsistent state.

  The logical name (for either DATA or T-LOG files) recorded in MasterDB sys.master_files is different to what is recorded in sys.database_files in the DB itself (and displayed in the SSMS GUI under properties / Files). It's not massively different (in sys.master_files it has a _[VersionNumber] appended to it).

  This causes our SCOM monitoring software to think the DB is running out of space and raises a critical alert. If you try to shrink one of the inconsistent files you get a message stating "Could not locate file <filename> for database <DBName> in sys.database_files"

  Does anyone know what could be causing this to happen? I suspect it may be a user trying to rename one of the logical names and it gets updated in sys.master_files but not sys.database_files.

  I'd be very grateful for any suggestions as to what might cause this and how to stop it happening.

  Many Thanks,

  Phil