asked
I need to delete a file from one particular path.
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
So if the file exists do you want to delete it or increment the name?
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.
EXECUTE master.dbo.xp_delete_file 0,N'E:\MyTraceFiles','trc',N'2012-06-26T00:00:00'
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
Sorry, I did not understand. If somehow the trace stopped then you can restart it by using "sp_trace_setstatus" at any time. No need to give the new name for the file. If you want to delete the file then first you have to remove the trace again by using "sp_trace_setstatus". Moreover, with TRACE_FILE_ROLLOVER and max file size parameters while creating the trace would add the sequence if the max file size is reached. You can get more help from [BOL][1] [1]:
http://msdn.microsoft.com/en-us/library/ms190362.aspx
Thanks usman, Okay now i will give another example. Now the schedule job run at 9-10 Am. On Monday. now in the path the trace will get copied in the location 'c:\trace\Full_Performance_Trace_mon.trc'. If you go in to the drive c:\trace\ this file full_performance_trace_mon will be there. Now, again i will run the job at 11-12. This time the job will stop and say that already a file name is there. so, i dont want full_performance_trace_mon.trc to be over written instead i want to create a new file full_performance_trace_mon1.trc in the same location. Thanks
Two things. Why you need to run the job at 11-12 again if the trace is already running? If you need to run the job then as I already mentioned in my answer first stop the trace, remove it and then re-create it. This way you have the flexibility to do whatever you want.
10 People are following this question.
