Reports time out on large sites due to SQL Command Timeout

This article applies to:

• Trustwave SPE (MailMarshal SPE) 3.X, 4.X

Symptoms

• When running SPE reports (Customer Console or Administration Console), the SQL command times out

Cause:

• The default database timeout for reports from the Admin Console is 60 seconds
• The default database timeout for reports from the Customer Console is 300 seconds
• When SPE runs the report query against SQL server, it will only wait for this default period for the data to return. If the data is not returned within this period, a timeout occurs.
• This typically occurs when the SQL server does not have the resources to return the requested data in the default period.
• Increasing the timeout gives the SQL server more time to return the data.

Resolution:

The underlying root cause for most report timeouts is the hardware and resources allocated to the SQL server. Allocating more RAM, faster disks and/or faster processors will reduce the time required for SQL to return the data to SPE, reducing the timeout occurrences.

• You can increase the timeout by setting a registry entry.
• The same entry affects both Admin Console and Customer Console report timeouts as well as other database operations.
• To adjust the timeout, set the following Registry key on the servers running the Web Console and the Marshal Interface Agent (For SPE versions below 3.6, the servers running Web Console and Reporting Agent):

Path: HKey_Local_Machine\Software\Marshal\MailMarshalSPE\
or HKey_Local_Machine\Software\WOW6432Node\Marshal\MailMarshalSPE\
Key: DBCommandTimeout
Type: DWORD
Value: The DB command timeout in seconds (decimal)

Warning: Use due care when editing the Registry.

After making the required registry changes, you must do the following for the settings to take effect:

1. Restart the Marshal Interface Agent Service (Reporting Agent Service in earlier versions)
2. Restart the IIS Web Service
3. Recycle the SPE Application Pool(s)

Note:

• See also Q13891, Customer Console reports or import operations time out
• The default was 30 seconds in SPE 2.0.3 and SPE 2.1.0. In SPE 2.1.1+, the default command timeout was increased to 60 seconds and the registry override was introduced.