Post reply

Increase trace file retention in SQL 2012

  • January 29, 2014 at 9:51 am

    #285059

    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

    #1684392

    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

    #1684394

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply