SQL Server 2000 數據庫監控

我從來沒有在 SQL Server 2000 上工作過，我需要檢查將以下資訊擷取到一個特定的數據庫。

DBName CurrentDate SPID BatchDuration ApplicationName HostName LoginName SQLQuery

我嘗試了下面的查詢，但它首先沒有返回任何資訊或不滿足要求，因為它沒有擷取 ApplicationName 和 SQL 文本。

SELECT
   DB_NAME(dbid) as dbName
,   CURRENT_TIMESTAMP as CurrentDate
,   P.spid
,   right(convert(varchar, 
       dateadd(ms, datediff(ms, P.last_batch, getdate()), '2000-01-01'), 
       121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
,   P.cmd
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
               ,'MIRROR HANDLER'
               ,'LAZY WRITER'
               ,'CHECKPOINT SLEEP'
               ,'RA MANAGER')
and DB_NAME(dbid) ='master'
order by batch_duration desc

此外，當我嘗試通過 SQL 代理設置此腳本時，它只允許每隔一分鐘執行一次。有沒有其他更好的方法可以像每 5 秒執行一次？或者也許我應該使用另一種方​​法，例如通過觸發器擷取帶有所需資訊的登錄？

任何輸入將不勝感激。

我在網上找到了一個解決方案：http: //strictlysql.blogspot.com/2010/07/lock-monitoring-sql-server-2000.html。修改腳本以滿足我的要求。

1. 在我們的監控數據庫中創建了一個表：

CREATE TABLE MyDBMonitoring ( DatabaseName varchar(35), Time datetime, SPID INT, Query varchar(1000), QueryStatus VARCHAR(35), LoginName VARCHAR(50), HostName VARCHAR(50), LastBatch datetime, Command VARCHAR(50), ProgramName VARCHAR(100)，OpenTransactions INT

) 2. 從連結修改腳本，只選擇我需要的欄位，並通過在伺服器上設置 SQL 代理作業將結果插入監控表。兩天后將結果提供給客戶。

CREATE TABLE #sp_lock_details（spid INT、dbid INT、objid INT、indid INT、TYPE VARCHAR(100)、資源 VARCHAR(1000)、模式 VARCHAR(10)、狀態 VARCHAR(50)）

插入 #sp_lock_details 執行 Sp_lock

DECLARE @id INT DECLARE @max INT DECLARE @spid INT DECLARE @exec_qry VARCHAR(500) DECLARE @monitored_dt DATETIME

SET @monitored_dt = Getdate()

創建表

$$ #sysproc $$ ( id INT IDENTITY(1, 1), $$ spid $$ $$ SMALLINT $$非空， $$ dbname $$ NVARCHAR COLLATE sql_latin1_general_cp1_cs_as NULL， $$ cpu $$ $$ INT $$非空， $$ memusage $$ $$ INT $$非空， $$ status $$ NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL, $$ loginame $$ NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL, $$ hostname $$ NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL, $$ lastwaittype $$NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL, $$ last_batch $$ $$ DATETIME $$非空， $$ cmd $$ NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL, $$ program_name $$NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL, QUERY VARCHAR(500), open_tran INT ) ON$$ PRIMARY $$ CREATE TABLE #dbccinfo ( eventype VARCHAR(255), param INT, eventinfo VARCHAR(255) )

INSERT INTO #sysproc (spid, dbname, cpu, memusage, status, loginame, hostname, lastwaittype, last_batch, cmd, program_name, open_tran) SELECT spid, Db_name(MASTER.dbo.sysprocesses.dbid) AS dbname, cpu, memusage, status , loginame, hostname, lastwaittype, last_batch, cmd, program_name, open_tran FROM MASTER.dbo.sysprocesses WHERE spid > 50

設置@max = @@IDENTITY 設置@id = 1

WHILE (@id <= @max) BEGIN SELECT @spid = spid FROM #sysproc WHERE id = @id

 SET @exec_qry = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(3), @spid) + ')'

 INSERT INTO #dbccinfo
 EXEC (@exec_qry)

 UPDATE sys
 SET    QUERY = eventinfo
 FROM   #sysproc sys,
        #dbccinfo
 WHERE  @id = id

 TRUNCATE TABLE #dbccinfo

 SET @id = @id + 1

結尾

INSERT INTO dba.dbo.MyDBAMonitoring (DatabaseName, Time, SPID, Query, QueryStatus, LoginName, HostName, LastBatch, Command, ProgramName, OpenTransactions) SELECT dbname AS DatabaseName, CURRENT_TIMESTAMP AS ‘TIME’, #sysproc.spid AS SPID, QUERY, #sysproc.status AS query_status, loginame, hostname, last_batch, cmd, program_name, open_tran FROM #sysproc, #sp_lock_details WHERE #sp_lock_details.spid = #sysproc.spid and dbname = ‘ODS;

刪除表#dbccinfo 刪除表#sysproc 刪除表#sp_lock_details

引用自：https://dba.stackexchange.com/questions/260466