Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Automating the SQL Profiler Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 2:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 14, Visits: 158
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..
Post #1465517
Posted Thursday, June 20, 2013 3:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 42,990, Visits: 36,144
Fails with what error?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1465529
Posted Thursday, June 20, 2013 3:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 14, Visits: 158
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?
Post #1465534
Posted Thursday, June 20, 2013 3:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 42,990, Visits: 36,144
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 2008, MVP
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

Post #1465548
Posted Thursday, June 20, 2013 4:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 14, Visits: 158
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
------------------------------------------------------------------------------------------------------------------------
Post #1465572
Posted Thursday, June 20, 2013 4:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 42,990, Visits: 36,144
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 2008, MVP
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

Post #1465576
Posted Thursday, June 20, 2013 6:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 14, Visits: 158
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...
Post #1465648
Posted Thursday, June 20, 2013 6:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:26 AM
Points: 42,990, Visits: 36,144
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 2008, MVP
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

Post #1465650
Posted Thursday, June 20, 2013 6:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 2,290, Visits: 2,749
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’! **
Post #1465659
Posted Sunday, June 23, 2013 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 14, Visits: 158
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

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
Post #1466520
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse