Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Network Performance Monitor (NPM) > Database maintenance time out after long query and does not complete

Database maintenance time out after long query and does not complete

Updated March 7,  2017

Overview

Occasionally, the scheduled database maintenance does not complete successfully. The swdebugmaintenance.log may have a complex SQL query which can run for about a minute or two, and then times out (example shown below, exact query may differ):

DEBUG SolarWinds.Orion.Common.SqlHelper - SQL: 
;WITH TablesWithLobs
AS
(
    SELECT DISTINCT
        TAB.object_id AS TabID
    FROM sys.tables AS TAB
    INNER JOIN sys.columns AS COLS
    ON
        COLS.object_id = TAB.object_id
    WHERE
        (COLS.max_length = -1 OR COLS.system_type_id IN (34,35,99))
),
IndexesWithLobs 
AS
(
    SELECT DISTINCT
        IDXCOLS.object_id,
        IDXCOLS.index_id
    FROM sys.index_columns AS IDXCOLS
    INNER JOIN sys.columns AS COLS
    ON
        COLS.object_id = IDXCOLS.object_id AND
        COLS.column_id = IDXCOLS.column_id
    WHERE
        (COLS.max_length = -1 OR COLS.system_type_id IN (34,35,99))
),
ClusteredIndexesWithLobs
AS
(
    SELECT 
        IDX.object_id,
        IDX.index_id
    FROM sys.indexes AS IDX
    INNER JOIN TablesWithLobs AS TABS
    ON
        TABS.TabID = IDX.object_id
    WHERE 
        TABS.TabID = IDX.object_id AND
        IDX.type = 1
),
AllIndexesWithLobs
AS
(
    SELECT object_id, index_id FROM IndexesWithLobs 
    UNION
    SELECT object_id, index_id FROM ClusteredIndexesWithLobs
)
SELECT
    OBJECT_NAME(IDXSTAT.object_id) AS TableName,
    IDX.name AS IndexName,
    IDXSTAT.avg_fragmentation_in_percent AS Fragmentation,
    IDXSTAT.record_count AS Rows,
    CASE WHEN LOBS.object_id IS NULL THEN 0 ELSE 1 END AS ContainLobColumn
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,NULL,NULL,'Sampled') AS IDXSTAT
INNER JOIN sys.indexes AS IDX
ON
    IDX.object_id = IDXSTAT.object_id AND
    IDX.index_id = IDXSTAT.index_id
LEFT JOIN AllIndexesWithLobs AS LOBS
ON
    LOBS.object_id = IDX.object_id AND
    LOBS.index_id = IDX.index_id
WHERE
    IDXSTAT.index_id <> 0    -- no heaps

Roughly a minute afterwards, the following error is displayed:

ERROR DatabaseMaintenanceGui.Program - Database Maintenance Error.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Environment

All Orion Platform products

Cause 

The error indicates a. SQL command timeout. In this instance, a portion of the defragmentation process of the database maintenance is failing.

Resolution

  1. Edit the %SYSTEMDATA%\Program Files (x86)\SolarWinds\Orion\SWNetPerfMon.DB file using a text editor.
  2. Set the last variable in the file to 0.
    ! SqlCommand.CommandTimeout in seconds SqlCommandTimeout=0
  3. Restart all Orion services for the changes to take effect.

 

Last modified
22:23, 26 Mar 2017

Tags

Classifications

Public