[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

這樣我們就可以在 0227 的 10 點啟動 Trace，並擷取至 11 點的資料。

假如 Trace 搭配 sp_procoption 的話，就可以作到 SQL Server 啟動時，就馬上開啟 Trace 來進行側錄

• 20150209 補充

SQL Pass 課程 資料庫管理的基本功 - 備份、效能和故障排除 中，講師有提到可以啟動 Windows 目錄壓縮，來減少 Trace 的大小，方便把 Trace 寄出去進行分析

延伸閱讀
• [SQL] sp_procoption

參考資料
• 論壇問題討論
• Scheduling a SQL Server Profiler Trace

[Challenge] 反轉句子內的單字

Beyond Relational TSQL Beginners Challenge 15

This challenge is to reverse the order of words within a sentence. Most of us must be familiar with reversing the order of letters within a word.

This challenge invites you to take a sentence and reverse the order of words within it. For example, if the original sentence is "Hello! how are you?", the reversed version should be "you? are how Hello!".

• Sample Data

ID Sentence
-- -------------------------------------
1  This is T-SQL Beginners Challenge #15
2  I am a challenge competitor

• Expected Results

ID Original Sentence                     Reversed Sentence
-- ------------------------------------- -------------------------------------
1  This is T-SQL Beginners Challenge #15 #15 Challenge Beginners T-SQL is This
2  I am a challenge competitor           competitor challenge a am I

• Rules
1. ID should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of ID.

[SQL] 警示應用 - 監控使用者登錄失敗

建立警示來監控使用者登錄 SQL Server 情況，當使用者登錄失敗時會發生 18456 錯誤號碼，進而觸發警示發信給 DBA，此方法可以用來監控是否有人不斷地嘗試登錄 SQL Server。

• SSMS => 物件總管 => SQL Server Agent => 警示 => 新增警示。

• 新增警示 => 一般 => 輸入名稱（SQL 登錄失敗）、選擇類型（SQL Server 事件警示）、輸入錯誤號碼（18456）。

• 新增警示 =>回應 => 通知操作員，並勾選該操作員要用電子郵件通知。

• 新增警示 => 選項 => 勾選電子郵件讓錯誤訊息文字也會包含進 EMail 中。

• 故意利用 SA 帳號但不輸入密碼來登錄 SQL Server。

• 產生錯誤訊息，可以看見錯誤號碼為 18456。

• 收到 18456 登錄失敗的通知 mail

• 新增警示結束後再進入，會多出一個［歷程紀錄］選項，可以看到警示觸發、回應時間和觸發次數。

• 20151013 補充

該作法雖然可以在第一時間通知有不正常的登入，但發太多通知，常常會變成收垃圾信件的 fu

• 延伸閱讀
• [Win] SMTP 設定
• [SQL] Database Mail 設定
• [SQL] SQL Server Agent Mail
• 參考資料
• Understanding "login failed" (Error 18456) error messages in SQL Server 2005
• 如何解決以 Windows 驗證方式登入 SQL Server 時，出現錯誤代碼 18456 
• 疑難排解：使用者 'x' 的登入失敗
• FB Super SQL Server 討論
• MSSQLSERVER_18452、MSSQLSERVER_18456
• SQL Server | 是誰在敲打我窗 來自 NT AUTHORITY\SYSTEM 失敗登入

[Challenge] 找出從屬關係

Beyond Relational TSQL Beginners Challenge 9

This challenge refers to a real-world problem I came across recently. The requirement was to identify all 'direct' or 'indirect' parents of a given child node and all its siblings.

To give a better idea of the problem consider the following.

A(1)
      |
---------------------------------
|               |         |
B(2)            R(3)      C(4)
|                         |
--------------------      Child1OfC(9)
|               |
Child1OfB(5)    Child2OfB(6)
|
----------
|      |
D(7)   E(8) 

Given ChildId = 5 the Output will be A,B,R,C, Child1OfB, Child2OfB.Because A,B,R,C are all Parents of 5 i.e. Child1OfB in some way (direct or indirect) and Child2OfB is the adjacent of Child1OfB.However, the children of Child1OfB (if any is present) will not be consider as they are it’s children.

• Sample Data

ParentId ChildId Name
-------- ------- ------------------ 
NULL      1      Niladri Biswas
1         2      Piyush Ghosh
1         3      Agnish Basu
1         4      Deepak Kumar Goyal
2         5      Sachin Srivastav
2         6      Nishant Mandilwar
5         7      Arindam Pal
5         8      Mahi Sharma
3         9      Mahima Roy
3        10      Simran Motilal
9        11      Raj Malhotra
9        12      Sharmistha Roy
10       13      Preeti Sen
10       14      Holly Huggins

Given a @ChildId = 6 as Parameter,

• Expected Results

Id Name
--- -------------------------
1  Niladri Biswas
3     Agnish Basu
4     Deepak Kumar Goyal
2     Piyush Ghosh
6         Nishant Mandilwar
5         Sachin Srivastav

• Rules
1. The solution should work on SQL Server 2005 and above.
2. Column names should respect the desired output shown.
3. Output must be sorted in DESCENDING ORDER of Name.

[SQL] 鏡像備份

考量備份檔案存放安全性，可能會把備份檔案複製至其他儲存空間，避免發生災難時，備份檔案也一併毀壞，BACKUP T-SQL 語法提供 Mirror 語法，可以在進行備份時同時產生多份備份檔案。

• T-SQL 語法

BACLUP DATABASE (LOG)
TO [備份裝置] [ ,...n ]
MIRROR TO [備份裝置] [ ,...n ]
WITH [DIFFERENTIAL | 各項可用在 WITH 選項]

• 簡易範例

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup Mirror 1\AdventureWorks.bak'
MIRROR TO DISK =  'C:\Backup Mirror 2\AdventureWorks.bak'
WITH FORMAT

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup Mirror 1\AdventureWorks.bak'
MIRROR TO DISK =  'C:\Backup Mirror 2\AdventureWorks.bak'
WITH DIFFERENTIAL

BACKUP LOG AdventureWorks
TO DISK = 'C:\Backup Mirror 1\AdventureWorks.bak'
MIRROR TO DISK =  'C:\Backup Mirror 2\AdventureWorks.bak'

利用上述語法產生的備份檔

[SQL] 判斷連續資料

此論壇問題是希望可以針對每筆資料和它之後的兩筆資料進行加總（每三筆連續資料進行加總），萬一該筆資料後面沒有辦法湊成三筆資料的話，加總值以 NULL 表示。

T-SQL 解法

-- 解法 1
SELECT 
　T.ID ,
  T.Data ,
  IIF(Judge IS NULL , NULL , Cum3) AS Cum3
FROM
  (
    SELECT * ,
        LEAD(Data,2,NULL) OVER (ORDER BY ID) AS Judge , 
        SUM(Data) OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)  AS Cum3
    FROM @Temp
  ) AS T

-- 解法 2
SELECT 
  T.ID , 
  T.Data , 
  IIF(Judge = 3 , Cum3 , NULL) AS Cum3
FROM 
  (
    SELECT * ,
        SUM(1) OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS Judge ,
        SUM(Data) OVER (ORDER BY ID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)  AS Cum3
    FROM @Temp
  ) AS T