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

Performance Tuning by creating index.

Lets take one stored procedure having 3 delete statements took 10 mins to complete execution.Customer asking to reduce the execution time.
Table size:2.5GB

As a DBA,What will we do?

1.First check for presence of index in the condidtional columns in the delete statements.

2.If no means,please create non-clustered index in the conditional coulmns in the tables available in the delete queries.

3.Then execute the stored procedure.

4.Now it will took 50seconds to finish execution.

5.Thats it.

Let us know for anyother ways?

How to resolve Error 22051?

Jobs failed with below error message.

Error Message:

Executed as user: XXXXXX-svc. Mail queued. [SQLSTATE 01000] (Message 0)  File attachment or query results size exceeds allowable value of 1000000 bytes. [SQLSTATE 42000] (Error 22051).  The step failed.

Resolution Steps:

Go to Database Mail under Management in Sql Server Management Studio -->Select Configuration Task as View or Change system parameters -->Give Maximum File Size (Bytes) as to 1.5 million

That’s it

Now mail sending commands in the job will execute successfully.

How to change monitor server for existing log shipping setup database in sql server?

Proceed below steps to change monitor server into new one in existing log shipping setup for a database.

First log into publisher database server and Right Click Publisher Database->Click Properties-

Select Transaction Log Shipping>Uncheck the Checkbox for ‘Enable this as a primary database in

a log shipping configuration.

Secondly Check the Checkbox for ‘Enable this as a primary database in a log shipping configuration

Now we can able to edit the Monitor server instance name settings.

Here we will change monitor server into a new monitor server name. 

Click OK.

That’s it.

Check log shipping working fine or not?

How to backup database into less size using LiteSpeed query?

Use below script to back up the database ‘kumartest’.

exec master.dbo.xp_backup_database @database = N'kumartest', @compressionlevel = 1, @filename = N'J:\SQLBackup\MSSQL\kumartest\kumartest_Full_201003041713.bak', @init = 0, @with = N'SKIP', @with = N'STATS = 10' GO

INITàspecifies that all backup sets should be overwritten, but preserves the media header. If INIT is specified, any existing backup set data on that device is overwritten.

SKIPàdisables the backup set expiration and name checking usually performed by the BACKUP statement to prevent overwrites of backup sets

STATS [= percentage] àDisplays a message each time another percentage completes, and is used to measure progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.

Hope we will get compressed backups.