SQL Server Audit Trace Logs .trc

We have audittrace_xxx.trc files being created every couple of minutes at about ~5 MB increments. The C2 audit tracing is disabled. I have read online about turning off the logs completely with a couple lines. But ideally I would like to manage it and keep it for a few days before it is deleted, hopefully automatically. Is this possible? Is there a way to manage the logs? Thank you.
LVL 1
First LastAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy DownsOWNERCommented:
Maybe this will help.

trace files for a database on Sql Server 2005 Express Edition. The C2 audit logging is turned on

 console application:

static void Main(string[] args)
    {
        try
        {
            Console.WriteLine("CcmLogManager v1.0");
            Console.WriteLine();

            // How long should we keep the files around (in months) 12 is the PCI requirement?
            var months = Convert.ToInt32(ConfigurationManager.AppSettings.Get("RemoveMonths") ?? "12");

            var currentFilePath = GetCurrentAuditFilePath();

            Console.WriteLine("Path: {0}", new FileInfo(currentFilePath).DirectoryName);
            Console.WriteLine();

            Console.WriteLine("------- Removing Files --------------------");

            var fileInfo = new FileInfo(currentFilePath);
            if (fileInfo.DirectoryName != null)
            {
                var purgeBefore = DateTime.Now.AddMonths(-months);
                var files = Directory.GetFiles(fileInfo.DirectoryName, "audittrace*.trc.zip");

                foreach (var file in files)
                {
                    try
                    {
                        var fi = new FileInfo(file);

                        if (PurgeLogFile(fi, purgeBefore))
                        {
                            Console.WriteLine("Deleting: {0}", fi.Name);

                            try
                            {
                                fi.Delete();
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }
            }

            Console.WriteLine("------- Files Removed ---------------------");
            Console.WriteLine();


            Console.WriteLine("------- Compressing Files -----------------");

            if (fileInfo.DirectoryName != null)
            {
                var files = Directory.GetFiles(fileInfo.DirectoryName, "audittrace*.trc");

                foreach (var file in files)
                {
                    // Don't attempt to compress the current log file.
                    if (file.ToLower() == fileInfo.FullName.ToLower())
                        continue;

                    var zipFileName = file + ".zip";

                    var fi = new FileInfo(file);
                    var zipEntryName = fi.Name;

                    Console.WriteLine("Zipping: \"{0}\"", fi.Name);

                    try
                    {
                        using (var fileStream = File.Create(zipFileName))
                        {
                            var zipFile = new ZipOutputStream(fileStream);
                            zipFile.SetLevel(9);

                            var zipEntry = new ZipEntry(zipEntryName);
                            zipFile.PutNextEntry(zipEntry);

                            using (var ostream = File.OpenRead(file))
                            {
                                int bytesRead;
                                var obuffer = new byte[2048];
                                while ((bytesRead = ostream.Read(obuffer, 0, 2048)) > 0)
                                    zipFile.Write(obuffer, 0, bytesRead);
                            }

                            zipFile.Finish();
                            zipFile.Close();
                        }

                        fi.Delete();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex);
                    }
                }
            }

            Console.WriteLine("------- Files Compressed ------------------");
            Console.WriteLine();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

        Console.WriteLine("Press any key...");
        Console.ReadKey();
    }

    public static bool PurgeLogFile(FileInfo fi, DateTime purgeBefore)
    {
        try
        {
            var filename = fi.Name;
            if (filename.StartsWith("audittrace"))
            {
                filename = filename.Substring(10, 8);

                var year = Convert.ToInt32(filename.Substring(0, 4));
                var month = Convert.ToInt32(filename.Substring(4, 2));
                var day = Convert.ToInt32(filename.Substring(6, 2));

                var logDate = new DateTime(year, month, day);

                return logDate.Date <= purgeBefore.Date;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

        return false;
    }

    public static string GetCurrentAuditFilePath()
    {
        const string connStr = "Data Source=.\\SERVER;Persist Security Info=True;User ID=;Password=";

        var dt = new DataTable();

        var adapter =
            new SqlDataAdapter(
                "SELECT path FROM [master].[sys].[traces] WHERE path like '%audittrace%'", connStr);
        try
        {
            adapter.Fill(dt);

            if (dt.Rows.Count >= 1)
            {
                if (dt.Rows.Count > 1)
                    Console.WriteLine("More than one audit trace file defined!  Count: {0}", dt.Rows.Count);

                var path = dt.Rows[0]["path"].ToString();
                return path.StartsWith("\\\\?\\") ? path.Substring(4) : path;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

        throw new Exception("No Audit Trace File in sys.traces!");
    }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.