Showing posts from June, 2011

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.

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)


ALTER TABLE #tbl_spwho2 ADD ProcessInfo VARCHAR(4000)


SET @spid = ''


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'

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.