SQL Server 2000 Database Monitoring

Question

I have never worked on SQL Server 2000 and I got a requirement to check capture the following information to one particular database.

DBName CurrentDate SPID BatchDuration ApplicationName HostName LoginName SQLQuery

I tried the query below but it in first place did not return any information or does not meet the requirement as it does not capture the ApplicationName and SQL Text.

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

Also when I try to setup this script via SQL Agent it only allows to run it every one minutes interval. Is there any other better way to run it like every 5 seconds? or maybe I should be using another method instead e.g. capturing the logins with required information via trigger instead?

Any input will be appreciated.

Answer 1

I found a solution online: http://strictlysql.blogspot.com/2010/07/lock-monitoring-sql-server-2000.html. Modified the script to fulfill my requirement.

1. Created a table in our monitoring database:

   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. Modified the script from the link and only selected the fields I need and insert the result into the monitoring table by setting up a SQL Agent Job on the Server. Will provide the result to client in two days.

   CREATE TABLE #sp_lock_details ( spid INT, dbid INT, objid INT, indid INT, TYPE VARCHAR(100), resource VARCHAR(1000), mode VARCHAR(10), status VARCHAR(50) )

   INSERT INTO #sp_lock_details EXEC 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, [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

   SET @max = @@IDENTITY SET @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
   

   END

   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;

   DROP TABLE #dbccinfo DROP TABLE #sysproc DROP TABLE #sp_lock_details