Managing trace files on Sql Server 2005

Question

I need to manage the trace files for a database on Sql Server 2005 Express Edition. The C2 audit logging is turned on for the database, and the files that it's creating are eating up a lot of space.

Can this be done from within Sql Server, or do I need to write a service to monitor these files and take the appropriate actions?

I found the [master].[sys].[trace] table with the trace file properties. Does anyone know the meaning of the fields in this table?

Answer 1

You can also set up SQL Trace to log to a SQL table. Then you can set up a SQL Agent task to auto-truncate records.

Answer 2

sys.traces has a record for every trace started on the server. Since SQL Express does not have Agent and cannot set up jobs, you'll need an external process or service to monitor these. You'll have to roll your own everything (monitoring, archiving, trace retention policy etc). If you have C2 audit in place, I assume you have policies in place that determine the duration audit has to be retained.

Answer 3

Here's what I came up with that is working pretty good from a 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!");
    }