查詢資料庫狀態(Database States)，使用 sys.databases 與 sys.master_files 為例

示範環境：
SQL Server 2012

雖然，「資料庫和檔案目錄檢視」：sys.databases 與 sys.master_files 都可以查詢資料庫狀態，
但使用 sys.databases 會是能即時反應資料庫狀態的異動。

---

請參考以下的範例程式碼：

-- 01_建立資料庫：Arch02
USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Ach02')
BEGIN
 ALTER DATABASE Ach02 
  SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE Ach02
END
GO
CREATE DATABASE Ach02
GO

-- 02_查詢資料庫狀態
-- sys.databases：ONLINE
SELECT name N'資料庫', state_desc N'資料庫狀態' FROM sys.databases
WHERE name='Ach02'

-- sys.master_files：ONLINE
SELECT name N'資料庫', state_desc N'資料庫狀態'  FROM sys.master_files
WHERE name='Ach02'
GO

-- 01_查詢資料庫狀態

-- 03_對資料庫：Ach02 設定為：離線
USE master
GO
ALTER DATABASE Ach02 
 SET OFFLINE;
GO

-- 04_查詢資料庫狀態
-- sys.databases：OFFLINE
SELECT name N'資料庫', state_desc N'資料庫狀態' FROM sys.databases
WHERE name='Ach02'

-- sys.master_files：ONLINE
SELECT name N'資料庫', state_desc N'資料庫狀態'  FROM sys.master_files
WHERE name='Ach02'
GO

-- 02_查詢資料庫狀態

-- 05_查詢資料庫：邏輯檔案、實體檔案、資料庫狀態
SELECT DB_NAME (mf.database_id) N'資料庫',mf.name N'邏輯檔案', mf.type_desc N'檔案類型',mf.physical_name N'實體檔案', db.state_desc N'資料庫狀態'
FROM sys.master_files mf INNER JOIN sys.databases db
ON mf.database_id = db.database_id

WHERE DB_NAME (mf.database_id) = 'Ach02'
GO

-- 03_查詢資料庫狀態

-- 06_對資料庫：Ach02 設定為：上線
USE master
GO
ALTER DATABASE Ach02 
 SET ONLINE;
GO

-- 07_對資料庫：Ach02 設定為：EMERGENCY
USE [master]
GO
ALTER DATABASE [Ach02] 
 SET  EMERGENCY  WITH NO_WAIT
GO

-- 08_查詢資料庫狀態
-- sys.databases：EMERGENCY
SELECT name N'資料庫', state_desc N'資料庫狀態' FROM sys.databases
WHERE name='Ach02'

-- sys.master_files：ONLINE
SELECT name N'資料庫', state_desc N'資料庫狀態'  FROM sys.master_files
WHERE name='Ach02'
GO

-- 04_查詢資料庫狀態

-- 09_查詢資料庫：邏輯檔案、實體檔案、資料庫狀態
SELECT DB_NAME (mf.database_id) N'資料庫',mf.name N'邏輯檔案', mf.type_desc N'檔案類型',mf.physical_name N'實體檔案', db.state_desc N'資料庫狀態'
FROM sys.master_files mf INNER JOIN sys.databases db
ON mf.database_id = db.database_id

WHERE DB_NAME (mf.database_id) = 'Ach02'
GO

-- 05_查詢資料庫狀態

---

認識 sys.databases (Transact-SQL)

包含 Microsoft SQL Server 的執行個體中每個資料庫各一列。

如果資料庫不在線上，或者 AUTO_CLOSE 設為 ON 且資料庫已關閉，則某些資料行的值可能是 NULL。
如果資料庫是離線狀態，則低權限的使用者就看不到對應的資料列了。

如果資料庫在離線狀態時，使用者要查看對應的資料列，至少必須具備 ALTER ANY DATABASE 伺服器層級權限或 master 資料庫中的 CREATE DATABASE 權限。

剛上線的資料庫不一定馬上能接受連接。
若要識別資料庫可接受連接的時間，請查詢 sys.databases 的 collation_name 資料行或 DATABASEPROPERTYEX 的 Collation 屬性。

當資料庫定序傳回非 Null 值時，表示資料庫可接受連接。
對於 AlwaysOn 資料庫，可以查詢 sys.dm_hadr_database_replica_states 的 database_state 或 database_state_desc 資料行。

--
認識 sys.master_files (Transact-SQL)

依照 master 資料庫的儲存情況，資料庫的每個檔案都會有一個資料列。這是單一全系統的檢視。

附註
當您卸除或重建大型索引時，或卸除或截斷大型資料表時，Database Engine 會延遲取消配置實際的頁面及其相關聯鎖定，直到認可交易之後。
延遲的卸除作業並不會立即釋出已配置的空間。

因此，在卸除或截斷大型物件之後，sys.master_files 立即傳回的值不一定能反映實際可用的磁碟空間。

--
認識 資料庫狀態

資料庫永遠都在特定的狀態。
例如，這些狀態包括 ONLINE、OFFLINE 或 SUSPECT。

若要驗證資料庫目前的狀態，請選取 sys.databases 目錄檢視中的 state_desc 資料行或是在 DATABASEPROPERTYEX 函數中的 Status 屬性。

OFFLINE：
資料庫是無法使用的。 明確的使用者動作可使資料庫變成離線狀態，並且在採取其他的使用者動作之前都是離線狀態。

例如，可以將資料庫設成離線，好讓檔案移到新的磁碟中。 在完成移動後，就會將資料庫重新啟動為線上狀態。

EMERGENCY：
使用者已變更資料庫並將狀態設為 EMERGENCY。 資料庫是在單一使用者模式下，而且可以進行修復或還原。
資料庫是標示為 READ_ONLY、記錄已停用並限定只有系統管理員 (sysadmin) 固定伺服器角色的成員才可存取。

EMERGENCY 主要是做為疑難排解的用途。
例如，標示為有疑問的資料庫可以設為 EMERGENCY 狀態。 這將可允許系統管理員唯讀存取資料庫。
只有系統管理員 (sysadmin) 固定伺服器角色的成員，可以將資料庫設定為 EMERGENCY 狀態。

---

參考資料

資料庫狀態
http://msdn.microsoft.com/zh-tw/library/ms190442.aspx

sys.databases
http://msdn.microsoft.com/zh-tw/library/ms178534.aspx

sys.master_files
http://msdn.microsoft.com/zh-tw/library/ms186782.aspx