Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register 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