SQL Server 利用 sp_who2 來記錄資料庫伺服器的應用程式連線情形

2021-05-06

筆記 SQL Server 如何藉由設定作業排程，定時執行 sp_who2 並保存於資料庫，從而實現自動化記錄資料庫伺服器應用程式連線情形的情境。

SQL Server Logo

說明

SP_WHO2

sp_who2 可以顯示出目前資料庫上的所有連線 Session，並包含所使用的登入名稱、連線資料庫、連線的裝置名稱以及所使用的連線方式。

exec sp_who2

sp_who2 回傳的結果

Temp Table

而因為 sp_who2 是預存程序，其回應的結果無法直接使用，必須要藉由 T-SQL 建立 temp Table 的方式來使用。

DECLARE @Table TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

-- 將 sp_who2 寫入 Temp Table
INSERT INTO @Table EXEC sp_who2

SELECT * FROM @Table

Filter & Insert Into

為了要將 sp_who2 的結果永遠保存，可以建立一個資料庫與資料表，其中資料表的 schema 如下：

CREATE TABLE [dbo].[LoginProcess](
    [SPID] [int] NULL,
    [Status] [varchar](max) NULL,
    [LOGIN] [varchar](max) NULL,
    [HostName] [varchar](max) NULL,
    [BlkBy] [varchar](max) NULL,
    [DBName] [varchar](max) NULL,
    [Command] [varchar](max) NULL,
    [CPUTime] [int] NULL,
    [DiskIO] [int] NULL,
    [LastBatch] [varchar](max) NULL,
    [ProgramName] [varchar](max) NULL,
    [SPID_1] [int] NULL,
    [REQUESTID] [int] NULL,
    [InsertDateTime] [datetime] NULL -- 這個欄位為紀錄擷取時間所使用
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[LoginProcess] 
  ADD CONSTRAINT [DF_LoginProcess_InsertDateTime] 
  DEFAULT (getdate()) FOR [InsertDateTime]
GO

另外結合原本的 Temp Table 擴充 Script，並存為 Stored Procedures

CREATE PROCEDURE [dbo].[sp_execSpWho2]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Table TABLE(
            SPID INT,
            Status VARCHAR(MAX),
            LOGIN VARCHAR(MAX),
            HostName VARCHAR(MAX),
            BlkBy VARCHAR(MAX),
            DBName VARCHAR(MAX),
            Command VARCHAR(MAX),
            CPUTime INT,
            DiskIO INT,
            LastBatch VARCHAR(MAX),
            ProgramName VARCHAR(MAX),
            SPID_1 INT,
            REQUESTID INT
    )

    INSERT INTO @Table EXEC sp_who2

    INSERT INTO LoginProcess
    SELECT  *, GETDATE() -- 作為 InsertDataTime 的值
    FROM    @Table
    WHERE Login != 'sa'  and DBname not in ('master', 'msdb') -- 過濾不需要的資料列
END
GO

Job Run Looply

最後再藉由 SQL Server Agent 的設定一個 Job ，來間隔特定時間重複執行 Stored Prcoedures，就完成本次的自動記錄設定！

Aggregation Data

SELECT d.name AS dbname,
       ISNULL(lp.last, TRY_CONVERT(DATETIME, '2000-01-01')) AS last,
       ISNULL(lp.count, 0) AS count
FROM sys.databases d
LEFT JOIN (
    SELECT dbname, 
           MAX(InsertDateTime) AS last, 
           COUNT(*) AS count
    FROM [dbo].[LoginProcess]
	WHERE login not in ('NT AUTHORITY\SYSTEM')
    GROUP BY dbname
) lp ON d.name = lp.dbname

DBA Automate

⭐Table Rows Monitor

⭐SP_WHO2

#Database #SQLServer #DBA