搜尋本站文章

2009-02-17

認識 SQL Server Database 資料庫選項:AUTO_CLOSE(自動關閉)



資料庫 AUTO_CLOSE 選項
選項值說明
ON(True)當設定為:ON 時,資料庫會完全關閉,並在最後一個使用者結束之後釋放其資源。當使用者試圖重新使用資料庫時,會自動重新開啟資料庫。例如,藉由發出 USE database_name 陳述式。
這個選項可能會造成經常存取之資料庫的效能降低,因為在每一個連接之後都會增加開啟和關閉資料庫的負擔。
而且在每一個連接之後,啟用此選項也會排清程序快取。

對於桌面資料庫而言,啟用此選項非常有用,因為它可讓您將資料庫檔案當做一般檔案來管理。您可以移動它們、複製它們來建立備份,甚至可以用電子郵件將它們傳給其他使用者。
在舊版 SQL Server 中,AUTO_CLOSE 是同步處理序,當有重複建立和中斷與 Database Engine 之連接的應用程式存取資料庫時,該處理序可能會降低效能。從 SQL Server 2005 開始,AUTO_CLOSE 處理序是非同步的;重複開啟和關閉資料庫不再降低效能。
當啟用此選項時,sys.databases 目錄檢視中的某些資料行及 DATABASEPROPERTYEX 函數會傳回 NULL,因為資料庫無法擷取資料。若要解決這個問題,請執行 USE 陳述式來開啟資料庫。
此啟用使選項也會造成,當資料庫關閉的作業執行時,會自動清除 SQL Server 執行個體的計畫快取。
若清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。在 SQL Server 2005 Service Pack 2 中,針對每次清除計畫快取的快取存放區,SQL Server 錯誤記錄檔會包含下列參考訊息:「由於某些資料庫維護或重新設定作業,SQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清。」只要快取發生排清,這個訊息就會每五分鐘記錄一次。
OFF(False)當設定為:OFF 時,則在最後一個使用者結束之後資料庫仍為開啟。
資料庫鏡像需要 AUTO_CLOSE OFF。
預設值:
不論作業系統為何,當使用 SQL Server 2000 Desktop Engine 或 SQL Server Express 時,這個選項對所有資料庫是設為:ON(True);其他版本的 SQL Server 則是設為:OFF(False)。

最佳作法建議
如果經常存取資料庫,請將此資料庫的 AUTO_CLOSE 選項設定為 OFF。



啟用資料庫選項:AUTO_CLOSE的方式如下:
任務1:建立資料庫
步驟01. 執行以下範例程式碼:
USE [master]
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB01')
DROP DATABASE [DB01]
GO
CREATE DATABASE [DB01] ON  PRIMARY
( NAME = N'DB01', FILENAME = N'C:\DB01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DB01_log', FILENAME = N'C:\DB01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

任務2:啟用資料庫選項:AUTO_CLOSE
步驟01. 執行以下範例程式碼:
USE [master]
GO
-- 設定資料庫DB01的AUTO_CLOSE屬性為ON
ALTER DATABASE [DB01]
SET AUTO_CLOSE ON WITH NO_WAIT
GO

請參考圖1所示:



圖1:設定資料庫選項:AUTO_CLOSE

或是使用SSMS(SQL Server Management Studio)管理工具
步驟01. 執行SSMS管理工具,在「物件總管」,展開「資料庫」,點選指定的資料庫,滑鼠右鍵,選取:「屬性」。
步驟02. 在「資料庫屬性」視窗,輸入以下的選項:
在左邊「選取頁面」窗格,點選「選項」。
在右邊窗格,在「其他選項」區域,在「自動」區域下,在「自動關閉」方塊部分,下拉選取「True」。
步驟03. 點選「關閉」,完成設定。

任務3:測試資料庫選項:AUTO_CLOSE
步驟01. 執行以下範例程式碼數次後:
USE DB01
GO
USE master
GO
USE DB01
GO
USE tempdb
GO
USE DB01
GO
USE master
GO

任務4. 檢視「SQL Server 記錄檔」
步驟01. 執行SSMS管理工具,在「物件總管」,展開「管理」\「SQL Server 記錄檔」。
步驟02. 滑鼠雙擊,開啟最新的記錄檔。
步驟03. 在「記錄檔檢視器」,可以看到有數筆啟動此資料庫的訊息:
Starting up database 'DB01'.


請參考圖2所示:


圖2:訊息:啟動資料庫Starting up database

任務5. 直接複製此資料庫的資料檔案
步驟01. 使用檔案總管,在 C: 碟根目錄下,建立資料夾 C:\ArchiveDB。
步驟02. 使用檔案總管,在 C: 碟根目錄下,選取此資料庫的檔案:DB01.mdf 與 DB01_log.ldf。
步驟03. 複製此兩個檔案到 C:\ArchiveDB  資料夾內。

由上,將資料庫選項:AUTO_CLOSE設定為:ON,在確認關閉後,就可以執行複製檔案。也類似於將此資料庫設定為「離線(OFFLINE)」。

提醒您:
若資料庫選項:AUTO_CLOSE是設定為OFF(false)時,資料庫引擎將鎖定資料庫檔案,也無法複製或是刪除。

請參考圖3所示:


圖3:檔案已經開啟,無法複製或是刪除