Reporting Realm Timing Out

Applicable SecureAuth IdP Versions: All Versions

Issue: Client is unable to generate reports on a previously-working Reporting Realm, and is now getting the following error message:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Cause: It is likely that the logging database has not been properly indexed to efficiently retrieve the results requested by the stored procedure as written in our Knowledge Base.

Troubleshooting Steps:
Note: the following steps assume that the Logging database was created using the steps provided in the aforementioned Knowledge Base documentation.

1. Open the database using SQL Server Management Studio (SSMS).

2. Expand and navigate to the following in the Object Explorer window, which typically opens on the left-hand side of the SSMS interface:

• Logging database
• Tables folder
• dbo.Log table
• Indexes folder.

3. Look for the following entry in the Indexes folder: IDX_NC_Log_EventID (Non-Unique, Non-Clustered)

If the entry exists, then please contact Support to look into this further. However, if the entry does not exist:

3a. Click on the New Query button located on the top portion of the SSMS interface

3b. Paste the following SQL code in the newly-opened Query editor:

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

3c. Click on the Execute button to create this new index.

3d. Right-click on the Indexes table referenced in Step 2, then click the Refresh option to ensure that the new index created is displayed.

3e. Right-click again on the Indexes table, then click the Rebuild All option to properly index the dbo.Log table.

Please note that, depending on the number of records stored in the dbo.Log table, performing this last step can create a surge in the CPU and memory usage of the database server. Please make the necessary accommodations with your change management team to ensure that the implementation is as seamless as possible.