~楓花雪岳~: [SQL] 設定 Trace 啟動時間

星期五, 3月 29, 2013

[SQL] 設定 Trace 啟動時間

SQL Profile 是一個耗能工具，通常我們會希望只針對某段時間或特定事件進行 Trace 就好，Trace 的設定只可以設定停止時間，沒有啟動時間，總不可能把人綁在電腦前面，只為啟動 Trace 截取資料，因此利用 SQL Profile 匯出功能的指令碼追蹤定義，把 Trace 匯出 Script，並包裝在 Store Procedure 內，再設定為 Job 交給 SQL Server Agent 來負責啟動。

Trace 只能設定停止時間，沒有啟動時間

SQL Profile 匯出功能 - 指令碼追蹤定義

匯出的 T-SQL Script 內，不會有 Trace 檔案的存放位置（InsertFileNameHere，EX：C:\Demo），這個部分必須再進行修改

CREATE PROCEDURE uspTraceStart
AS
  BEGIN

    // Create a Queue
    declare @rc int
    declare @TraceID int
    declare @maxfilesize bigint
    declare @DateTime datetime

    set @DateTime = '2013-02-27 11:00:00.000'
    set @maxfilesize = 100

    // Please replace the text InsertFileNameHere, with an appropriate
    // filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
    // will be appended to the filename automatically. If you are writing from
    // remote server to local drive, please use UNC path and make sure server has
    // write access to your network share

    exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @Datetime
    if (@rc = 0)
      BEGIN
        -- Client side File and Table cannot be scripted

        -- Set the events
        declare @on bit
        set @on = 1
        exec sp_trace_setevent @TraceID, 10, 1, @on
        exec sp_trace_setevent @TraceID, 10, 3, @on
        exec sp_trace_setevent @TraceID, 10, 11, @on
        exec sp_trace_setevent @TraceID, 10, 35, @on
        exec sp_trace_setevent @TraceID, 10, 12, @on
        exec sp_trace_setevent @TraceID, 10, 13, @on
        exec sp_trace_setevent @TraceID, 45, 1, @on
        exec sp_trace_setevent @TraceID, 45, 3, @on
        exec sp_trace_setevent @TraceID, 45, 11, @on
        exec sp_trace_setevent @TraceID, 45, 35, @on
        exec sp_trace_setevent @TraceID, 45, 12, @on
        exec sp_trace_setevent @TraceID, 45, 28, @on
        exec sp_trace_setevent @TraceID, 45, 13, @on
        exec sp_trace_setevent @TraceID, 12, 1, @on
        exec sp_trace_setevent @TraceID, 12, 3, @on
        exec sp_trace_setevent @TraceID, 12, 11, @on
        exec sp_trace_setevent @TraceID, 12, 35, @on
        exec sp_trace_setevent @TraceID, 12, 12, @on
        exec sp_trace_setevent @TraceID, 12, 13, @on

        // Set the Filters
        declare @intfilter int
        declare @bigintfilter bigint

        // Set the trace status to start
        exec sp_trace_setstatus @TraceID, 1

      END

  END

在 SQL Server Agent 內建立一個 Job 並設定排程，來執行上述的 Store Procedure