[SQL][問題處理]設定長時間的 SQL Trace,避免因為關機和重新啟動造成中斷

[SQL][問題處理]設定長時間的 SQL Trace,避免因為關機和重新啟動造成中斷

最近在協助客戶處理一些效能的問題,想要利用 SQL Trace 去做側錄,再來針對一些特別時段和特定作業來做系統的調整,因此如同一些網路上相關的文章所介紹的方法一樣,先利用 SQL Profile 設定好追蹤屬性。

image

 

接下來再利用「匯出」→「指令碼追蹤定義」→「對於 SQL Server 2005 – SQL11」,將設定匯出成為 SQL Trace 的設定指令。

image

 

接下來再針對匯出檔案修改檔案 Size , 檔案路徑和名稱 , 以及資料檔產生的規則等設定後就可以使用了。但在這次所遇到的案例中,因此會有些異常狀況發生,導致 SQL Server 或者是作業系統重新啟動,造成所捨定的 SQL Trace 就中斷了,因此為了解決這樣的問題,我們先把原本的設定指令做一點加工。首先我們在處理段的前後加上 「CREATE PROCEDURE AutoTrace AS BEGIN」,中間我們稍微調整一下,讓檔案名稱可以隨的不同啟動時間而指定不同的檔案名稱。

image

 

並且把最後面本來接在 finish: 後面的 GO 給換成「END」,也就是說把整個設定處理變成一個 Stored Procedure 來使用。

image

 

接下來我們透過一個 SQL Server 所提供的系統預存程序 「sp_procoption」來設定,指定剛剛所建立的預存程序 AutoTrace 會在啟動的時候自動執行

EXEC sp_procoption AutoTrace, 'startup' , 'on'

 

如果要查看有哪些預存程序是有設定啟動時自動執行的話,則可以使用已下指令查詢,可以看到 AutoTrace 這個預存程序已經設定為啟動時自動執行。

SELECT name, type_desc, is_auto_executed, create_date, modify_date
FROM sys.procedures

image

 

如果要關閉的話,則可以透過使用系統預存程序 「sp_procoption」,將最後一個參數改為「off」,或者是說更暴力一點,直接將預存程序給刪除,這樣 SQL 啟動的時候就不會自動執行了。

EXEC sp_procoption AutoTrace, 'startup' , 'off'

 

因此如果有需要設定長時間的 SQL Trace 的朋友,可以參考這樣的方式來做設定,當然可能衍生的問題就是會有檔案很多的狀況,針對這樣的狀況,一般我是指定設定檔案目錄為壓縮,這樣會占用比較小的儲存空間,以上是個人一點使用上的經驗,提供給大家參考看看囉。