October 17, 2008 at 8:55 am
All,
I need to schedule a sql trace on Profiler.I see a stop time but no start time.Accordin to msdn I can schedule a Trace by the SQL Profiler GUI or as a job via SQL Server Agent.How do I do this,I need to execute the trace while my monthly subscription runs on the begining of each month roughly for 5 min.How can I achive this ?
October 17, 2008 at 9:13 am
Get profiler to script the trace. (File - Export). Put the script in a SQL job, make the couple of required changes (specifying a filename and the end time) and then schedule the job to run when you need it to.
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
October 17, 2008 at 9:28 am
Im trying right now.Shall update if I face any problems.
Thank You
October 17, 2008 at 9:48 am
This is what I have done.
Go to SQL Profiler > Create new Trace>
Specify Trace stop time and file name to save the trace to.
The Trace would start executing.Go to File >Export >Script Trace
Defination> SQL 2005
Save the Scripted trace as .sql.
Open Mngt Studio > New Query.Open the .sql file .Edit the following two lines
set @DateTime = '2008-10-17 11:00:00.000' [Script End Time]
exec @rc = sp_trace_create @TraceID output, 0, N'C:\1.trc', @maxfilesize, @Datetime [script file location]
Create a new sql job of type TSQL and copy the scripted trace and schedule a start time.
I have tested the sql job by running it.The job executed at the schedule start time but did not continue executing till the scripts end time.Instead executed and stopped within a sec.
The .trc file that it had to save the trace to was also empty.
Am I missing soing something wrong here?
October 17, 2008 at 9:59 am
You may want to set the end time to be an offset of the time the script runs. Something like
SET @DateTime = DATEADD(hr,1,GETDATE())
if you want the trace to run for an hour
Run it in management studio before you put it into a job. You'll see errors in management studio, you won't see them in a job
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
October 17, 2008 at 10:00 am
Should the trace use Master db to run ?
October 17, 2008 at 10:08 am
Doesn't matter what database it's in.
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
October 18, 2008 at 5:01 pm
LOOKUP_BI (10/17/2008)
All,I need to schedule a sql trace on Profiler.I see a stop time but no start time.Accordin to msdn I can schedule a Trace by the SQL Profiler GUI or as a job via SQL Server Agent.How do I do this,I need to execute the trace while my monthly subscription runs on the begining of each month roughly for 5 min.How can I achive this ?
best way is via a server side trace, the 4 stored procedures you need are
SP_TRACE_CREATE
SP_TRACE_SETEVENT
SP_TRACE_SETFILTER
SP_TRACE_SETSTATUS
also the functions
fn_trace_gettable
fn_trace_getinfo
to generate a trace definition use the following syntax
declare @traceidout int
declare @maxfilesize bigint
declare @on bit
set @on = 1
set @maxfilesize = 50 --size in MB's
exec sp_trace_create @traceidout output, 2, N'D:\Trace\mytrace', @maxfilesize, NULL
exec sp_trace_setevent @traceidout, 12, 1, @on
exec sp_trace_setevent @traceidout, 12, 3, @on
change @maxfilesize to whatever value you require (its in MB)
Dont append the .TRC to the path above it does it for you.
refer to BOL for all trace events and columns
set a filter using the following syntax
exec sp_trace_setfilter @traceidout, 3, 0, 0, 7
use the following to get your trace details and ID
select * from ::fn_trace_getinfo(default)
use the following to start, stop and close the trace
(must stop a trace before it can be closed. Must be closed before you can access the file)
exec sp_trace_setstatus TRACEID, 1 --start trace
exec sp_trace_setstatus TRACEID, 0 --stop trace
exec sp_trace_setstatus TRACEID, 2 --close trace
finally to output to a table stop and close the trace then use the following syntax
SELECT * INTO temp_tablename
FROM ::fn_trace_gettable('d:\trace\mytrace.trc', default)
You can create SQL jobs and schedule them using the code above. As it runs server side there are no I\O nasties that a client would generate and you can schedule it at will
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2008 at 9:42 am
Perry Whittle (10/18/2008)
use the following to start, stop and close the trace(must stop a trace before it can be closed. Must be closed before you can access the file)
Just to be clear - you don't have to stop a server side trace to access the trace file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2009 at 6:03 am
I'm trying to create a trace script like the one above having exported the SQL from Profiler. However, whatever path I choose for the trace file I get the following error:
Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(error not found).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
I suspect this could be because the server hasn't got write access to the drives I'm using but I can't work out how to check this. Any help would be appreciated!
June 11, 2009 at 6:07 am
can you post the create trace statement you are using
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 11, 2009 at 6:10 am
Yep, here you go:
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 11/06/2009 11:53:22 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- 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, 0, N'c:\trace_test', @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, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 60, @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, 31, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @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, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 60, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 21831f93-dde4-4674-b27e-8a2b08b0c1a0'
set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- 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:
go
June 11, 2009 at 6:17 am
You're trying to write the trace file in the root of C:.
I remember that on a lot of server systems you need special permissions to create files there. Try using a path like C:\temp\Trace.trc
[font="Verdana"]Markus Bohse[/font]
June 12, 2009 at 5:35 am
i agree with Markus. Create\use a folder that you have permissions for and use that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 12, 2009 at 8:22 am
Thanks very much for your help on this chaps. Markus' suggested file location has certainly cured the error message. Excuse me if this is a daft question but I can't work out how to get at the trc file the code has created. I'm presuming the c drive referred to is the c drive for the server & not for my own machine but I can't work out how to find it.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply