January 29, 2014 at 9:51 am
With the current activity in our SQL 2012 we're only retaining about 9-10 days of transactions in the 5 Trace files setup by default. Is there any way to set SQL to not drop the old trace files so I can copy them to another server and remove them manually?
Thanks.
January 29, 2014 at 9:56 am
Just set up your own automated task to do the copy on a frequent basis. SCHTASKS is built into Windows and is fine for this. SQL Agent could be used - any number of mechanisms actually.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 29, 2014 at 10:00 am
you are talking about the default trace?
if that's true, there's no way to modify that built in default trace.
however, you can script out that same trace,and create a duplicate of it, and then change the file size, number of rollover files etc.
then you create your new trace with that definition and use that in the future.
the main issue with that is you also need to create a stored procedure that recreates the trace on startup of the server, as traces are stopped and dropped when the server stops and restarts.
you could also simply create a job that reads the current default trace, insert it's results into a table; have that job run every x days or so, and insert only items that don't exist from the trace yet, so you have a longer trace history.
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply