Troubleshooting: Reporting Realm Times Out When Generating Reports

Version Affected: All
 
 

Overview

After previously generating reports successfully, a SecureAuth Reporting Realm fails to generate a report and returns the following error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
 
 

Cause

The Reporting Realm's Logging database stores report data in the dbo.Log table. If that table does not have an index on its EventID and Timestamp columns, the stored procedure that generates reports must scan the entire table to retrieve results. On a large dbo.Log table, this can take long enough to trigger the timeout.
 
 

Resolution

These steps assume a Logging database already exists for the Reporting Realm, with a dbo.Log table that stores the realm's report data.

1. Open the Logging database using SQL Server Management Studio (SSMS).
2. In the Object Explorer panel, expand the Logging database, then Tables, then dbo.Log, then Indexes.
3. Check whether an index named IDX_NC_Log_EventID (Non-Unique, Non-Clustered) already exists. If it exists, contact SecureAuth Support for further help — this resolution does not apply. If it does not exist, continue with the remaining steps.
4. Click New Query in SSMS and run the following SQL against the Logging database:

USE Logging
CREATE NONCLUSTERED INDEX [IDX_NC_Log_EventID] ON [dbo].[Log]
(
[EventID] ASC,
[Timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

5. Right-click Indexes and select Refresh to confirm the new index appears.
6. Right-click Indexes again and select Rebuild All to rebuild the dbo.Log table's indexes.

Special Considerations

Rebuilding all indexes on the dbo.Log table can cause a temporary spike in CPU and memory usage on the database server, especially if the table holds a large number of records. Coordinate this step with your change management process so it can be scheduled appropriately.


 

SecureAuth Knowledge Base Articles provide information based on specific use cases and may not apply to all appliances or configurations. Be advised that these instructions could cause harm to the environment if not followed correctly or if they do not apply to the current use case.

Customers are responsible for their own due diligence prior to utilizing this information and agree that SecureAuth is not liable for any issues caused by misconfiguration directly or indirectly related to SecureAuth products.