Monitoring Database Activity by Job

How can we find the process happening to a particular database in SQL Server for a specific period of time?

1. Go to SQL SERVER AGENT ->JOBàNew JobàJob name as ‘Monitoring database activity’

2. Give owner as ‘sa’ or those who have permission to run job.

3. Then go to steps->Enter step name as ‘Monitoring database name’àSelect Type as ‘Transact SQL Script’ àSelect database as ‘Master’àCopy the commands available in the below box into Command square box in the job step.

 

DECLARE @spid VARCHAR(4) CREATE TABLE #tbl_spwho2 (SPID VARCHAR(4),Status VARCHAR(30),Login VARCHAR(50),HostName VARCHAR(50),BlkBy VARCHAR(4),DBName VARCHAR(30),Command VARCHAR(100),CPUTime VARCHAR(15),DiskIO VARCHAR(15),LastBatch VARCHAR(50),ProgramName VARCHAR(100),SPID1 VARCHAR(4),RequestID INT) INSERT INTO #tbl_spwho2 EXEC SP_WHO2 ALTER TABLE #tbl_spwho2 ADD ProcessInfo VARCHAR(4000) DECLARE SELECT_SPID CURSOR FOR SELECT SPID FROM #tbl_spwho2 SET @spid = '' OPEN SELECT_SPID FETCH NEXT FROM SELECT_SPID INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #tbl_spid_info (EventType VARCHAR(50),Parameters VARCHAR(30),EventInfo VARCHAR(4000)) INSERT INTO #tbl_spid_info (EventType,Parameters,EventInfo) EXEC('DBCC INPUTBUFFER(' + @spid + ')') UPDATE #tbl_spwho2 SET ProcessInfo = B.EventInfo FROM #tbl_spwho2 A,#tbl_spid_info B WHERE A.Spid = @spid DROP TABLE #tbl_spid_info FETCH NEXT FROM SELECT_SPID INTO @spid END CLOSE SELECT_SPID DEALLOCATE SELECT_SPID --Input the specified database name by replacing XXX in the below query. INSERT INTO tbl_spwho2 SELECT * FROM #tbl_spwho2 (NOLOCK) WHERE DBName = 'XXXXXX' --SELECT * FROM tbl_spwho2 DROP TABLE #tbl_spwho2

4. Then schedule the job as customer need like every 5 minutes to monitor the specific database activity.

That’s’ it.

Note: if customer needs to track database activity between Mondays to Friday means, Schedule the job in weekdays.Then we will get information using below query.

SELECT * FROM tbl_spwho2