sys.master_files.state_desc - OFFLINE vs ONLINE Status ??

• Jeff-67583

  SSCertifiable

  Points: 5625

  More actions

  October 2, 2007 at 3:12 pm

  #198690

  Hello,

  MSSQL2005/SP2 on Win2003Server/SP2

  SELECT name, physical_name AS CurrentLocation, state_desc

  FROM sys.master_files

  WHERE database_id = DB_ID(N'my_databasename');

  The above query returns ONLINE for column "state_desc" from the table sys.master_files. However, if I change the database to OFFLINE using either ALTER DATABASE or via SS Management Studio, running the above query still returns the db status as being ONLINE ??

  Any thoughts?

  Many thanks. Jeff

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 2, 2007 at 4:48 pm

  #738431

  FILE state <> DATABASE state. your query returns FILE states not DATABASE states:

  From BOL:

  In SQL Server 2005, the state of a database file is maintained independently from the state of the database.

  
  * Noel