How to change the location of the Default Trace Files?

I had a situation where a 500 GB drive needed to be repurposed and the…oh 20GB worth of databases on it needed to be moved somewhere else.  Even though this included the system databases this was not overly complicated and not something you can’t find instructions for anywhere else thanks to our friend google.  A little more complicated was that the default trace files were also being written to that location.  Although I suspect the worst result of leaving that location would be a failed message in the logs and no default trace, I didn’t want SQL to do anything silly like fail to start the moment it couldn’t write to the default trace.  It seems this is either such a blindingly simple thing to do that people haven’t posted a how to online, or that it’s not something people have a call to do much.  At any rate, I found this blog post on sqlconcepts that indicates you can’t change default settings for the default trace.  Luckily while that is true of the size or the number of files retained, it is a relatively simple matter to change the location.

Reading the microsoft article on the default trace it says that the default location is MSSQL\LOG which is technically true, and also led me to believe that the default trace was stored, by default, in the same location as the error logs.  You can change the location your error logs are written to by adjusting the location of your current error logs in your SQL Server startup parameters.  I tested this on my local instance(SQL 2012) and everything ran fine, switching to the new location after a server restart.  I guess this makes sense given that the default trace really is another sort of error log.

On a slight tangent due to the sql concepts post above:  If you want to retain more history than the default(5 20MB files – which can equate to not very much at all on a heavily used server) it is possible to do so by simply scheduling a job which copies the trace files to another location.  You can either just copy all *.trc files out of your errorlog location to your archive location, or if you want to get fancy and only copy the new files you can get the current file name with:

select path from sys.traces where id=1

So long as the default trace is enabled it should always have an id of 1 as it is started(and rolled) whenever SQL Server restarts.

 

I hope this is helpful to someone.

Leave a Comment

Your email address will not be published.