2012年2月18日 星期六

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:
建議設定時間分開執行,為避免同時執行排程造成資料庫異常,
必需將每個排程時間都隔開。

0 意見:

張貼留言

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Web Hosting Bluehost