-
Title
Using the FileLogMonitorAgent to monitor the SQL Server Error Log -
Description
The Infrastructure cartridge includes File Log Monitor functionality that can be used to create a FIleLogMonitorAgent that is capable of monitoring the SQL Server Error Log for messages outside the scope of the SQL Server Error Log monitoring.
-
Resolution
Consult the Foglight for Infrastructure cartridge guide for the most current instructions on creating a Log Monitor agent.
Requirements
- An Infrastructure agent monitoring the remote host must be configured and monitoring the remote host
- A SQL Server instance must be installed on the server
- For Windows, either a WinRM or DCOM/WMI connection can be used. If using DCOM/WMI then additional registry configuration may be required for command line access to the remote host (as per Knowledgebase Article 4229811)
To create a FileLogMonitorAgent for SQL Server Error Log monitoring
1. Navigate to Infrastructure | Log Monitor in the navigation
2. Click the File Log Monitor button or link
or 
3. In the popup that displays, choose an Agent Manager from the pulldown menu. Enter a new agent name or click the Generate Name checkbox. Click Next.
4. In the new popup panel that appears, click the View button in the Monitored Hosts row
5. In the Agent Property List panel for the LogHosts, click the Edit button
6. Click the Add button to add one or more hosts
7. Fill in the fields including the Hostname and the Host Type (Unix or Windows). The other fields are optional. Then click the Save button when complete.
8. Click the View button in the Log Files row
9. In the Agent Property List panel for the LogFiles, click the Edit button
10. Click the Add button to add a location and filename pattern monitor.
11. Fill in the Directory, Filename Pattern, and File Format Name fields. The Directory should be the location of the SQL Server Error Log on the remote host. The Filename Regular Expression Pattern can include the name of a file or characters in the file name. For SQL Server Error Logs, ERRORLOG will match all Error Log files in the specified folder. Click the Save button when complete.
12. Click the View button in the Patterns row
13. In the Agent Property List panel for the Record Patterns, click the Edit button
14. Click the Add button to add a location and filename pattern monitor.
15. Enter a Regular Expression match pattern to match text in the Error log and a match severity for the alarm. Adding a tag is optional. Click the Save button when complete.
16. Click the View button in the File Format row
17. Click the Edit button in the row for the Default log file format
18. Move the slider to the right to be able to edit the character set value
19. Change the Character Set value from UTF-8 to UTF-16. Click the Save button.
20. Click the Next button
21. Click the Next button after validating the Credentials.
22. Review the configuration and click the Finish button
23. Click the Close button after the agent has been created
25. In Administration | Agent Status, a new FileLogMonitorAgent will now be present
25. After a few minutes, matching messages from the SQL Server Error Log should be seen in the Infrastructure | Log Monitor page
26. Alarms that match the criteria set in the FileLogMonitorAgent configuration will be fired.
The following video details how to monitor a SQL Server Error Log using the Log Monitor
-
Additional Information
The files written by SQL Server are UTF-16. By default, the Log Filter reads as UTF-8, unless you tell it otherwise in the agent. If UTF-8 is applied when reading UCS-2 (or UTF-16) text (e.g. ANSI, or any 8-bit encoding), then every second character is a 0x0. This then breaks the CR-LF to CR-0-LF, which is seen as two line changes (one for CR and one for LF)
Some users may find the SQLDUMPER_ERRORLOG.log file found in the same folder as the ERRORLOG files to be more useful for stack dump reporting purposes.