I need to delete a file from one particular path.


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
if it is on today's day then the new file should incremented to
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.



more ▼

asked Jun 26, 2012 at 06:09 AM in Default

avatar image

20 4 4 6

So if the file exists do you want to delete it or increment the name?

Jun 26, 2012 at 07:15 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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)

 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
 EXEC sp_trace_setstatus  @trace_id = @traceid 
           , @status = 0
 EXEC sp_trace_setstatus @trace_id = @traceid  
           , @status = 2

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.

more ▼

answered Jun 26, 2012 at 07:41 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

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'

more ▼

answered Jun 26, 2012 at 09:17 AM

avatar image

10.8k 37 55 51

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jun 26, 2012 at 01:56 PM

avatar image

20 4 4 6

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

Jun 26, 2012 at 02:52 PM Usman Butt

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.


Jun 26, 2012 at 02:59 PM satishn

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.

Jun 26, 2012 at 03:08 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 26, 2012 at 06:09 AM

Seen: 1246 times

Last Updated: Jun 26, 2012 at 03:16 PM

Copyright 2016 Redgate Software. Privacy Policy