Submit a ticketCall us

Solarwinds & Cisco Live! Barcelona
Join us from the 29th of January to the 2nd of February at Cisco Live 2018 in Barcelona, where we will continue to show how monitoring the network with SolarWinds will keep you ahead of the game. At our booth (WEP 1A), we will demonstrate how SolarWinds network solutions can help. As a bonus, we are also hosting a pre-event webinar - Blame the Network, Hybrid IT Edition with our SolarWinds Head Geek™, Patrick Hubbard on January 24th - GMT (UTC+0): 10:00 a.m. to 11:00 a.m. There's still time to RSVP.

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

Tags

Classifications

Public