主页 / dba / 问题 / 260466
Accepted
Ali
Asked: 2020-02-25 11:46:31 +0800 CST

SQL Server 2000 数据库监控

  • 772

我从来没有在 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 秒运行一次？或者也许我应该使用另一种方法，例如通过触发器捕获带有所需信息的登录？

任何输入将不胜感激。

sql-server-2000 t-sql

1 个回答

  1. Best Answer

    我在网上找到了一个解决方案：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()

      CREATE TABLE [#sysproc] ( id INT IDENTITY(1, 1), [spid] [SMALLINT] NOT NULL, [dbname] NVARCHAR COLLATE sql_latin1_general_cp1_cs_as NULL, [cpu] [INT] NOT NULL, [memusage] [INT] NOT NULL , [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] NOT NULL 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

    • 0

相关问题