|
|
發表於 2021-4-11 23:38:48
|
查看: 2257 |
回復: 0
此服務由 FeedBurner提供
首頁
留言板
RSS
訂閱所有附加功能教學的網誌 訂閱 | 上一篇上一篇 | 返回 返回 | 下一篇下一篇
附加功能教學
SQL Server 資料庫維護
作者: SmartIT 日期: 2012-02-17 10:27
字體大小: 小 中 大
Share
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：
|
|
