SQL Server 2000 Database Monitoring 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 &gt; 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.
I found a solution online: http://strictlysql.blogspot.com/2010/07/lock-monitoring-sql-server-2000.html. Modified the script to fulfill my requirement.


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
  )
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 &lt;= @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