Hi, My job is automate the trace for our customer. so, i will start the trace automatically through a schedule job. This job will save a trace in the particular path Eg: C:\Trace\Full_Performance_Trace_Mon.TRC Note: Here the path is C:\Trace, Trace will automatically create the file names like Full_Performance_Trace_Mon , Full_Performance_Trace_Tue, Full_Performance_Trace_Wed... on with respect to the today's day. Now, My problem is say now I'm running the trace the new file Full_Performance_Trace_Tue will get saved today. Now, if i again run the trace the job comes with the error message stating that the file is already available. So, i need to write a query through CMD shell if a file is already available in the location go if it is on today's day then the new file should incremented to Full_Performance_Trace_TUE1 else i need to delete the file.. for deleting the file i can use this [exec sp_trace_create @TraceID output, 0, @filepath, @maxfilesize, @stoptime] But, i need a condition for deleting a file. Thanks for the help any question let me know. Thanks Satish
Seems like you are Creating, stopping, closing and then re-creating the trace again to give the customized name. If I were you I would approach it in simple steps **Stop/Close the trace if running step**. For e.g. something like the following could help you do that (First Draft) DECLARE @traceid INT, @Day NVARCHAR(3), @FilePath NVARCHAR(2000) SET @Day = LEFT(DATENAME(WEEKDAY, CURRENT_TIMESTAMP),3) SELECT @FilePath = 'C:\Trace\Full_Performance_Trace_'+ @Day SELECT @traceid = [traceid] FROM fn_trace_getinfo (DEFAULT) WHERE [property] = 2 AND CAST([value] AS NVARCHAR(2000)) LIKE @FilePath + N'%' IF @traceid IS NOT NULL BEGIN EXEC sp_trace_setstatus @trace_id = @traceid , @status = 0 EXEC sp_trace_setstatus @trace_id = @traceid , @status = 2 END **Clean up Step**. Delete all the trace files of the current day i.e. with name like **Full_Performance_Trace_Tue*.trc**. You can use a proxy account with Step Type Operating System(CmdExec) for that. **Create Trace Step**. Create the trace with specified file name, maximum file size, stop time and `TRACE_FILE_ROLLOVER (option = 2)` to append the sequence number when the maximum file size is reached. Hope this gives you the idea.
You can create a maintenance plan, "Maintenance Cleanup Task" to cleanup the files with extension "trc" or include the following command prior to your current job step to cleanup trace files using SQL Server.
Thanks Anuj & Usman but i need something different. To say clearly, i have created a Sql schedule Job which will capture the statements and save it as .trc in the location 'C:\Trace\Full_Performance_Trace_mon.trc'. Say, some how the trace stops in middle.when i restart the trace it will say the file is already presented in the particular location. i can use the cmd shell to delete the file but i cant because at the end i will combine two trace that run on Mon, say Full_Performance_Trace_mon.trc && Full_Performance_Trace_mon1.trc and copy into the table "Full_Performance_Trace_mon" using the query Select * FROM ::fn_trace_gettable('c:\trace\Full_Performance_Trace_mon.trc', default ) So, is there any way to create a new file automatically say Full_Performance_Trace_mon1. so the trace will be running fine. Thanks Satish