Error: SQL Timeout Expired

Symptom

Error:

Log Viewer is currently unavailable. DotNetNuke.Services.Exceptions.ModuleLoadException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlInternalConnection.OnError( SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

Possible Cause

Some SQL tables have grown too large, specifically EventLogSiteLog, and ScheduleHistory.

Solution

  1. Create a backup of your database before running any SQL queries.
  2. Go to Persona Bar > Settings > SQL Console.
  3. Check the sizes of EventLogSiteLog, and ScheduleHistory.
    1. Run the following SQL script:
      
      SELECT t.NAME AS TableName
      	,p.rows AS RowCounts
      	,SUM(a.total_pages) * 8 AS TotalSpaceKB
      	,SUM(a.used_pages) * 8 AS UsedSpaceKB
      	,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
      FROM sys.tables t
      INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
      INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
      	AND i.index_id = p.index_id
      INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
      LEFT JOIN sys.schemas s ON t.schema_id = s.schema_id
      WHERE t.NAME NOT LIKE 'dt%'
      	AND t.is_ms_shipped = 0
      	AND i.OBJECT_ID > 255
      	AND t.name in ('ScheduleHistory', 'EventLog', 'SiteLog')
      GROUP BY t.NAME
      	,s.NAME
      	,p.Rows
      ORDER BY TotalSpaceKB DESC
                                          
    2. In the result, note the RowCounts values for EventLogSiteLog, and ScheduleHistory.
  4. Clear EventLog.
    1. Run the following SQL script:
      
      DELETE TOP (1000)
      FROM EventLog;
                                          
      Note: To clear EventLog, replace 1000 with the RowCounts value. To reduce the size of EventLog (instead of clearing it), replace 1000 with the number of rows you want to delete.
  5. Delete the SiteLog and ScheduleHistory tables.
    1. Run the following SQL script:
      
      TRUNCATE TABLE SiteLog;
      TRUNCATE TABLE ScheduleHistory;
                                          
  6. Restart the application to allow the changes to take effect.
  7. To test, try to access the Admin Logs.

Comments

0 comments

Please sign in to leave a comment.