The table to store all error log information on the monitoring server is Tbl_SQLErrorLogReport, which resides in the tempdb database. This table can accommodate both SQL Server 2005 and SQL Server 2000 error log formats. If this table generates any entries, the Usp_SQLErrorLogReportServer stored procedure will send an email notification to a defined recipient, with the involved client server’s name in the header and error messages in the body of the message. For email notification to work, you must have correctly configured database mail on the central monitoring server. Figure 1 shows an email report sent to the DBA group and received by a Lotus Notes 7.0 client.
Mass Operations
Manually setting up the client processes on a large number of SQL Server systems is difficult. In addition, modifying the client processes on hundreds of servers (e.g., adding a new keyword in the inclusion files) is equally tedious. I created the stored procedure Usp_Mass_Operation_On_All_Clients, which WebListing 7 contains, to handle mass operations onSQL Server clients. This procedure includes fourinput parameters: @InputFileFullPath, @HasWritePermissionOnClient, @RootPathOnMonitoringServer, and @RootPathOnClient.
The parameter @InputFileFullPath must be a file containing valid T-SQL statements. The parameter @HasWritePermissionOnClient is a bit value that works solely with DOS commands, such as xcopy. When the parameter’s value is 1, the SQL Server service account on the central monitoring server has appropriate permission on the remote client servers. If the value is 0, the Mssqlserver account doesn’t have permission to run command shell commands on client servers. In this case, the stored procedure generates a result that can be copied and pasted into a batch file. You can then run the batch file after you log in to the client server via a proper user account. The parameter @RootPathOnMonitoringServer is the source file path; @RootPathOnClient is the destination file path. They are identical by default, but they don’t have to be.
To begin, create the SQLErrorReport folder under the central monitoring server’s C root and copy findstr_incl.txt and findstr_excl.txt into this folder. The SQLErrorReport folder and its contents will be rolled out to all the client servers.
You can use the same stored procedure to also create the client-side jobs. Open the SQL Editor on the central monitoring server and run the following code (you need remote write privileges to execute this code):
EXEC master.dbo.Usp_Mass_Operation_On_All_
Clients @InputFileFullPath = 'C:\Client_
job.sql', @HasWritePermissionOnClient =
1, @RootPathOnMonitoringServer = NULL, @
RootPathOnClient = NULL
This code runs the client_job.sql file to create the job on all clients, and it copies the SQLErrorReport directory and its files to the same locations on all clients. Note that the client_job.sql file is the same file as Web Listing 4. Also note that the login account and password used with the OSQL utility in Web Listing 7 must be replaced with appropriate login credentials.
If you want to add, remove, or modify a search string in the inclusion file on all clients, but the central monitoring server’s SQL Server service account isn’t able to write to clients, you can make the change in the findstr_incl.txt file on the central server first, then save the result from running the following code to, for example, a file named Update_search_strings.bat.
EXEC master.dbo.Usp_Mass_Operation_On_
All_Clients @InputFileFullPath = NULL,
@HasWritePermissionOnClient = 0, @
RootPathOnMonitoringServer = NULL, @
RootPathOnClient = NULL
Figure 2 shows the results of running this code. Run the batch file under an account that can write to the client servers, such as a domain user account that is a member of the local administrator group on the client servers. Figure 3 shows the results of running the batch file.
You can now automatically create the client stored procedure Usp_SQLErrorLogReportClient on multiple SQL Server systems. You need to save the stored procedure (which Web Listing 3 contains) as Usp_SQLErrorLogReportClient.sql. Then, run the following T-SQL statement to simultaneously create the stored procedure on all clients:
EXEC master.dbo.Usp_Mass_Operation_On_
All_Clients @InputFileFullPath = 'C: temp\ Usp_SQLErrorLogReportClient.sql',
@HasWritePermissionOnClient = NULL, @
RootPathOnMonitoringServer = NULL, @
RootPathOnClient = NULL
To create the client-side login, run the following code:
EXEC master.dbo.Usp_Mass_Operation_On_All_
Clients ‘C:\temp\Create_OdbcpingLogin_
account.sql’, NULL, NULL, NULL
The code for the Create_OdbcpingLogin_account.sql file is as follows:
IF NOT EXISTS (SELECT * FROM master.
dbo.syslogins where [loginname] =
N'OdbcpingLogin')
EXEC master.dbo.sp_addlogin
N'OdbcpingLogin', N'Pswd2008';
Refine As You Go
Although my solution can save DBAs a tremendous amount of time and labor, it shouldn’t prevent you from manually checking your error logs from time to time. Regularly investigating logs by hand will help you discover events that have been missed by the automated process, so that you can update your error checking processes in a dynamic database environment. Many factors can affect whether your automated error checking process works well. When rescheduling jobs, make sure the central monitoring server job runs after the completion of client-side jobs. In addition, if you modify the inclusion or exclusion files, be sure to watch for white spaces. You don’t typically see the hidden blanks in WordPad or Notepad but they can cause unexplained problems. Finally, remember that each line in the final report represents a problem or important event that needs further investigation. Although my technique works out of the box, it will be most effective if you continue to refine it for your environment.