automate profiler trace in sql server 2005

Create the Trace Definition

The most efficient means to define the SQL commands used for constructing a profiler trace is to use SQL Profiler.

* Start SQL Profiler and select File --> New Trace.
-Select the option of events, columns, and filters you want in your trace.
* Start the trace and then stop it.
* to check the script file Click File > Export > Script Trace Definition > For SQL Server 2005
* Save the trace file on local disk.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[trc_DBNAME_PROD] Script Date: 10/30/2011 21:06:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[trc_DBNAME_PROD] @Folder nvarchar(200)
as
/*
Start a 120 minute profiler trace storing the captured output in provider folder.
The folder must exist. A subfolder will be created using the start date and time to allow for repeated running of this profile without replacing the previuos captured trace files. On SQL Server 2005, XP_CMDSHELL needs to be enable to create the subfolder.
*/
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,120,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 1024 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--> By your saved trace file…..<--
-- Set the events… add the events under this section
/* for example please see the below format
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on

-->By your saved trace file…..-
-- Set the events… add the events under this section
/* for example please see the below format
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 18
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HC1APAU1P'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%Profiler%'
exec sp_trace_setfilter @TraceID, 11, 0, 0, N'CDS_service'
--exec sp_trace_setfilter @TraceID, 11, 0, 0, N'test'
exec sp_trace_setfilter @TraceID, 35, 0, 0, N'CDSAML_PROD'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

· Automation process is completed
· Please create a store procedure according to your naming conventions

1) Please create a JOB with the below script
2) USE [master]
3) GO
4)
5) DECLARE @return_value int
6)
7) EXEC @return_value = [dbo].[trc_DBNAME_PROD]
8) @Folder = N'E:\ProfilerTrace\Template'
9)
10)SELECT 'Return Value' = @return_value
11)
12) GO

Please select the your desired location to @ Folder = ‘*******’

1) If you want to do manual stop, u need to execute the below script
select * FROM ::fn_trace_getinfo(default) –- to find the running trace ID’s.. please don’t stop trace ID 1 because its default treace

EXEC sp_trace_setstatus @traceid = 2, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = 2, @status = 2; -- Close trace and delete it from the server

NOTE***: Please enable XP_CMDSHELL, bocz the above script will create folders on local disk...

Thanks,
Satish Kumar.

connect to ssis service failed access is denied

Some times users may face the issues when the users are trying to connect remote integration services with the below error message:

connect to ssis service failed
access is denied

Work Around:
Please refer to this LINK given by MicroSoft:

http://support.microsoft.com/kb/2000474

Thanks,
Satish Kumar.

Execute permissions to Integration services on SQL Server

follow the below process to grant access for Integration services:

GO: MSDB --> Security --> Roles --> db_ssisltduser --> add the user/group name

Give SQLAgentOperatorRole --> if they want them to schedule it.

For more on integration securities please refer the link:

http://msdn.microsoft.com/en-us/library/ms141053.aspx

Thanks,
Satish Kumar.

send an email if the query returns any data

Hi All,
Some times we have requirement to send an e-mail, if the query returns data. please use the below code:
==============================================
use DATABASE_NAME
go
if exists(SELECT DstDesc, DstDT, COUNT(*) as [Count] from Distributions where
DstDT > '25-Aug-2011' group by DstDesc, DstDT having COUNT(*) > 1)

begin
--print 'test'
exec msdb..sp_send_dbmail @profile_name = 'SQL_MAIL_Profile_NAME',
@recipients = 'user_name@mail.com',
@subject = 'Duplicate Distribution',
@body = 'Please find below the duplicate entries in Distributions table',
@query = 'set nocount on
SELECT DstDesc, DstDT, COUNT(*) as [Count] from Distributions where
DstDT > ''25-Aug-2011'' group by DstDesc, DstDT having COUNT(*) > 1
set nocount off',
@execute_query_database = 'DATABASE_NAME',
@query_result_header = 1,
@query_result_width = 256
end
==============================================
Thanks,
Satish Kumar.

shrink database failed with PAGE granularity

Msg 651, Level 16, State 1, Line 1Cannot use the PAGE granularity hint on the table "dbo.table_name" because locking at the specified granularity is inhibited.The statement has been terminated.

The issue will error occurs when you have one or more non-clustered indexes created on table without a clustered index on table. One should always create a clustered index on table.

Resolution for above error is just to create a Clustered Index on the table AND re-run the shrink database

Thanks,
Satish Kumar.

Query to pull the users created in a database

SELECT
SU.Name AS UserName,
SU.createdate As createdate,
SR.Name AS RoleName
FROM sysUsers AS SU
INNER JOIN
sysUsers AS SR ON SU.GID = SR.UID WHERE
SU.GID <> SU.UID
ORDER BY RoleName, UserName

Enabling Service Broker on MSDB

Sometimes SQL Server jobs will be failed due to the following issue:

Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.

Solution
Enable Service Broker on MSDB …
To check Service Broker is enabled on MSDB, please execute the given query:
select is_broker_enabled,* from sys.databases where name = 'MSDB'

If the output value of “is_broker_enabled” is 0 -> Service Broker NOT enabled
Note: A value of 0 indicates that Service Broker message delivery is not activated in the MSDB database

If the output value of is_broker_enabled is 1 -> Service Broker IS enabled

To enable the Service Broker on MSDB database is as follows:

1) Connect to SQL Server
2) Stop the SQL Agent Services
3) Run the query "ALTER DATABASE MSDB SET ENABLE_BROKER"
4) Check the service broker is enabled or not by executing the below query:
4.1) select is_broker_enabled from sys.databases where name = 'MSDB'
4.2) The output value “1” means service broker is successfully enabled
5) Start the SQL Agent Services

Reason to stop SQL Agent services: Enabling Service Broker is not allowed when the database is in USE. Obviously SQL Agent will use MSDB database.

After the execution of the above, please exeute the below scripts to confirm that we are receving mails:

EXEC msdb.dbo.sp_send_dbmail
@recipients = user_name@mail.com',
@body = 'TEST.',
@subject = 'TEST' ;