Microsoft SQL Server Logs

The Microsoft SQL Server error logs includes backup and restore operations, batch commands, or other scripts and processes (see more here). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, audit failures like failed logins, or other server-level error messages.

We’ll show you to use the free and open source Nxlog tool to retrieve these logs and send them to Loggly. You can also use Snare or Syslog-NG for Windows.

This guide was written for Windows Vista or later in 64-bit, the latest version of nxlog in the default installation directory, SQL Server 2008 R2, and can send TCP events out on port 514. It was tested on Amazon EC2 with Windows_Server-2008-R2_SP1-English-64Bit-SQL_2008_R2_SP2_Express-2013.11.13 (ami-1653c826). For alternatives, please see the Advanced Options section.

  1. Install Nxlog
    Install nxlog using this guide if you haven’t already. SQL Server will send error logs to the Windows Event Log by default, where Nxlog reads them using the im_msvistalog module, and then converts them to JSON so Loggly can parse and index the fields.
  2. Verify
    Search for SQL Server logs in Loggly using the app name:

    syslog.appName:"MSSQLSERVER"

    SQL Server Logs

    SQL Server Logs Advanced Options

    • Syslog-NG for Windows – with commercial support from Balabit
    • Search or post your own Microsoft SQL server logs, server database, or server error log file questions in the community forum.

    Troubleshooting SQL Server Logs

    If you don’t see any data show up in the verification step, then check for these common problems.

    • Verify there are current and ongoing SQLServer logs in the Windows Event Viewer. You can usually find them under Windows Logs > Application > MSSQLSERVER.
    • Check our guide on Troubleshooting Nxlog
    • Search or post your own SQL server error logging or transaction log questions in the community forum.
    Thanks for the feedback! We'll use it to improve our support documentation.


Top