Collect Logs for the Microsoft SQL Server App

Step 1. Install a collector for Windows

If you have not already done so, install and configure an installed collector for Windows, using the instructions in Install a Collector on Windows.

Step 2. Configure a local file source

The ERRORLOG typically uses UTF-16LE encoding, but you should confirm the file encoding used on your SQL Server for this configuration.

To check your ERRORLOG encoding on a Mac, use the following commands:

MacBookPro:Downloads npande$ file -I ERRORLOG*ERRORLOG*
ERRORLOGERRORLOG: text/plain; charset=utf-16le
ERRORLOG.1: text/plain; charset=utf-16le
ERRORLOG.2: text/plain; charset=utf-16le

You can also check ERRORLOG encoding with Notepad ++.

To configure a local file source to ingest the ERRORLOG, using the instructions in Local File Source, with the following differences:

1. In Step 4, for the File Path, use something like, C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\ERRORLOG, depending on your system.
2. Set the Source Category to DB/MSSQL/ERROR.
3. In Step 5, for Encoding select UTF-16LE.

Step 3. Download scripts and configuration file

Download these files:

• MSSQL_Perf_Monitor_Script.vbs
• MSSQL_Perf_Monitor.cfg
• MSSQL_Monitor.bat

Copy the files to your Windows system into a folder that you create at C:\MSSQL_Perf_Monitor_Script.

Step 4. Configure database login parameters

Make the following updates to the MSSQL_Perf_Monitor.cfg file: 

In the [dbLogin] section, supply values for each parameter:

Server=CompleteServerName
Database=DatabaseName

For example:

Server=WIN-Server-prod-01
Database=master

Step 5. Enable and disable queries

The [queries] section of MSSQL_Perf_Monitor.cfg contains nine SQL queries.  

To comment out a query, prefix it with a pound sign (#).

To add a query, type or paste it into the file in this format:

SQLQuery = Your query on a single line (no newlines)

Step 6. Test the scripts

To test the scripts:

1. Go to the command prompt and execute the .bat file:

   C:\MSSQL_Perf_Monitor_Script>MSSQL_Monitor.bat
   

2. The .bat file executes the .vbs file. The VB script runs the SQL queries specified  in the .cfg file. 
3. The output of each SQL query result is displayed on the console.

Once the script runs correctly, configure the Sumo script source, as desribed in the following section.

Step 7. Configure the script source

1. In Sumo Logic, go to Manage Data > Collection > Collection.
2. Find the name of the installed collector to which you'd like to add a Source. Click Add... then choose Add Source from the pop-up menu.
3. Select Script for the Source type.
   
4. For Name enter any name you like, for instance SQL Server Script.
5. The Description is optional.
6. For Source Category, enter the desired category. It can be any value you like, for example  DB/MSSQL/DBQueryScript.
7. For Frequency, select desired frequency. 
8. For Specify a timeout for your command, select a value that is long enough that long-running queries can complete.
9. For Command, select Windows Script.
10. For Script, select Type a path to the script to execute, then enter: C:\MSSQL_Perf_Monitor_Script\MSSQL_Monitor.bat.
11. For Working Directory, enter: C:\MSSQL_Perf_Monitor_Script.
12. Click Save.

Sample Logs

2018-05-02 00:05:09,876 Logon       Login succeeded for user 'bob'. Connection made using SQL Server authentication. [CLIENT: 242.95.35.122]

2018-00-02 00:00:33,803 spid884s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

2018-00-02 00:00:09,521 spid500     Could not allocate space for object 'dbo.SORT temporary run storage:  262898620895351' in database 'cache' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.