筆記 SQL Server 如何藉由設定作業排程,定時執行 sp_who2 並保存於資料庫,從而實現自動化記錄資料庫伺服器應用程式連線情形的情境。
說明
SP_WHO2
sp_who2 可以顯示出目前資料庫上的所有連線 Session,並包含所使用的登入名稱、連線資料庫、連線的裝置名稱以及所使用的連線方式。
exec 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