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 Sunday, June 23, 2013 11:48 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1466580
Posted Monday, June 24, 2013 12:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

----------------------------------------------------------------------------------------------------------------
Post #1466587
Posted Monday, June 24, 2013 12:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1466592
Posted Monday, June 24, 2013 12:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 14, Visits: 160
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)
Post #1466594
Posted Monday, June 24, 2013 12:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1466597
Posted Monday, June 24, 2013 12:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 14, Visits: 160
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... job fails..

Any suggestions for this one?

Cheers
Post #1466598
Posted Monday, June 24, 2013 12:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1466601
Posted Monday, June 24, 2013 12:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:22 PM
Points: 14, Visits: 160
Hi,

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

Can you please put up some logic and share it here :)

Thank you for all your efforts..

Cheers
Post #1466603
Posted Monday, June 24, 2013 1:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,350, Visits: 2,856
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’! **
Post #1466604
Posted Monday, June 24, 2013 2:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:51 AM
Points: 916, Visits: 2,881
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
Post #1466637
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse