Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating the SQL Profiler


Automating the SQL Profiler

Author
Message
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
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’! **
nivedita talukdar
nivedita talukdar
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 162
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]
   @DBName   sysname = 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:

----------------------------------------------------------------------------------------------------------------
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
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’! **
nivedita talukdar
nivedita talukdar
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 162
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 Sad

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)
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
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’! **
nivedita talukdar
nivedita talukdar
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

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

I just edited the job and ran this below command:

SELECT * INTO trace_table1 FROM ::fn_trace_gettable('C:\PWB_Test\T.trc', 1)

The job went fine....and also deleted my T.trc file from folder...

But how do I do for the rest of the files now...

If I do change the value to 2 or 3 it says that, 'trace_table1' already exists... Sad job fails..

Any suggestions for this one?

Cheers
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
Add a new first step to the job to check if the table exisits and only create it if it doesn't exist yet.
Change the command to read the trace from "SELECT * INTO ... FROM " (which creates the table) to "INSERT ... SELECT * FROM " (to use the existing table).

Use a combinations of SQL and commandprompt to get the tracefile with the lowest sequence number. Or you could use a staging table to hold the number and increase it each time the job has run. Or use some other logic to get the name of the file...

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

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

Didnt exactly get Sad am very very poor in programming and doing this for the first time...

Can you please put up some logic and share it here Smile

Thank you for all your efforts..

Cheers
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
Take a look at the following links to get a file list:
http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
Uses a undocumented feature that could be changed/removed by Microsoft without notice!!

http://oakdome.com/programming/SQL_FileListing.php
Uses the XP_CMDSHELL extended stored procedure and could be a security risk!!

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
Running this 24\7 is potentially going to be massive amounts of data. I would have dedicated storage for the trace files.

exec sp_trace_setevent @TraceID, 'RPC:Completed', 'EndTime', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'Reads', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'ClientProcessID', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'Writes', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'BinaryData', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'ApplicationName', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'CPU', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'LoginName', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'SPID', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'Duration', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'NTUserName', @on
exec sp_trace_setevent @TraceID, 'RPC:Completed', 'StartTime', @on

exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'EndTime', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'Reads', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'TextData', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'ClientProcessID', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'Writes', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'NTUserName', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'ApplicationName', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'StartTime', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'CPU', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'LoginName', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'SPID', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchCompleted', 'Duration', @on

exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'TextData', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'ClientProcessID', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'NTUserName', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'ApplicationName', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'StartTime', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'LoginName', @on
exec sp_trace_setevent @TraceID, 'SQL:BatchStarting', 'SPID', @on




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
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