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


Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)