SQL Server 資料庫維護

SQL Server 資料庫維護

本文件針對SQL Server 2005上的SmartIT資料庫作效能改進，與資料庫整理時可參考使用。

介紹說明：

一、DB Backup 「單一備份」
二、DB Backup 「每日備份至一週」
三、DB reindex 「資料庫索引重建」
四、資料庫壓縮
五、資料庫修復
六、快速查詢資料庫資料表筆數
七、清除交易紀錄檔
八、操作流程建議
九、排程設定圖解

註：文中所有之DB_Name，意指SmartIT之資料庫名稱，標記紅色字體為可變更。

一、DB Backup 「單一備份」

-----------------------------------------DB Backup-----------------------------------------------

BACKUP DATABASE [DB_Name]

TO DISK = N'D:\SmartIT_DB_BAK\smartit_bk.bak' WITH INIT,NOUNLOAD,
NAME = N'smartit_bk',NOSKIP,STATS = 10,NOFORMAT

----------------------------------------- DB Backup-----------------------------------------------

*DB_Name，意指SmartIT資料庫名稱
*D:\SmartIT_DB_BAK\smartit_bk.bak，意指備份路徑及備份檔案名稱

建議設定方式：
資料庫備份建議為一周一次，如果資料異動不多，

可設定一個月一次，在週六或週日執行比較不影響效能。

========================================================== 

二、DB Backup 「每日備份至一週」

說明：

使用下列指令可以備份資料庫，只備份七個檔案不會累加，檔名為週一到週日，

會做完整備份。

-------------------------- DB Backup 週備份-----------------------------
DECLARE @bkFileName nvarchar(255)
    SET DATEFIRST 1
    SELECT @bkFileName =
     CASE  DATEPART(dw, GETDATE())
       WHEN 1 THEN N'D:\星期一.bak'
       WHEN 2 THEN N'D:\星期二.bak'
       WHEN 3 THEN N'D:\星期三.bak'
       WHEN 4 THEN N'D:\星期四.bak'
       WHEN 5 THEN N'D:\星期五.bak'
       WHEN 6 THEN N'D:\星期六.bak'
       WHEN 7 THEN N'D:\星期日.bak'
     END
BACKUP DATABASE [DB_Name] TO DISK = @bkFileName WITH INIT,NOUNLOAD,NAME=N'SMARTIT 備份',NOSKIP,STATS=10,NOFORMAT
-------------------------- DB Backup 週備份-----------------------------

*DB_Name，意指SmartIT資料庫名稱
*D:\星期一.bak，意指備份路徑及備份檔案

建議操作設定方式：

使用此備份可設定每天都做備份，但是每次備份為full Backup，且不是累加方式備份，

只會產生7個檔案，週一到週日，如果硬碟夠大的話可以用來做這樣的備份。

==========================================================

三、DB reindex 「資料庫索引重建」

說明：

重建索引的目的在於：當使用者需透過該欄位，

從資料檔(或表格)搜尋所要的記錄(或行)時，借索引之助，可大幅提高資料檢索的速度。

------------------------ DBREINDEX ALL TABLE -----------------------------
DECLARE @TableName nvarchar(261)
DECLARE @SQLStatement nvarchar(4000)
DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY  FOR
SELECT
    QUOTENAME(TABLE_SCHEMA) +
    N'.' +
    QUOTENAME(TABLE_NAME)
FROM
    INFORMATION_SCHEMA.TABLES
WHERE EXISTS
    (
    SELECT *
    FROM sysindexes
    WHERE id =
        OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
        N'.' +
        QUOTENAME(TABLE_NAME)) AND
        indid IN(0,1)
    )
OPEN TableList
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM TableList INTO @TableName
    IF @@FETCH_STATUS = -1 BREAK
    RAISERROR ('Reindexing %s', 0, 1, @TableName) WITH NOWAIT
    Print(@TableName)
    DBCC DBREINDEX (@TableName, '', 70)
    EXEC(@SQLStatement)
END
CLOSE TableList
DEALLOCATE TableList
------------------------ DBREINDEX ALL TABLE -------------------------- 

建議操作設定方式：

此設定建議在清除交易紀錄檔之前執行，

於每天晚上22:00執行，因執行後會有交易紀錄。

========================================================== 

四、資料庫壓縮

說明：

執行此設定會縮減 User DB 使用者資料庫的檔案大小，

讓User DB 的檔案騰出 10% 的可用空間。

--------------------------資料庫壓縮--------------------------

DBCC SHRINKDATABASE (DB_Name, 10)

--------------------------資料庫壓縮--------------------------

*DB_Name，意指SmartIT資料庫名稱

建議操作設定方式：

資料庫壓縮可設定手動執行，如有清除資料後再作此壓縮比較適合。

==========================================================

五、資料庫修復

說明：

使用此script的時機，主要在於當我們在使用報表時，

在擷取資料時如果出現『逾時過期』訊息時，建議使用下列指令修復資料庫，

特別注意第一行與最後一行『sp_dboption DB_Name,single, true』

『sp_dboption DB_Name,single, false』與中間指令要分開執行。

Step1:

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

sp_dboption DB_Name, single , true

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

**DB_Name，意指SmartIT資料庫名稱
**如執行時發生失敗，請重新啟動SQL Server Service後，再試一次

Step2:

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

dbcc checktable ("Table_Name",repair_allow_data_loss)
dbcc dbreindex ("Table_Name")

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

**Table_Name，意指SmartIT資料表名稱

Step3:

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

sp_dboption DB_Name, single , false

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

**DB_Name，意指SmartIT資料庫名稱

下列為可能需要修復的資料表對照表:

Table Name / 資料名稱

organization_group    組織
users                      人員
smartit                    SmartIT主表（電腦清單）
software                  軟體
virtual_software        軟體虛擬群組
virtual_software_mapping    虛擬群組對應
software_change       軟體變更
system_change         系統變更
appusage                 軟體使用率
url_audit                  網站稽核
print_log                  印表機稽核
imlog                       即時通訊稽核
files_audit_log          檔案抄寫稽核

**此段設定可針對有問題部分執行即可，不需全部執行。

建議操作設定方式：

資料庫修復使用時機適合在某資料表過大造成報表無法產生時執行，

執行時會將資料庫離線，建議在非資產回傳尖峰時間執行，

且須停止SmartIT Server服務，以加快執行速度。

========================================================== 

六、快速查詢資料庫資料表筆數

說明：

使用下列指令可以快速查詢資料表的大小，

藉此方式可以判斷是否有哪些資料表資料量過大，提供維護人員或管理人員參考使用。

-----------------------快速查詢資料庫資料表筆數-------------------------
select a.name,max(b.rows) as rows
from sysobjects a,sysindexes b where a.id= b.id and a.xtype = 'U'
group by a.name
order by max(b.rows)
-----------------------快速查詢資料庫資料表筆數-------------------------

建議操作設定方式：

使用此Script可搭配資料庫修復使用，目的在於先檢測資料庫每個資料表的資料量，

藉此判斷是否需要做資料庫修復。

========================================================== 

七、清除交易紀錄檔

說明：

交易紀錄檔的產生，是每個與資料庫連結的動作之紀錄，

若SQL Server的交易紀錄一直沒有去清除的話，會導致整個資料庫都不能使用。

指令說明：

執行Backup Log with Truncate_Only 其用法是備份資料庫的Log檔，

由於我們在語法中沒有指定備份的裝置為何，

Sql Server即會認為此動作為單純要把已交易完成的Log資料清空

(已交易完成的資料所指即為已commit的資料)，

而Truncate_Only的選項則是告訴Sql Server目的在清空Log之空間，

至於清出的空間則由Sql Server管理，不還給OS。

Backup Log完成後，要再執行DBCC SHRINKFILE ,其作為為將資料的空間作重整及壓縮至2M。

----------------------------清除交易紀錄檔--------------------------
use DB_Name
Backup Log DB_Name with TRUNCATE_ONLY
dbcc shrinkfile (DB_Name _log , 2)
----------------------------清除交易紀錄檔--------------------------

 *DB_Name，意指SmartIT資料庫名稱

--------清除DB Name為SmartIT的交易紀錄檔範例--------
use SmartIT
Backup Log SmartIT with TRUNCATE_ONLY
dbcc shrinkfile (SmartIT_log , 2)
--------清除DB Name為SmartIT的交易紀錄檔範例--------

建議操作設定方式：

建議交易紀錄檔可設定每天晚上23:30執行，交易紀錄檔越大，存取資料的速度也會越慢。

========================================================== 

八、操作流程建議：

Mdf 過大，資料量過多

順序為

1、資料表查詢 <確認資料表資料大小>

2、詢問user是否可以先進行瘦身(記錄檔維護)

3、清除交易紀錄檔

4、壓縮資料庫

5、db reindex 

6、備份

Ldf 過大，交易紀錄檔太大

順序為

1、清除交易紀錄檔

2、資料表查詢

3、詢問user是否可以先進行瘦身(記錄檔維護)

4、清除交易紀錄檔

5、壓縮

6、db reindex 

7、備份

========================================================== 

九、SQL Server 2005排程設定圖解說明

Step1：開啟資料庫程式。

Step2：選擇使用可設定排程的權限登入，並確認連結的資料庫是否正確。

Step3：開啟「SQL Server Agent」的作業，在右手邊空白處點選(滑鼠右鍵)，選擇新增作業。

Step4：設定排程作業的名稱，本範例 以重建索引設定來說明。

Step5：選擇「步驟」，在按下「新增」，建立一個指令執行步驟。

Step6：開啟頁面後，先設定步驟名稱、選擇資料庫、在將指令貼到命令區塊中，設定完成後按確定儲存。

Step7：選擇「排程」，在按下「新增」，建立一個執行步驟。

Step8：設定排程名稱，要執行的天數，與時間，最後再按下「確定」回到設定畫面。

Step9：設定完成後可在排程清單中看到所有設定項目。

Step10：

建議設定時間分開執行，為避免同時執行排程造成資料庫異常，

必需將每個排程時間都隔開。

Posted in: 知識分享, SQLServer