{"id":856,"date":"2010-04-14T00:00:00","date_gmt":"2010-04-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/consolidating-sql-server-error-logs-from-multiple-instances-using-ssis\/"},"modified":"2021-06-03T16:45:28","modified_gmt":"2021-06-03T16:45:28","slug":"consolidating-sql-server-error-logs-from-multiple-instances-using-ssis","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/consolidating-sql-server-error-logs-from-multiple-instances-using-ssis\/","title":{"rendered":"Consolidating SQL Server Error Logs from Multiple Instances Using SSIS"},"content":{"rendered":"<div id=\"pretty\">\n<p class=\"start\">SQL Server hides a wealth of useful information inside its error log files. Aside from containing the details of specific errors that have occurred, as it name implies it must, there are details of successful and unsuccessful login attempts, the output of DBCC commands, and more. You can even raise your own errors or events to the log, for example to indicate when an &#8216;index rebuild&#8217; process is complete.<\/p>\n<p>Unfortunately, I use the term &#8220;hides&#8221; advisedly. Although it puts a lot of useful information in there, getting it out again in a useful, easy-to-digest format is often a long and painful trial, unless of course you&#8217;re the sort of DBA who cherishes nothing more in life than a few free hours each morning to scroll through hundreds of thousands of SQL Server error log entries, server-by-server, using the Log Viewer application, raptly focused on each and every interesting message.<\/p>\n<p>What would be useful to all other DBAs, I hope, is the means to consolidate the error log entries for all your servers into a central location, and to report on them for there, quickly firing off SQL queries that could tell you instantly, for example:<\/p>\n<ul>\n<li>&#8220;When was the last time X logged in or accessed the server?&#8221;<\/li>\n<li>&#8220;Have there been any prolific login failure attempts by unknown accounts?&#8221;<\/li>\n<li>&#8220;Have any of these specific errors occurred on any of my servers in the last x days&#8221;<\/li>\n<\/ul>\n<p>The solution I offer here uses an SSIS package to pull error log data from the current error log on any number of remote servers, and load the data into a staging table in a central database. From here, any new log records are pushed into a permanent table using the new SQL 2008 <b>MERGE<\/b> command. From here the log records can be diced and sliced with T-SQL, to your heart&#8217;s content.<\/p>\n<h2>Shortcomings of the Standard Error Log Reader<\/h2>\n<p>Before we dive into our custom &#8220;error log consolidation&#8221; solution, let&#8217;s take a brief look at the SQL Server Log Viewer application for SQL Server 2005 and beyond. Figure 1 shows the Log Viewer window, with an applied filter, which searches for messages containing the term &#8220;failed&#8221;, across multiple error logs, on a server instance named <span class=\"STCodeinTextChar\">SRVSAT<\/span> (one that is not much-used).<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL1.JPG\" alt=\"1001-RL1.JPG\" \/><\/p>\n<p class=\"caption\">Figure 1 &#8211; Standard SQL Server 2005 Log Viewer<\/p>\n<p>Notice that, while I&#8217;m specifically search the SQL Server error logs here, I can also choose to view the <span class=\"STBold\">SQL Server Agent<\/span>, <span class=\"STBold\">Database Mail<\/span> and <span class=\"STBold\">Windows NT logs<\/span>. There is also a search function that lets us find specific log entries based on a keyword.<\/p>\n<p>In my filtered search of the SQL Server logs, you can see that there are two entries on this particular server that contain the word &#8220;failed&#8221;; one is a login failure for <span class=\"STBold\">sa<\/span> and the other is a failure in opening a database file. The filter capability is limited, however, in the sense that a filtered search for &#8220;Failed&#8221; or &#8220;Login Failed&#8221; will return login failure results. However, a filtered search on &#8220;Failed Logins&#8221; you will return no results.<\/p>\n<p>Overall, the Log Viewer is a reasonable ad-hoc tool for tracking down errors on a given server. However, the fact that the Log viewer can only be directed at one server at a time makes it a rater tedious means of tracking down specific errors across multiple servers. Also, its filter and searching capabilities certainly do not match what can be achieved with the power of T-SQL.<\/p>\n<h2>Custom Error Log Consolidation<\/h2>\n<p>As noted in the Introduction, this solution is built on SSIS. I first began using SSIS back in 2007, when I developed the &#8220;DBA Repository&#8221; solution, described in full detail in my <a href=\"http:\/\/www.simple-talk.com\/books\/sql-books\/sql-server-tacklebox\/\">SQL Server Tacklebox<\/a> book. It uses an SSIS package to execute a range of useful scripts across all servers under my care, and pull the results back into a central SQL Server database, for reporting and analysis. A fringe benefit is that it&#8217;s been relatively easy to update and adapt this original solution to accommodate new requirements, such as the error log consolidation I describe here.<\/p>\n<p>My central database, <span class=\"STBold\">DBA_Rep<\/span>, is housed on a SQL Server 2008 instance and contains all manner of tables in which to store various bits of useful SQL Server information. In order to support my error log consolidation, I simply needed to add two new tables to this database:<\/p>\n<ul>\n<li><b>StageErrorLog<\/b> &#8211; a temporary holding table for the error log data read from each server.<\/li>\n<li><b>SQL_ErrorLog<\/b> &#8211; the permanent storage table for the error log data<\/li>\n<\/ul>\n<p>The two tables&#8217; schemas are identical except that the permanent table, <span class=\"STBold\">SQL_ErrorLog<\/span>, contains an identity column called <span class=\"STBold\">LogID<\/span>. The code to create these tables is included as part of the code download bundle for this article, along with the package code, sample queries and so on.<\/p>\n<p>As I describe the error log solution, I&#8217;ll focus on those aspect most closely related to gathering and analyzing the log data, rather than on the specific SSIS techniques use, for example, to assign object and string variables in the <span class=\"STBold\">ForEachLoop<\/span> Containers within the packages. The latter is covered in detail in one of my previous <a href=\"http:\/\/www.simple-talk.com\/sql\/ssis\/using-ssis-to-monitor-sql-server-databases-\/\">Simple-Talk<\/a> articles.<\/p>\n<h3>The SSIS Package<\/h3>\n<p>The SSIS package that in a single run polls, consolidates and merges the error logs from any number of servers is really quite simple. A single screen shot, shown in Figure 2, captures the entire package, comprising just five steps.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL2.JPG\" alt=\"1001-RL2.JPG\" \/><\/p>\n<p class=\"caption\">Figure 2 &#8211; Error Log Reader SSIS Package<\/p>\n<p>Let&#8217;s examine each step in turn, with most emphasis on the <span class=\"STBold\">Error Log Reader<\/span> for-each loop, and the <span class=\"STBold\">MERGE<\/span> step.<\/p>\n<h3>Get the Server Names into an Object Variable<\/h3>\n<p>The first task &#8220;Populate ADO Variable For SQL Server Names&#8221; populates a <span class=\"STBold\">SQL_RS<\/span> object variable with the list of the servers from which we will be extracting error log information. In a later step, our package will iterate through this list and populate a string variable, <span class=\"STBold\">SRV_Conn<\/span>. This, in turn, is used to dynamically update the <span class=\"STBold\">ServerName<\/span> property of the <span class=\"STBold\">MultiServer<\/span> Connection Manager object, which controls the the connection to each SQL Server instance we need to interrogate. There is also a second Connection Manager object, <span class=\"STBold\">DBA_Rep<\/span>, which is used to connect to the central repository. This whole step is described in fuller detail in my previous article, <a href=\"http:\/\/www.simple-talk.com\/sql\/ssis\/using-ssis-to-monitor-sql-server-databases-\/\">Using SSIS to monitor SQL Server Databases<\/a>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL3.JPG\" alt=\"1001-RL3.JPG\" \/><\/p>\n<p class=\"caption\">Figure 3 &#8211; Connection Manager Objects<\/p>\n<h3>Truncate the Staging table<\/h3>\n<p>The second step is straightforward and simply issues as single T-SQL command, <span class=\"STBold\">TRUNCATE TABLE StageErrorLog<\/span>, to empty the contents of the <span class=\"STBold\">StageErrorLog<\/span> temporary table, which temporarily stores error log data for each server, as the package executes.<\/p>\n<h3>The Error Log Reader ForEachLoop Container<\/h3>\n<p>The &#8220;Error Log Reader&#8221; <span class=\"STBold\">ForEachLoop<\/span> container forms the heart of the package, and performs two important functions:<\/p>\n<ol>\n<li>It sets the connection string for each server using the two variables <span class=\"STBold\">SQL_RS<\/span> and <span class=\"STBold\">SRV_Conn<\/span><\/li>\n<li>It executes the objects contained within, namely &#8220;<span class=\"STBold\">Create Temp Table for Error Logs<\/span>&#8221; and &#8220;<span class=\"STBold\">Get Error Logs From All Servers<\/span>&#8220;.<\/li>\n<\/ol>\n<p>Figure 4 shows the SSIS Task Editor for the <span class=\"STBold\">Create Temp Table for Error Logs<\/span> task. It uses the <span class=\"STBold\">MultiServer<\/span> Connection Manager to create a temporary table on the remote server, in which to store the error log data during package execution.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL4.JPG\" alt=\"1001-RL4.JPG\" \/><\/p>\n<p class=\"caption\">Figure 4 &#8211; Create Temp Table For Error Logs Task Editor<\/p>\n<p>The T-SQL statement that is executed within this task is shown in Listing 1. First, it drops (if it exists) and creates on the remote server, in <b>TempDB<\/b>, a temp table, <span class=\"STBold\">wErrorLog<\/span> and then populates the table with the output of the <span class=\"STBold\">sp_readerrorlog<\/span> system stored procedure.<\/p>\n<p>The <span class=\"STBold\">sp_readerrorlog<\/span> takes a parameter value, in this case &#8220;0&#8221;, which instructs it to read only the current log file. While it is possible to read historic logs, I decided that only reading the current log would improve performance. I run the task, daily to minimize the risk of losing log data, and run an additional step once a month that interrogates all of the available logs to catch anything that may have been missed. The three actions that I know will initiate a new error log on SQL Server are running <span class=\"STBold\">sp_cycle_erroglog<\/span>, running <span class=\"STBold\">DBCC<\/span> <span class=\"STBold\">ERRORLOG<\/span> and restarting SQL Server.<\/p>\n<div class=\"note\">\n<p class=\"note\"><b>Enumerating through multiple log files<\/b><br \/>\nIf you choose to interrogate all the log files, rather than just the current one, a script that may prove useful is included in my <a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/the-dba-script-thumb\/\">DBA Script Thumb<\/a>.<\/p>\n<\/div>\n<p>The final step in the Read Error Logs script alters the temp table <span class=\"STBold\">wErrorLog<\/span> to add a <span class=\"STBold\">Server<\/span> column and then updates the records with the <span class=\"STBold\">ServerProperty(ServerName)<\/span> function. This step is critical as it allows us to distinguish from which server the error logs came, once the records are all merged into the final destination table (a step I will explain in short order).<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">--Read Error Logs\r\n\u00a0\r\nDECLARE @TSQL NVARCHAR(2000)\r\nIF EXISTS ( SELECT\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempdb.dbo.sysobjects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 id = OBJECT_ID(N'[tempdb].[dbo].[wErrorLog]') ) \r\n\u00a0\u00a0\u00a0 DROP TABLE [tempdb].[dbo].[wErrorLog] ; \r\n\u00a0\r\nCREATE TABLE [tempdb].[dbo].[wErrorLog]\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LogDate DATETIME ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ProcessInfo NVARCHAR(50) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [Text] NVARCHAR(MAX)\r\n\u00a0\u00a0\u00a0 )\r\n\u00a0\r\nINSERT\u00a0 INTO [tempdb].[dbo].[wErrorLog]\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC sp_readerrorlog 0\r\n\u00a0\r\nALTER TABLE [tempdb].[dbo].[wErrorLog]\r\nADD Server VARCHAR(100) NULL ;\r\nGO\r\n\u00a0\r\nUPDATE\u00a0 [tempdb].[dbo].[wErrorLog]\r\nSET\u00a0\u00a0\u00a0\u00a0 Server = CONVERT(VARCHAR(100), SERVERPROPERTY('Servername'))\r\n\u00a0\r\nSELECT\u00a0 *\r\nFROM\u00a0\u00a0\u00a0 [tempdb].[dbo].[wErrorLog]\r\n<\/pre>\n<p class=\"caption\">Listing 1 &#8211; The Read Error Logs script, executed by the <span class=\"STBold\">Create Temp Table for Error Logs<\/span> task<\/p>\n<p>The output of the <span class=\"STBold\">sp_readerrorlog<\/span> stored procedure, executed on a SQL Server 2005 instance, can be seen in Figure 5. Notice that three columns are returned, <span class=\"STBold\">LogDate<\/span>, <span class=\"STBold\">ProcessInfo<\/span> and <span class=\"STBold\">Text<\/span>.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL5.JPG\" alt=\"1001-RL5.JPG\" \/><\/p>\n<p class=\"caption\">Figure 5 &#8211; Output of sp_readerrorlog<\/p>\n<p>When the package executes it will create and populate the <span class=\"STBold\">wErrorLog<\/span> temp table on each of the remote servers. The next step is to pull the data from each of the <span class=\"STBold\">wErrorLog<\/span> temp tables into the staging table, <span class=\"STBold\">StageErrorLog<\/span>, in the <span class=\"STBold\">DBA_Rep<\/span> central repository.<\/p>\n<p>So why do we need temp tables on each remote server, as well as a staging table in the central database? The main reason is to provide the next task in this container, the <span class=\"STBold\">Get Error Logs from All Servers<\/span> data flow task, a valid source table on the remote server from which to select. This task pulls all of the error log data from the remote servers into a single staging table in my central SQL Server 2008 database, <span class=\"STBold\">DBA_REP<\/span>. From there, in the final step in the package (discussed shortly) I can use the <span class=\"STBold\">MERGE<\/span> command to move any new error log records into the <span class=\"STBold\">SQL_ErrorLog<\/span> table. Some of the remotes servers were running SQL 2005 instances, so in fact it was not possible to run the <span class=\"STBold\">MERGE<\/span> command directly against these SQL Server 2005 sources, as it would fail syntactically.<\/p>\n<p>Figure 6 shows the <span class=\"STBold\">Get Error Logs From All Servers<\/span> data flow task with the Server Source (<span class=\"STBold\">wErrorLog<\/span>) and the Repository Destination (<span class=\"STBold\">StageErrorLog<\/span>).<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL6.JPG\" alt=\"1001-RL6.JPG\" \/><\/p>\n<p class=\"caption\">Figure 6 &#8211; Data Flow Task for Error Log<\/p>\n<h3>Merging the Error Log Data into the Central Database<\/h3>\n<p>After all of the remote servers bear their error log fruit into the <span class=\"STBold\">StageErrorLog<\/span> basket all that remains is to merge that data into its final destination, <span class=\"STBold\">SQL_ErrorLog<\/span>, which can then be used for analysis via standard T-SQL queries.<\/p>\n<p>I chose to use the <span class=\"STBold\">MERGE<\/span> statement for a number of reasons. I felt that the typical &#8220;truncate and refresh solution&#8221; is inefficient for large data loads. I could have built an equivalent &#8220;UPSERT&#8221; solution, without <b>MERGE<\/b>, by using <b>IF EXISTS<\/b> constructs to control <b>INSERT<\/b>s and <b>UPDATE<\/b>s, as well as <b>DELETE<\/b>s, but <b>MERGE<\/b> meant I could avoid all of that work. Finally, I just wanted a good excuse to experiment with a new command, and this was a perfect project for that!<\/p>\n<p>I have not found an equivalent of <b>MERGE<\/b> in the SSIS Toolbox. There are some third party tools that will provide this functionality, at a cost, but instead I decided simply to execute the <b>MERGE<\/b> command within an <span class=\"STBold\">Execute SQL<\/span> task.<\/p>\n<p class=\"MsoNormal\">The MERGE command is shown in Listing 2. It looks at matched and unmatched (<span class=\"STBold\">NOT MATCHED<\/span>) records in the target table, <span class=\"STBold\">StageErrorLog<\/span>, based on all four field values (<span class=\"STBold\">LogDate<\/span>, <span class=\"STBold\">ProcessInfo<\/span>, <span class=\"STBold\">Text<\/span> and <span class=\"STBold\">Server<\/span>) and <b>INSERT<\/b>s new records into <span class=\"STBold\">SQL_ErrorLog<\/span>. I know I will never <b>DELETE<\/b> or <b>UPDATE<\/b> the records, so new <b>INSERT<\/b>s are all I am concerned about.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">MERGE SQL_ErrorLog AS Target\r\n\u00a0\u00a0\u00a0 USING StageErrorLog AS Source\r\n\u00a0\u00a0\u00a0 ON ( Source.LogDate = Target.LogDate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Source.ProcessInfo = Target.ProcessInfo\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Source.Text = Target.Text\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Source.Server = Target.Server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 WHEN NOT MATCHED BY TARGET \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN\r\nINSERT\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LogDate ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ProcessInfo ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Text ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) VALUES\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Source.LogDate ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Source.Processinfo ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Source.Text ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Source.Server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) ;\r\n<\/pre>\n<p class=\"caption\">Listing 2 &#8211; merging new log records into the <span class=\"STBold\">SQL_ErrorLog table<\/span><\/p>\n<h3>Executing and Testing the Package<\/h3>\n<p>So that is it, five fairly simple steps. Now all we have to do is execute the package to start consolidating logs for all of the SQL Servers. I would imagine that the list of servers would be limited to critical production environments, where logs must, by mandate, be stored for audit purposes and for problem\/resolution analysis. Figure 7 shows the package executing, with each task turning green as it completes successfully.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL7.JPG\" alt=\"1001-RL7.JPG\" \/><\/p>\n<p class=\"caption\">Figure 7 &#8211; Executing the Error Log Reader Package<\/p>\n<p>It is quite easy to determine if the package is working as expected. If you have <span class=\"STBold\">Audit Logins<\/span> enabled, you can simply, on a database in your test environment, deliberately generate failed login attempts. You can then rerun the package and make sure that only those new records were inserted in the <span class=\"STBold\">SQL_ErrorLog<\/span> table. In my test environment, I have two servers. On the last execution of the package, I had 951 records in the <span class=\"STBold\">SQL_ErrorLog<\/span> table, 778 for <span class=\"STBold\">Server1<\/span> and 173 for <span class=\"STBold\">Server2<\/span>, which the following query showed.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 COUNT(*) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Server\r\nFROM\u00a0\u00a0\u00a0 SQL_ErrorLog\r\nGROUP BY Server\r\n<\/pre>\n<p class=\"MsoNormal\">After a couple of failed login attempts, using SQL authenticated users &#8220;sa&#8221; and &#8220;bad user&#8221;, I reran the package and the row counts increased as expected by only a handful of records per server making the new totals 779 and 178 for <span class=\"STBold\">Server1<\/span> and <span class=\"STBold\">Server2<\/span> respectively. I verified that the failed logins were in the <span class=\"STBold\">SQL_ErrorLog<\/span> table by executing the following query:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 Text ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(Text) Number_Of_Attempts\r\nFROM\u00a0\u00a0\u00a0 SQL_ErrorLog\r\nWHERE\u00a0\u00a0 Text LIKE '%failed%'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND ProcessInfo = 'LOGON'\r\nGROUP BY Text\r\n<\/pre>\n<p>As Figure 8 shows, &#8220;bad user&#8221; tried to login and I captured it via the package execution. The important part is that only the news records were inserted, so the <span class=\"STBold\">MERGE<\/span> statement worked as anticipated.<\/p>\n<p class=\"illustration\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL8.JPG\" alt=\"1001-RL8.JPG\" \/><\/p>\n<p class=\"caption\">Figure 8 &#8211; Failed Login Attempts From SQL_ErrorLog table.<\/p>\n<h2>Querying the Error Log Data<\/h2>\n<p>With the records loaded and the package tested, it is time to get creative with the T-SQL analysis of the data. If you know the specific errors (or log entries I should say) for which you are looking, such as DBCC or memory or deadlocks, then you can simply use a <span class=\"STBold\">LIKE<\/span> or <span class=\"STBold\">EXISTS<\/span> statement as in the following two queries that scan for &#8220;CPU&#8221; and &#8220;MEMORY&#8221;. Of course you can do this with one query, but I broke them out for emphasis.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT\u00a0 LogDate ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Text\r\nFROM\u00a0\u00a0\u00a0 SQL_ErrorLog\r\nWHERE\u00a0\u00a0 Text LIKE '%memory%'\r\n\u00a0\r\nSELECT\u00a0 LogDate ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Text\r\nFROM\u00a0\u00a0\u00a0 SQL_ErrorLog\r\nWHERE\u00a0\u00a0 Text LIKE '%CPU%'\r\n<\/pre>\n<p>The results are already proving valuable as you can see from the highlighted rows regarding memory, In Figure 9. Apparently I am not using all the memory that I could do, and SQL Server 2008 is helping me by telling me what to do to increase the amount of memory SQL Server can utilize. Thanks, Error Log.<\/p>\n<p class=\"illustraion\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/1001-RL9.JPG\" alt=\"1001-RL9.JPG\" \/><\/p>\n<p class=\"caption\">Figure 9 &#8211; Memory- and CPU-related data in SQL_ErrorLog<\/p>\n<p>Of course, as DBAs, I will leave it to you to be to be creative in the development of useful queries for your environment. Heck, you can even add a Full Text index if you like. In fact, if you notice in the code download, the <span class=\"STBold\">SQL_ErrorLog<\/span> DDL adds an identity column called <span class=\"STBold\">LogID<\/span> to facilitate the creation of such a Full Text index. That process is an article in itself and one which I will tackle next.<\/p>\n<h2>Conclusion (with a few small caveats)<\/h2>\n<p>I do not like putting the caveat section so close the end of the article but you may have guessed by now that there are a few and I will just note them really quickly and move on to the conclusion before you have a chance to utter &#8220;Ah Shucks&#8221;. First, this solution only pulls error logs from SQL Server 2005 and higher, due to the different way the <span class=\"STBold\">sp_readerrorlog<\/span> data is returned in earlier versions. Second, the solution does not pull SQL Agent logs or Mail Logs, like the Log Viewer application does. Finally, the package and <b>MERGE<\/b> statement therein requires SQL Server 2008 to house the <b>DBA_Rep<\/b> database. Not too horrible after all, I hope. If I were to have said it only works on SQL Server 2000 that might have been a problem.<\/p>\n<p>I hope this error log consolidation solution, based on SSIS, and making use of the new SQL 2008 <b>MERGE<\/b> command, proves useful to many DBAs. If it also saves you lots of time and money then great; I did my small part for the economy.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server hides a lot of very useful information in its error log files. Unfortunately, the process of hunting through all these logs, file-by-file, server-by-server, can cause a problem. Rodney Landrum offers a solution which will allow you to pull error log records from multiple servers into a central database, for analysis and reporting with T-SQL.&hellip;<\/p>\n","protected":false},"author":221800,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4168,4967,5161,4150,4151,4306,4183],"coauthors":[11298],"class_list":["post-856","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-database","tag-rodney-landrum","tag-server-error-logs","tag-sql","tag-sql-server","tag-ssis","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/856","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\/221800"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=856"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/856\/revisions"}],"predecessor-version":[{"id":74820,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/856\/revisions\/74820"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=856"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=856"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=856"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=856"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}