Troubleshoot the Splunk Add-on for Microsoft SQL Server¶

General troubleshooting¶

For helpful troubleshooting tips that you can apply to all add-ons, see Troubleshoot add-ons in Splunk Add-ons. For additional resources, see Support and resource links for add-ons in Splunk Add-ons.

Verify data collection¶

To check that the Splunk platform is collecting the data that you expect, use this search command to list the indexed source types:

| metadata index=main type=sourcetypes | fields sourcetype

You can find all possible source types contributed by this add-on listed on the source types page. If your search results are missing a source type that you intended to collect, verify that you have enabled that source type in %SPLUNK_HOME%\etc\apps\Splunk_TA_microsoft-sqlserver\local\inputs.conf, %SPLUNK_HOME%\etc\apps\splunk_app_db_connect\local\inputs.conf.

Determine event sources¶

The add-on adds two fields at search time for each event that can be useful in determining the event source.

Field	How to determine the event source
`sqlserver_instance_name`	For events collected through file or performance monitoring, use the SQL Server instance name. For example: `default_instance` For events collected through Splunk DB Connect, use “DBConn:”+ connection_name. For example: `DBConn:sqlserver_default_connection`
`sqlserver_full_instance_name`	For events collected through file or performance monitoring, use host_name + “/”+sqlserver_instance_name. For example: `DBConn:sqlserver_default_connection` For events collected through Splunk DB Connect, usehost_name +”/”+”DBConn:”+ connection_name. For example: `WIN-J0NE2C7KVR9/DBconn:sqlserver_default_connection`

Eliminate white spaces in events¶

Some events from Microsoft SQL Server data contain extra white spaces caused by fixed sizes in SQL Server and the handling logic in Splunk DB Connect. Trim the white spaces using the search in this section:

sourcetype = [insert source type] | rex mode=sed "s/\s{2,}//g"

Line breaking and missing field issues for `mssql:audit` and `mssql:trclog` data collected with DB Connect v1¶

Due to limitations in Splunk DB Connect v1, you might experience line breaking issues or missing fields in your audit and trace logs. You can work around the issue by adding SHOULD_LINEMERGE = true in your mssql:audit and mssql:trclog stanzas in %SPLUNK_HOME%\etc\apps\dbx\local\props.conf.

Examine the buffer pool¶

If you want to see what objects and indexes are in the buffer pool, run the following SQL statements in DB query in Splunk DB Connect.

Note

Query statements in the DB connect conf files are limited to 128 characters, so this query must be run in Splunk Web.

select

       count(\*)as cached\_pages\_count,

       obj.name as objectname,

       ind.name as indexname,

       obj.index\_id as indexid

from sys.dm\_os\_buffer\_descriptors as bd

    inner join

    (

        select       object\_id as objectid,

                           object\_name(object\_id) as name,

                           index\_id,allocation\_unit\_id

        from sys.allocation\_units as au

            inner join sys.partitions as p

                on au.container\_id = p.hobt\_id

                    and (au.type = 1 or au.type = 3)

        union all

        select       object\_id as objectid,

                           object\_name(object\_id) as name,

                           index\_id,allocation\_unit\_id

        from sys.allocation\_units as au

            inner join sys.partitions as p

                on au.container\_id = p.partition\_id

                    and au.type = 2

    ) as obj

        on bd.allocation\_unit\_id = obj.allocation\_unit\_id

left outer join sys.indexes ind

  on  obj.objectid = ind.object\_id

 and  obj.index\_id = ind.index\_id

where bd.database\_id = db\_id()

  and bd.page\_type in ('data\_page', 'index\_page')

group by obj.name, ind.name, obj.index\_id

order by cached\_pages\_count desc

Understand the missing records_affected field¶

If you are using Microsoft SQL Server 2008 R2, the add-on does not provide the records_affected field for events in the source type mssql:execution:dm_exec_query_stats. The add-on cannot supply this field because the add-on derives this field from the last_rows column in the table sys.dm_exec_query_stats, but Microsoft SQL Server 2008 R2 does not include this column. The absence of this field does not affect the mapping of any other fields.