June 20, 2013 at 2:43 am
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..
June 20, 2013 at 3:04 am
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
June 20, 2013 at 3:16 am
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?
June 20, 2013 at 3:48 am
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
June 20, 2013 at 4:36 am
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]
@DBNamesysname = 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
------------------------------------------------------------------------------------------------------------------------
June 20, 2013 at 4:39 am
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
June 20, 2013 at 6:38 am
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...
June 20, 2013 at 6:41 am
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
June 20, 2013 at 6:49 am
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
June 23, 2013 at 6:49 am
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
June 23, 2013 at 11:48 pm
If you have setup the trace using rollover files including a max number of files to use, you will have to read the first file before the rollover reaches the max number of files and starts over using the first again. Look at the datetime the files were last modified to see if this has happened.
There could be aanother issue with the filenames, see BOL:
Be aware that the fn_trace_gettable function will not load rollover files (when this option is specified by using the number_files argument) where the original trace file name ends with an underscore and a numeric value. (This does not apply to the underscore and number that are automatically appended when a file rolls over.)
Can you show us a list of the trace files in a post. Include the datetime the files were last modified.
June 24, 2013 at 12:05 am
Hi there,
Thanks for your response, below is the script generated from Profiler and I've been using this which generates a rollover file like 'T.trc', 'T_1.trc','T_2.trc' and so on..
I've not included or rather set any limit to files creation as such as this (DDL trace) needs to run continuously..
I thought that it would be ideal to load data from each file into a table and then delete each of the files..
But neither I'm able to load the data into a table sequentially and nor able to delete the files...just stuck..
-------------------------------------------------------------------------------------------------------------------
USE [DB_NAME]
GO
/****** Object: StoredProcedure [dbo].[TraceScript] Script Date: 06/24/2013 13:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Create proc [dbo].[TraceScript]
@DBNamesysname = null
as
set nocount on
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, N'C:\PWB_Test\T', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 111, 9, @on
exec sp_trace_setevent @TraceID, 111, 6, @on
exec sp_trace_setevent @TraceID, 111, 10, @on
exec sp_trace_setevent @TraceID, 111, 14, @on
exec sp_trace_setevent @TraceID, 111, 11, @on
exec sp_trace_setevent @TraceID, 111, 12, @on
exec sp_trace_setevent @TraceID, 152, 1, @on
exec sp_trace_setevent @TraceID, 152, 9, @on
exec sp_trace_setevent @TraceID, 152, 10, @on
exec sp_trace_setevent @TraceID, 152, 11, @on
exec sp_trace_setevent @TraceID, 152, 12, @on
exec sp_trace_setevent @TraceID, 152, 6, @on
exec sp_trace_setevent @TraceID, 152, 14, @on
exec sp_trace_setevent @TraceID, 129, 1, @on
exec sp_trace_setevent @TraceID, 129, 6, @on
exec sp_trace_setevent @TraceID, 129, 10, @on
exec sp_trace_setevent @TraceID, 129, 14, @on
exec sp_trace_setevent @TraceID, 129, 11, @on
exec sp_trace_setevent @TraceID, 129, 12, @on
exec sp_trace_setevent @TraceID, 130, 1, @on
exec sp_trace_setevent @TraceID, 130, 10, @on
exec sp_trace_setevent @TraceID, 130, 11, @on
exec sp_trace_setevent @TraceID, 130, 12, @on
exec sp_trace_setevent @TraceID, 130, 6, @on
exec sp_trace_setevent @TraceID, 130, 14, @on
exec sp_trace_setevent @TraceID, 118, 1, @on
exec sp_trace_setevent @TraceID, 118, 9, @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, 11, @on
exec sp_trace_setevent @TraceID, 118, 12, @on
exec sp_trace_setevent @TraceID, 131, 1, @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, 11, @on
exec sp_trace_setevent @TraceID, 131, 12, @on
exec sp_trace_setevent @TraceID, 176, 1, @on
exec sp_trace_setevent @TraceID, 176, 9, @on
exec sp_trace_setevent @TraceID, 176, 10, @on
exec sp_trace_setevent @TraceID, 176, 11, @on
exec sp_trace_setevent @TraceID, 176, 12, @on
exec sp_trace_setevent @TraceID, 176, 6, @on
exec sp_trace_setevent @TraceID, 176, 14, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 97fe69e2-a853-4f40-840e-e7d576108b43'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
----------------------------------------------------------------------------------------------------------------
June 24, 2013 at 12:28 am
nivedita talukdar (6/23/2013)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...
From the sample above, you should have files in your folder with names like "mytrace.trc", "mytrace_1.trc", "mytrace_3.trc", etc.
From the post with your trace-create statement you should have files in folder C:\PWB_test with names "T.trc", "T_1.trc", "T_2.trc", etc. Are these files present in the folder?
And if you change the "default" option to a specific number of files (example: 2), will it still load only one tracefile into the table?
June 24, 2013 at 12:36 am
Hi there,
Yes there are files like 'T.trc', 'T_1.trc' and so on...
But when my job step 2 which is to load the data into the table is run using any of the below commands it fails 🙁
USE DB_NAME
GO
SELECT * INTO trace_table1 FROM ::fn_trace_gettable('C:\PWB_Test\T.trc', 1)
OR----
USE DB_NAME
GO
SELECT * INTO trace_table1 FROM ::fn_trace_gettable('C:\PWB_Test\T.trc', default)
June 24, 2013 at 12:41 am
And what is the error message when the steps fail?
If you can't find the error message, execute the command "select * from ::fn_trace_gettable('C:BWP_test\T.trc', default)" from a SSMS query window...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply