SmartIT QA集
作者:SmartIT 日期:2012-02-17 15:19
└Client 安裝Agent 的權限問題
└設定排程清除SQL 2008資料庫的交易紀錄檔
└SQL Server 資料庫維護 new!
└SQL Server Express資料庫維護 new!
SQL Server Express資料庫維護
作者: 日期:2012-02-17 15:17
SQL Server Express資料庫維護
由於Microsoft SQL Server Express 版本無法使用SQL Agent 排程，且SQL 2005 Server 會有4G 容量上限的限制，因此必須使用手動方式整理資料庫，或是使用此排程來操作，此設定需搭配Windows 排程來執行，當然也可以手動執行。
介紹說明：
一、將批次檔寫好放在固定位置不會被刪除六、排程2 「將歷史記錄移到其他資料庫」
一、將批次檔寫好放在固定位置不會被刪除
批次檔案內容說明如下
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
@echo off
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" ‐S 127.0.0.1 ‐Usa ‐Pkeepout ‐i
D:\backup_full.sql ‐o D:\backup_full.log
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
(1) 先檢查 sqlcmd.exe 程式放在哪個位置
(2) 再將程式路徑複製出來，如果路徑中有空格，需要在前後加雙引號。
(3) –S 後面請加上SQL Server 的IP 位置
(4) –U 後面請加上SQL Server 的帳號。
(5) –P 後面請加上SQL Server 該帳號的密碼。
(6) –i 後面為要執行的SQL Script 語法與存放路徑。
(7) –o 後面為執行後所產生的log 放置的地方與檔名。
*批次檔範例下載
backup.zip (248 Byte ,下載:5次)
==========================================================
二、將要執行的SQL Script 放置與批次檔同一個資料夾中，並確認是否正確
註：文中所有之DB_Name，意指SmartIT之資料庫名稱，標記紅色字體為可變更。
說明：
將需要執行的SQL Script 放入與批次檔同一個資料夾中，方便管理與維護。
每一個SQL Script 都建一個批次檔來執行，目的是希望每個排程都按照指定時間執行，並且不是每個排程都需要每天做，所以分開執行最好。
這裡建議幾個常用的Script，提供參考。
1、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，意指備份路徑及備份檔案名稱
=
2、清除交易紀錄檔
-----------------------------------------清除交易紀錄檔-----------------------------------------
use DB_Name
Backup Log DB_Name with TRUNCATE_ONLY
dbcc shrinkfile ( DB_Name_log , 2)
-----------------------------------------清除交易紀錄檔-----------------------------------------
=
3、資料庫壓縮
-----------------------------------------資料庫壓縮-----------------------------------------
DBCC SHRINKDATABASE ( DB_Name, 10)
-----------------------------------------資料庫壓縮-----------------------------------------
=
4、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，意指備份路徑及備份檔案
==========================================================
三、 設定Windows 排程，執行批次檔
設定Windows 排程希望能由系統每天自動處理，減少人工操作部分
(1)、開啟Windows 排程，選擇[新增排定的工作]啟動視窗後再選擇[下一步]
-
(2)、選擇要執行的程式後，點選[下一步]
-
(3)、選擇批次檔的名稱，在按下[開啟]
-
(4)確認排程名稱後，選擇執行方式，再按[下一步]
-
(5)確認執行時間後，在點選[下一步]
-
(6)輸入擁有可執行權限的帳號密碼後，點選[下一步]
-
(7)完成設定，點選[完成]按鈕
-
(8)設定完成後，開啟排程，並將設定頁面中，在?小時後停止排成的選項，取消勾選
完成後點選套用結束畫面
==========================================================
四、 驗證排程是否可正常執行
完成排成設定後可按滑鼠右鍵，選擇[執行]來啟動排程，確認排程是否可執行完畢。
==========================================================
五、排程1 「超過三十天的紀錄移轉至歷史紀錄」
-----------------------------------------移轉排程-----------------------------------------------
-----------------------------------------移轉排程-----------------------------------------------
建議設定方式：
資料庫移轉排程通常為開啟多項稽核時進行設定。大量時則可設定一個月一次，在週六或週日執行比較不影響效能。
==========================================================
六、排程２ 「將歷史紀錄移到其他資料庫」
說明：
使用下列指令可以將歷史紀錄移轉至其他資料庫做資料存放。
-------------------------- DB 資料移轉-----------------------------
/* 1.網站稽核 */
DELETE FROM Backup_DB_Name.dbo.url_audit_history --目的DB
WHERE ( Backup_DB_Name.dbo.url_audit_history.url_audit_history_key IN --目的DB比對值
(SELECT DB_Name.dbo.url_audit_history.url_audit_history_key --來源DB比對值
FROM DB_Name.dbo.url_audit_history )) --來源DB
INSERT INTO Backup_DB_Name.dbo.url_audit_history --目的DB
SELECT * FROM Backup_DB_Name.dbo.url_audit_history --來源DB
DELETE FROM Backup_DB_Name.dbo.url_audit_history --來源DB
/* 2.應用程式使用率 */
DELETE FROM Backup_DB_Name.dbo.appusage_history --目的DB
WHERE ( Backup_DB_Name.dbo.appusage_history.serial IN --目的DB比對值
(SELECT DB_Name.dbo.appusage_history.serial --來源DB比對值
FROM DB_Name.dbo.appusage_history)) --來源DB
INSERT INTO Backup_DB_Name .dbo.appusage_history --目的DB
SELECT * FROM DB_Name .dbo.appusage_history --來源DB
DELETE FROM DB_Name .dbo.appusage_history --來源DB
/* 3.印表機列印記錄 */
DELETE FROM Backup_DB_Name.dbo.print_log_history --目的DB
WHERE ( Backup_DB_Name.dbo.print_log_history.print_log_history_key IN --目的DB比對值
(SELECT DB_Name.dbo.print_log_history.print_log_history_key --來源DB比對值
FROM DB_Name.dbo.print_log_history )) --來源DB
INSERT INTO Backup_DB_Name.dbo.print_log_history --目的DB
SELECT * FROM DB_Name.dbo.print_log_history --來源DB
DELETE FROM DB_Name.dbo.print_log_history --來源DB
/* 4.軟體變更 */
DELETE FROM Backup_DB_Name.dbo.software_change_history --目的DB
WHERE ( Backup_DB_Name.dbo.software_change_history.software_change_history_key IN --目的DB比對值
(SELECT DB_Name.dbo.software_change_history.software_change_history_key --來源DB比對值
FROM DB_Name.dbo.software_change_history)) --來源DB
INSERT INTO Backup_DB_Name.dbo.software_change_history --目的DB
SELECT * FROM DB_Name.dbo.software_change_history --來源DB
DELETE FROM DB_Name.dbo.software_change_history --來源DB
/* 5.系統變更 */
DELETE FROM Backup_DB_Name.dbo.system_change_history --目的DB
WHERE ( Backup_DB_Name.dbo.system_change_history.system_change_history_key IN --目的DB比對值
(SELECT DB_Name.dbo.system_change_history.system_change_history_key --來源DB比對值
FROM DB_Name.dbo.system_change_history)) --來源DB
INSERT INTO Backup_DB_Name.dbo.system_change_history --目的DB
SELECT * FROM DB_Name.dbo.system_change_history --來源DB
DELETE FROM DB_Name.dbo.system_change_history --來源DB
/* 6.管理端稽核記錄 */
DELETE FROM Backup_DB_Name.dbo.manage_change_log_history --目的DB
WHERE ( Backup_DB_Name.dbo.manage_change_log_history.manage_change_log_history_key IN --目的DB比對值
(SELECT DB_Name.dbo.manage_change_log_history.manage_change_log_history_key --來源DB比對值
FROM DB_Name.dbo.manage_change_log_history)) --來源DB
INSERT INTO Backup_DB_Name.dbo.manage_change_log_history --目的DB
SELECT * FROM DB_Name.dbo.manage_change_log_history --來源DB
DELETE FROM DB_Name.dbo.manage_change_log_history --來源DB
/* 7.即時通訊稽核 */
DELETE FROM Backup_DB_Name.dbo.imlog_history --目的DB
WHERE ( Backup_DB_Name.dbo.imlog_history.serial IN --目的DB比對值
(SELECT DB_Name.dbo.imlog_history.serial --來源DB比對值
FROM DB_Name.dbo.imlog_history )) --來源DB
INSERT INTO Backup_DB_Name.dbo.imlog_history --目的DB
SELECT * FROM DB_Name.dbo.imlog_history --來源DB
DELETE FROM DB_Name.dbo.imlog_history --來源DB
/* 8.USB檔案稽核 */
DELETE FROM Backup_DB_Name.dbo.files_audit_log_history --目的DB
WHERE ( Backup_DB_Name.dbo.files_audit_log_history.files_audit_log_history_key IN --目的DB比對值
(SELECT DB_Name.dbo.files_audit_log_history.files_audit_log_history_key --來源DB比對值
FROM DB_Name.dbo.files_audit_log_history )) --來源DB
INSERT INTO Backup_DB_Name.dbo.files_audit_log_history --目的DB
SELECT * FROM DB_Name.dbo.files_audit_log_history --來源DB
DELETE FROM DB_Name.dbo.files_audit_log_history --來源DB
-------------------------- DB 資料移轉-----------------------------
*DB_Name，意指SmartIT來源資料庫名稱
*Backup_DB_Name，意指備份用的目的地資料庫名稱
==========================================================
SQL Server 資料庫維護
作者:SmartIT 日期:2012-02-17 10:27
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：
建議設定時間分開執行，為避免同時執行排程造成資料庫異常，
必需將每個排程時間都隔開。
簡易設定排程清除SQL 2008資料庫交易紀錄檔
作者:SmartIT 日期:2011-04-25 16:30
部署Agent 遇到 Runas Fail
作者:SmartIT 日期:2011-03-01 15:59
管理者在部署SmartIT Agent時，經常會使用 share folder 或 直接在本機部屬的方式。
近日遇到一些使用者在 使用 share folder 會發現Runas Fail的情況。
部署狀況如下：
Agent 在 share folder 上 – 怎麼 RunAs 都 Runas Fail
安裝路徑:\\10.1.11.111\Install\ITSetup.exe
Agent Log 顯示為:
2011/2/25 下午 04:30:58 [ITSetup]:Change process owner to "maypc" fail －部署結果：失敗
但如果部署方式為:
把Share Folder 的 Agent 安裝檔複製到 C:\Install，執行 C:\Install\ITSetup.exe 則 RunAs Success !!.
Agent Log 顯示:
2011/2/25 下午 04:30:58 [ITSetup]:Change process owner to "maypc" success
並且 Agent 順利安裝成功。而 10.1.11.111 這台電腦有加入網域 －部署結果：成功
【狀況解說】
同樣的程式只因為放在不同的路徑有不一樣的結果。
原因是為 Agent Runas 的帳號為 Local Account，而非 Domain Account
Local Account不具有 Domain 環境下的存取權問題，所以才會有 Runas 不成功的狀況
【解決方式】
將 \\10.1.11.111\Install 的 Share folder 加入 Guest 帳號，
※ Guest 帳號預設是關閉的，需要到【電腦->管理->本機使用者或群組】手動做開啟
則可解決此問題。
