Automating the SQL Profiler

  • 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..

  • 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
  • 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?

  • 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
  • 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

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

  • 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
  • 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...

  • 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
  • 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’! **
  • 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

  • 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.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • 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:

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

  • 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?

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • 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)

  • 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...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply