{"id":1753,"date":"2014-01-28T00:00:00","date_gmt":"2014-01-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/collect-your-sql-server-auditing-and-troubleshooting-information-automatically\/"},"modified":"2021-08-24T13:39:48","modified_gmt":"2021-08-24T13:39:48","slug":"collect-your-sql-server-auditing-and-troubleshooting-information-automatically","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/collect-your-sql-server-auditing-and-troubleshooting-information-automatically\/","title":{"rendered":"Collect Your SQL Server Auditing and Troubleshooting Information Automatically"},"content":{"rendered":"<div class=\"spaced-bottom padded--tight scheme--lightest-grey\">\n<img decoding=\"async\" class=\"media__image\" style=\"float: left; vertical-align: middle;\" src=\"https:\/\/assets.red-gate.com\/external\/SimpleTalk\/database-devops-cog-92.png\" \/> <\/p>\n<p class=\"padded-top--tight\"><strong>DevOps, Continuous Delivery & Database Lifecycle Management<\/strong><br \/>Continuous Integration<\/p>\n<\/div>\n\n<p class=\"start\">After many years, the Default Trace still remains the simplest \u00a0way of auditing SQL Server. It gives you so much useful information about significant events, when they happened and, where relevant, the login associated with the event. The Default trace is, however, now deprecated in favor of Extended Events and \u00a0so has not evolved much over the years. The biggest problem with it is that it consists only of 5 files of 20Mb each, and they get overwritten often, especially in a busy SQL Server environment. \u00a0<\/p>\n<p>This article shows how you can get around this difficulty in order to maintain an unbroken record of trace events. This is only the start.<\/p>\n<p>We then tackle the problems of maintaining\u00a0 a record of these default trace events for a whole group of servers and their databases, and use this archive for reporting and alerting for potential problems.\u00a0 We will do this by \u00a0automating the process of extracting the default trace data from several SQL Server instances to a centralized location, persisting the data to a single database and preparing it for further analysis. The information that it can provide you about the way these servers and databases are being used is extremely valuable and difficult to get any other way. \u00a0We can, for example get a complete record of every change to every database object, when it happened and who did it.<\/p>\n<p>For the purpose, we will be using a Robocopy script which offloads the default trace files from the remote servers, then SSIS package which will import the data into a database and will delete the imported files.<\/p>\n<p>The steps are as follows:<\/p>\n<ul>\n<li>Configure the Robocopy to access the remote server and to store the default trace files locally<\/li>\n<li>Configure the SSIS package to look for the default trace files copied by Robocopy<\/li>\n<\/ul>\n<p>\u00a0We&#8217;ll use Robocopy because \u00a0the tool can be used to<\/p>\n<ol>\n<li>monitor the folder in a remote server\u00a0 that contains the default trace files<\/li>\n<li>detect any changes and copy over any changed file \u00a0periodically<\/li>\n<\/ol>\n<p>We choose Robocopy over \u00a0SSIS to do this because \u00a0we would have to schedule an SSIS package to run quite often and the copying process is not as lightweight.<\/p>\n<h2>Setting up Robocopy<\/h2>\n<p>The purpose of the Robocopy script in this case is to use it to maintain a copy of the Default Trace files in a centralized location, since the default trace log files in the\u00a0 SSQL Server instance are overwritten after a certain time.<\/p>\n<p>This is a bit tricky to schedule and it is based on each individual SQL Server instance. For example, on a very busy production server it might be so that, every 10 minutes, all 5 default trace files are overwritten and on another SQL Server instance it may take 5 days for the files to be overwritten. The overwrite of the files depends on the volume of the traced events occurring on the system and also on instance restarts.<\/p>\n<p>This is why it will take some investigation to understand and to schedule the Robocopy script \u00a0in individual cases.<\/p>\n<p>For the purpose of this article I will use a setting for Robocopy to check for changes in the default trace files every 10 minutes, though, in the assumption that this interval would be geared to the number events being recorded in the trace for the individual server.<\/p>\n<p>The following script will execute Robocopy and will look at the default trace folder for the SQL Server instance and will copy over the changes to a local folder:<\/p>\n<pre>robocopy \"\\\\remoteServerA\\Program Files\\Microsoft SQL  Server\\MSSQL10_50.SQL2008\\MSSQL\\Log\" \"\\\\myMachine\\c$\\Destination\\remoteServerA\" *.trc \/MON:10<\/pre>\n<p>Note that the script is using the UNC path for the file storage locations. This means that it is up to the user to decide whether the robocopy script will be scheduled to run on the source machine or on the destination machine. (From my personal experience, it is better to have all Robocopy scripts to run from the same destination machine &#8211; it is easier to monitor and maintain).<\/p>\n<p>Also note that the Destination folder contains a sub-folder for each monitored server. This is used later on in the configuration of the SSIS package.<\/p>\n<h2>Setting up the database<\/h2>\n<p>Here is the database script:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\tCREATE TABLE [dbo].[ProcessingTrace_Config]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TraceProcessingID] [smallint] IDENTITY(1, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [varchar](256) NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TracePath] [varchar](550) NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IsActive] [bit] NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT [PK_ProcessingTrace_Config] PRIMARY KEY CLUSTERED\r\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0( [TraceProcessingID] ASC )\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[temp_trc]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TextData] [ntext] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [BinaryData] [image] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TransactionID] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LineNumber] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTUserName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [HostName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ClientProcessID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Duration] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EndTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Reads] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Writes] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [CPU] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Permissions] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Severity] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventSubClass] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ObjectID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Success] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IndexID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IntegerData] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventClass] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ObjectType] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NestLevel] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [State] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Error] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Mode] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Handle] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ObjectName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [FileName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [OwnerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [RoleName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetUserName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DBUserName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginSid] [image] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetLoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetLoginSid] [image] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ColumnPermissions] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LinkedServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ProviderName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [MethodName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [RowCounts] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [RequestID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [XactSequence] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventSequence] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [BigintData1] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [BigintData2] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [GUID] [uniqueidentifier] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IntegerData2] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ObjectID2] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Type] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [OwnerID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ParentName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IsSystem] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Offset] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SourceDatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SqlHandle] [image] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SessionLoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [PlanHandle] [image] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [GroupID] [int] NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_AlteredObjects]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DBName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTUserName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [HostName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Duration] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ObjectName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ObjectType] [varchar](58) NOT NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_CreatedUsersAndLogins]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [RoleName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetUserName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetLoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SessionLoginName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_DroppedUsersAndLogins]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [RoleName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetUserName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TargetLoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SessionLoginName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_ErrorLog]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [TextData] [ntext] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Severity] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Error] [int] NULL\r\n\u00a0\u00a0\u00a0 )\u00a0 \r\n\r\nCREATE TABLE [dbo].[trc_FileGrowAndShrink]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Duration] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EndTime] [datetime] NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_FTSearch]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IsSystem] [int] NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_LogFileAutoGrowAndShrink]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Duration] [bigint] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EndTime] [datetime] NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_LoginFailed]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SessionLoginName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_MemoryChangesEvents]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [IsSystem] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_MissingStatsAndPredicates]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_ServerStarts]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SessionLoginName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\n\r\nCREATE TABLE [dbo].[trc_SortAndHashWarnings]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [EventName] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [subclass_name] [nvarchar](128) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [DatabaseID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [NTDomainName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ApplicationName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LoginName] [nvarchar](256) NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [SPID] [int] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [StartTime] [datetime] NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ServerName] [nvarchar](256) NULL\r\n\u00a0\u00a0\u00a0 ) \r\nGO\r\nCREATE CLUSTERED INDEX [idx_EventClass_EventSubClass_ObjectType_DatabaseID] ON [dbo].[temp_trc]\r\n(\r\n[EventClass] ASC,\r\n[EventSubClass] ASC,\r\n[ObjectType] ASC,\r\n[DatabaseID] ASC\r\n) \r\nGO\r\nCREATE STATISTICS [stat_245575913_14_26_27_21_28_3] ON [dbo].[temp_trc]([StartTime], [ServerName], [EventClass], [EventSubClass], [ObjectType], [DatabaseID])\r\nGO\r\nCREATE STATISTICS [stat_245575913_21_14_26] ON [dbo].[temp_trc]([EventSubClass], [StartTime], [ServerName])\r\nGO\r\nCREATE STATISTICS [stat_245575913_21_27_28_3_14] ON [dbo].[temp_trc]([EventSubClass], [EventClass], [ObjectType], [DatabaseID], [StartTime])\r\nGO\r\nCREATE STATISTICS [stat_245575913_27_14] ON [dbo].[temp_trc]([EventClass], [StartTime])\r\nGO\r\nCREATE STATISTICS [stat_245575913_27_21_14_26] ON [dbo].[temp_trc]([EventClass], [EventSubClass], [StartTime], [ServerName])\r\nGO\r\nCREATE STATISTICS [stat_437576597_10_11] ON [dbo].[trc_AlteredObjects]([StartTime], [ServerName])\r\nGO\r\nCREATE STATISTICS [stat_405576483_8_9] ON [dbo].[trc_MissingStatsAndPredicates]([StartTime], [ServerName])\r\nGO\r\n<\/pre>\n<p>After creating the objects, we have to populate the config table:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0INSERT INTO [dbo].[ProcessingTrace_Config]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ([ServerName]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,[TracePath]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,[IsActive])\r\n\u00a0\u00a0\u00a0\u00a0 VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ('remoteServerA'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'C:\\Destination\\remoteServerA'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,1)\r\n<\/pre>\n<p>The table contains 3 columns:<\/p>\n<ol>\n<li><strong>Server name<\/strong> &#8211; the name of the server which is audited<\/li>\n<li>\u00a0<strong>Trace path<\/strong> &#8211; the local folder where the default trace files are stored for the server<\/li>\n<li>\u00a0<strong>isActive <\/strong>&#8211; this flag indicates whether the files should be processed<\/li>\n<\/ol>\n<h2>Importing the default trace files<\/h2>\n<p>The SSIS package takes its configurations from the <b>dbo.ProcessingTrace_Config table<\/b>.<\/p>\n<p>Then the <b>ForEachLoop<\/b> container executes for every record in the config table and it imports each trace file into a scrubbing table called <b>dbo.temp_trc.<\/b><\/p>\n<p>From there the default trace data is queried by event groups and merged into separate tables.<\/p>\n<p>The idea is that since we do not know how often the default trace files are changing for each server, and since the files have a maximum size of 20Mb each (but they may be much smaller), it is actually more efficient to import them and merge them than to write custom logic to check which file was imported and which has not. (The performance overhead of importing 20Mb trace files and using the MERGE script is minimal. I performed a test by populating 1 million rows in each table by using Redgate&#8217;s Data Generator and even in such case the import was fast. )<\/p>\n<p>Technically, the Robocopy script makes sure that the files are stored and updated on our local storage and later on we can schedule the SSIS package to import them at any time we would like.<\/p>\n<p>The events are split in the following categories, and each category is represented by a database table:<\/p>\n<ul>\n<li>FileGrowAndShrink<\/li>\n<li>LogFileAutoGrowAndShrink<\/li>\n<li>ErrorLog<\/li>\n<li>SortAndHashWarnings<\/li>\n<li>MissingStatsAndPredicates<\/li>\n<li>FTSearch<\/li>\n<li>AlteredObjects<\/li>\n<li>CreatedUsersAndLogins<\/li>\n<li>DroppedUsersAndLogins<\/li>\n<li>LoginFailed<\/li>\n<li>ServerStarts<\/li>\n<li>MemoryChangesEvents<\/li>\n<\/ul>\n<p class=\"MsoNormal\">A typical merge operation is this, for sort and hash warnings. (the rest are in the SSIS package that you can download from the link at the bottom of the article.) The <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1931-MergeScripts.html\">scripts can be viewed here.<\/a><\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\tMERGE trc_SortAndHashWarnings AS target\r\n\t\u00a0\u00a0\u00a0 USING \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( SELECT\u00a0\u00a0\u00a0 TE.name AS [EventName] ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 v.subclass_name ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.DatabaseName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.DatabaseID ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.NTDomainName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.ApplicationName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.LoginName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.SPID ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t.StartTime ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 T.ServerName\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0\u00a0 [dbo].[temp_trc] T\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND v.subclass_value = t.EventSubClass\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0\u00a0\u00a0 te.name = 'Hash Warning'\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR te.name = 'Sort Warnings'\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) AS source\r\n\t\u00a0\u00a0\u00a0 ON target.StartTime = source.StartTime\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND target.ServerName = source.ServerName\r\n\t\u00a0\u00a0\u00a0 WHEN NOT MATCHED BY TARGET \r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 (\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EventName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 subclass_name ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DatabaseName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DatabaseID ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NTDomainName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ApplicationName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LoginName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SPID ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 StartTime ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ServerName\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUES\u00a0\u00a0\u00a0 ( EventName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 subclass_name ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DatabaseName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DatabaseID ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NTDomainName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ApplicationName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LoginName ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SPID ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 StartTime ,\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ServerName\r\n\t\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 );\r\n\t<\/pre>\n<p>After extracting and merging the data, the last step is to delete all the files from the filesystem that are older than 1 day.<\/p>\n<p>Note that the scheduling of the Robocopy and the SSIS package is individual and it depends on the systems which are audited. If the default trace files are overwritten often by the source system then we might want to run the Robocopy task and the SSIS package more often.<\/p>\n<p>For the purpose of this article I have set up the SSIS Script Component to delete files older than 1 day.<\/p>\n<p>Here is the C# script for the component:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">#region Namespaces\r\nusing System;\r\nusing System.Data;\r\nusing Microsoft.SqlServer.Dts.Runtime;\r\nusing System.Windows.Forms;\r\nusing System.IO; \/\/ add this\r\n#endregion\r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void Main()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0 string directoryPath = Dts.Variables[\"User::TracePath\"].Value.ToString();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, \"*.trc\");\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (string currFile in oldFiles)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FileInfo currFileInfo = new FileInfo(currFile);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if (currFileInfo.CreationTime &lt; DateTime.Now.AddDays(-1))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 currFileInfo.Delete();\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/\/ TODO: Add your code here\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dts.TaskResult = (int)ScriptResults.Success;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\r\n<\/pre>\n<h1>Conclusions<\/h1>\n<p>This article shows how the default trace logs of a number of SQL Servers can be aggregated and preserved on a\u00a0 centralized auditing server , and then imported into a central auditing database via an SSIS task that filters and merges the results into a number of tables that give a central record of\u00a0 a number of diverse events that are useful for first-line problem-diagnosis, such as database and log File growth and shrinkage,\u00a0 Error Log information,\u00a0 a variety of warnings, notice of created or altered\u00a0 or deleted database objects, users\u00a0 or logins, failed logins, server starts and memory change events.<\/p>\n<p>Now we have all this information in one place for all our servers, we have the opportunity for\u00a0 first-line alerting for a number of signs that things are going wrong, and that we need to reach for our monitoring system to find out more about what is going on within that server, and maybe also database.<\/p>\n<p>With this database in place we can then have a number of data mining possibilities for this data. We&#8217;ll do into more detail about this in a subsequent article.<\/p>\n<div class=\"note\">\n<p class=\"note\">The SSIS package is downloadable from the link at the head of the article, as is the SQL source of the scripts. You can view all the SQL merge scripts via the browser<a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1931-MergeScripts.html\"> by clicking here.<\/a><\/p>\n<\/div>\n<div class=\"scheme--lightest-grey spaced-bottom\">\n  <img decoding=\"async\" class=\"media__image padded--tight\" style=\"float: right; vertical-align: middle;\" src=\"https:\/\/assets.red-gate.com\/external\/SimpleTalk\/database-devops-cog-160.png\"><\/p>\n<div class=\"padded\">\n<p class=\"padded-top--tight\"> <strong>DevOps, Continuous Delivery & Database Lifecycle Management<\/strong><br \/> <a href=\"\/collections\/database-lifecycle-management-patterns-practices-library\/?utm_source=simpletalk&amp;utm_medium=publink&amp;utm_campaign=dlm&amp;utm_content=pandparticle&amp;utm_term=bottombanner\">Go to the Simple Talk library<\/a> to find more articles, or visit <a href=\"https:\/\/www.red-gate.com\/solutions?utm_source=simpletalk&amp;utm_medium=publink&amp;utm_campaign=dlm&amp;utm_content=pandparticle&amp;utm_term=bottombanner&amp;__hstc=81186632.9b3ff88e8dc727f9f1f608ffe1681edd.1467735864337.1482415357732.1482419905332.123&amp;__hssc=81186632.36.1482419905332&amp;__hsfp=3698596899\">www.red-gate.com\/solutions<\/a> for more information on the benefits of extending DevOps practices to SQL Server databases.<\/p>\n<\/div>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"<p>If you have a number of SQL Server instances with versions ranging from 2005 upwards, with a whole host of databases, and you want to be alerted about a number of diverse events that are useful for first-line problem-diagnosis and auditing, then Feodor&#8217;s homebrew solution, using SSIS and Robocopy is likely to be what you&#8217;re looking for.&hellip;<\/p>\n","protected":false},"author":221902,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4168,4170,4179,4150,4151,4306],"coauthors":[11305],"class_list":["post-1753","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database","tag-database-administration","tag-source-control","tag-sql","tag-sql-server","tag-ssis"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221902"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1753"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1753\/revisions"}],"predecessor-version":[{"id":92231,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1753\/revisions\/92231"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1753"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}