SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating the SQL Profiler


Automating the SQL Profiler

Author
Message
nivedita talukdar
nivedita talukdar
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 162
Hi All,

I'm facing an issue while trying to automate the SQL Profiler run via scripts:

Requirement:

Need to audit few events on 24 hour basis /365 days for auditing purpose

What I did:

1) Started SQL Profiler and selected events as per my choice and stopped the trace...
2) Scripted the profiler output and saved it as a stored procedure
3) Called the SP through SQL Agent Job abd scheduled to run every 5 mins..

Results:
Except the first job run, rest all run fails...

Can you please help me...

Thanks..
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
Fails with what error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nivedita talukdar
nivedita talukdar
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 162
Message
Executed as user: NT AUTHORITY\SYSTEM. Cannot create a new trace because the trace file path is found in the existing traces. [SQLSTATE 42000] (Error 19067). The step failed.


Fails with the above error... which is quite obvious as the trace file already exists...
Can you please help me to modify the script so as to append the existing file?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
You can't append a trace to an existing file. Change the script so that it always creates a different file. Having the datetime in the file name is usually the easiest.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nivedita talukdar
nivedita talukdar
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 162
Can you please help me to edit this:

-------------------------------------------------------------------------------------------------------------------------

USE [Audit]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
CREATE proc [dbo].[TraceScript]
@DBName sysname = null
as

set nocount on

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5


exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
Changed this to
{ exec @rc = sp_trace_create @TraceID output, 0, N'C:\PWB_Test\AuditTace', @maxfilesize, NULL
if (@rc != 0) goto error
}

declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 118, 7, @on
exec sp_trace_setevent @TraceID, 118, 23, @on
exec sp_trace_setevent @TraceID, 118, 8, @on
exec sp_trace_setevent @TraceID, 118, 40, @on
exec sp_trace_setevent @TraceID, 118, 64, @on
exec sp_trace_setevent @TraceID, 118, 1, @on
exec sp_trace_setevent @TraceID, 118, 9, @on
exec sp_trace_setevent @TraceID, 118, 41, @on
exec sp_trace_setevent @TraceID, 118, 49, @on
exec sp_trace_setevent @TraceID, 118, 6, @on
exec sp_trace_setevent @TraceID, 118, 10, @on
exec sp_trace_setevent @TraceID, 118, 14, @on
exec sp_trace_setevent @TraceID, 118, 26, @on
exec sp_trace_setevent @TraceID, 118, 34, @on
exec sp_trace_setevent @TraceID, 118, 50, @on
exec sp_trace_setevent @TraceID, 118, 3, @on
exec sp_trace_setevent @TraceID, 118, 11, @on
exec sp_trace_setevent @TraceID, 118, 35, @on
exec sp_trace_setevent @TraceID, 118, 51, @on
exec sp_trace_setevent @TraceID, 118, 4, @on
exec sp_trace_setevent @TraceID, 118, 12, @on
exec sp_trace_setevent @TraceID, 118, 28, @on
exec sp_trace_setevent @TraceID, 118, 60, @on
exec sp_trace_setevent @TraceID, 118, 5, @on
exec sp_trace_setevent @TraceID, 118, 21, @on
exec sp_trace_setevent @TraceID, 118, 29, @on
exec sp_trace_setevent @TraceID, 118, 37, @on
exec sp_trace_setevent @TraceID, 131, 7, @on
exec sp_trace_setevent @TraceID, 131, 23, @on
exec sp_trace_setevent @TraceID, 131, 8, @on
exec sp_trace_setevent @TraceID, 131, 40, @on
exec sp_trace_setevent @TraceID, 131, 64, @on
exec sp_trace_setevent @TraceID, 131, 1, @on
exec sp_trace_setevent @TraceID, 131, 41, @on
exec sp_trace_setevent @TraceID, 131, 49, @on
exec sp_trace_setevent @TraceID, 131, 6, @on
exec sp_trace_setevent @TraceID, 131, 10, @on
exec sp_trace_setevent @TraceID, 131, 14, @on
exec sp_trace_setevent @TraceID, 131, 26, @on
exec sp_trace_setevent @TraceID, 131, 34, @on
exec sp_trace_setevent @TraceID, 131, 50, @on
exec sp_trace_setevent @TraceID, 131, 3, @on
exec sp_trace_setevent @TraceID, 131, 11, @on
exec sp_trace_setevent @TraceID, 131, 35, @on
exec sp_trace_setevent @TraceID, 131, 51, @on
exec sp_trace_setevent @TraceID, 131, 59, @on
exec sp_trace_setevent @TraceID, 131, 4, @on
exec sp_trace_setevent @TraceID, 131, 12, @on
exec sp_trace_setevent @TraceID, 131, 28, @on
exec sp_trace_setevent @TraceID, 131, 60, @on
exec sp_trace_setevent @TraceID, 131, 21, @on
exec sp_trace_setevent @TraceID, 131, 29, @on
exec sp_trace_setevent @TraceID, 131, 37, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - de4d310b-cffd-4a87-9da9-d7f9768c9c59'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1


select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
------------------------------------------------------------------------------------------------------------------------
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
Hang on...

If you need to audit 24/7/365, why are you running the trace every 5 minutes? Set a larger file size, set a rollover (BoL will show you how to do that) and leave one trace to run all the time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


nivedita talukdar
nivedita talukdar
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 162
Had checked the rollover option as it is by default but no use...job still fails..
Ok if I have to run this 24/7 and need to keep the data for not more than 1 or 2 months...how do I keep it running?
I mean the trace or the SQL Job or...? Am just so confused now...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218193 Visits: 46278
nivedita talukdar (6/20/2013)
Had checked the rollover option as it is by default but no use...job still fails..


Yes, it will. The rollover's not for restarting the job, it's for running the job for long periods and using multiple files

Ok if I have to run this 24/7 and need to keep the data for not more than 1 or 2 months...how do I keep it running?


Specify EndTime as null on the trace definition, and make sure that you have a good sized file and the rollover option

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8404 Visits: 3718
You need to build some logic into the Agent jobstep to check if the trace is allready running. If the trace definition doesn't exist you need to create and start the trace. If the trace is stopped you'll need to start the trace. If the trace is running just end the jobstep.

-- get trace information
SELECT
[traceid]
,[property]
,[tpdesc] =
case
when [property] = 1 and [value] = 1 then N' produces a rowset.'
when [property] = 1 and [value] = 2 then N' creates a new file when max file size is reached.'
when [property] = 1 and [value] = 3 then N' create a new file when max file size is reached and produce a rowset.'
when [property] = 1 and [value] = 4 then N' shuts down the trace on an error.'
when [property] = 1 and [value] = 5 then N' produces a rowset and shutdown on error.'
when [property] = 1 and [value] = 6 then N' creates a new file when max file size is reached and shuts down on an error.'
when [property] = 1 and [value] = 7 then N' creates a new file when max file size is reached, produces a rowset and shuts down on an error.'
when [property] = 1 and [value] = 8 then N' is a Blackbox trace.'
when [property] = 2 then N' results are in file ' + cast([value] as nvarchar(245)) + N'.trc.'
when [property] = 3 then N' max file size is ' + cast([value] as nvarchar(5)) + N' megabytes.'
when [property] = 4 and [value] is not null then N' automatically stops on ' + cast([value] as nvarchar(25)) + '.'
when [property] = 4 and [value] is null then N' does not automatically stop on any date and time.'
when [property] = 5 and [value] = 0 then N' is stopped.'
when [property] = 5 and [value] = 1 then N' is running.'
end
FROM :: fn_trace_getinfo(0)
ORDER BY [traceid],[property] DESC



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
nivedita talukdar
nivedita talukdar
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 162
Hi,

Thank you so much for your response... I'm able to set the trace and run and it does creates multiple files but I'm unable to load all of the .trc files data to tables...

As suggested I'm using this:
USE AdventureWorks;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('c:\temp\mytrace.trc', default);
GO

But this doesn't copy or load all the .trc files...instead just does it for the first file and thus not satisfying my condition...

I created a job with 3 steps:
1) Stop the trace
2) Load into table
3) Use xp_cmdshell to delete the .trc file

But this doesn't seem to be working Sad

Can you please help me to build this job as this looks quite simple but isn't and at the same time is quite critical aspect for this task..

Cheers,
Nivedita
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search