Database and Performance Issues

OptimiDoc stores jobs, copies, users, device configuration and audit data in a database, and reads or writes it on nearly every page load and device interaction. This article covers the database and performance issues that appear as a deployment grows, and — just as importantly — the scale features that 26.x already ships (server-side pagination, streaming exports, the indexed job/copy schema, built-in database optimisation and slow-query monitoring) so you know what you should not need to build yourself. Menu paths refer to the React web console.

How OptimiDoc Uses the Database

• OptimiDoc uses two Entity Framework contexts: the main OptimiDocDbContext (default catalog optimidocX) for jobs, users, devices and audit data, and a separate QuotaDbContext (default catalog optimidocQ) that holds quota data. The presence of the quota database is what enables quotas — and it must be backed up alongside the main database (see cause 8).

• Every query runs through an EF6 slow-query interceptor that times it and logs anything slow to a dedicated log. This is covered in depth in the child article, Slow Query Monitoring.

• Both contexts use a command timeout of 120 seconds by default, raised for specific long operations (see cause 5).

• The job list, reports and activity log use server-side pagination and streaming exports — there is no longer a 50 000-row cap that silently truncated large result sets — so a large history is not, by itself, a reason for the UI to fail.

Symptoms

• The web interface loads slowly or times out.

• Device panel operations lag because API calls take longer than expected.

• Logs show Timeout expired or Connection pool exhausted.

• The server becomes unresponsive during peak periods.

• The UI hangs while background delivery keeps working — and the logs show nothing (a specific signature; see cause 6).

• Reports or exports take a long time on very large date ranges.

Common Causes and Solutions

1. Large Job and Copy History

Cause: Over time the Jobs and Copies tables accumulate millions of rows; queries against them slow down, particularly reports and the activity log.

Diagnostic steps:

1. Check database size under Diagnostics > Maintenance > Database Info (shows the server, database name and size in MB).

2. Note how far back the job history extends in All Jobs.

3. Watch the slow-query log for queries against Jobs / Copies (see Slow Query Monitoring).

Solution:

• Trim history with Diagnostics > Maintenance > Clear Old Jobs, which deletes jobs older than a chosen age (default 365 days) after a confirmation prompt. (There is no "Data Retention" settings page — history cleanup is performed from Maintenance.)

• The bulk delete deliberately runs with a 20-minute command timeout, so an initial cleanup of a very large table may take a while — let it finish.

• The schema already ships indexes tuned for this scale (cause 3), so growth alone should not cripple queries.

2. SQL Server LocalDB Limitations

Cause: OptimiDoc can run on SQL Server LocalDB for ease of installation, but LocalDB caps the database at 10 GB, offers limited concurrency and performs no background maintenance. Busy production deployments outgrow it.

Diagnostic steps:

1. Check the connection string / engine in web.config, and the database size under Diagnostics > Maintenance > Database Info.

2. Watch for "database is full" / "max size reached" errors in the logs.

Solution:

• Migrate to a full SQL Server instance. SQL Server Express is free but shares the same 10 GB limit; Standard/Enterprise removes it and adds proper concurrency and maintenance.

• Follow LocalDB to SQL Server. Remember to migrate the quota database too if quotas are in use.

3. Index Maintenance

Cause: After a large migration, or on a heavily churned database, indexes can become fragmented and statistics stale, slowing queries.

What OptimiDoc ships and provides:

• The schema includes indexes added specifically for scale — on the Jobs table (IX_Jobs_Received, IX_Jobs_State, IX_Jobs_Title) and the Copies table (IX_Copies_State, IX_Copies_Finished, plus a covering IX_Copies_Finished_Covering). If these are missing, confirm all database migrations have been applied.

• OptimiDoc has a built-in Database Optimization action — you do not need SQL Server Management Studio for routine maintenance. Under Diagnostics > Maintenance > Database Optimization, choose an Optimization level:

Solution:

• Run Level 1 regularly; run Level 2 or Level 3 during off-hours if the slow-query log shows degradation on the Jobs/Copies tables.

• On SQL Server Standard/Enterprise you may additionally schedule a native maintenance plan.

4. Slow Queries

Cause: A specific query (often a report or the activity log on a huge date range) is slow.

Solution:

• OptimiDoc logs every slow query automatically. The built-in interceptor warns at 2 000 ms and errors at 10 000 ms by default (tunable via the SlowQuery.WarnMs / SlowQuery.ErrorMs app settings), writing to a dedicated slow-query log.

• Use the child article Slow Query Monitoring for the full workflow — the application log, SQL Server Query Store and Extended Events — and the recurring query patterns to expect.

5. Command Timeouts ("Timeout expired")

Cause: A database command exceeded its allotted time and was cancelled, surfacing as Timeout expired.

How the timeouts are set:

• Both contexts default to 120 seconds.

• Reports, exports and the activity log raise it to 300 seconds for large date ranges.

• The bulk job delete (Clear Old Jobs) raises it to 1 200 seconds (20 minutes).

Solution:

• A one-off timeout on a very large report usually means the date range or dataset is enormous — narrow the range, or ensure indexes and statistics are current (cause 3).

• Persistent timeouts on ordinary queries point at fragmentation/stale statistics or LocalDB limits, not the timeout value itself.

6. Connection Pool Exhaustion vs. the Sync-over-Async Deadlock

Two different problems both look like "the app hangs" — distinguish them by the logs.

Connection pool exhaustion. The pool (default 100) is fully in use and new requests wait, logging "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool."

• Recycle the IIS application pool for immediate relief, and investigate what holds connections open (a long-running query or a leaked context).

• Raise Max Pool Size in the connection string only as a stop-gap.

The sync-over-async deadlock (silent-log signature). If the web UI hangs and the logs show nothing at all, suspect a blocked request thread, not a crash: a request thread that blocked on an async call can deadlock and starve the thread pool.

• The tell-tale sign is that background processing keeps working while the UI stalls — the classic "delivery works, browse hangs." A hung request thread writes no log line, and IIS only logs completed requests, so silence in the log is itself the signature.

• The storage layer is fully asynchronous in 26.x, which removed the known instances of this class of bug. If you see the signature, capture which operation triggers it and report it — recycling the app pool clears the symptom temporarily.

7. Disk Space Exhaustion

Cause: The data file (.mdf) or transaction log (.ldf) has filled the drive, so all database operations fail.

Solution:

• Free space or expand the volume; check the .mdf / .ldf sizes and the SQL Server error log.

• If point-in-time recovery is not required, set the recovery model to Simple to curb log growth; shrink the log only as a temporary measure after identifying why it grew.

• Trim history via Clear Old Jobs (cause 1) to reduce data-file growth.

8. Don't Overlook the Separate Quota Database

Cause: Quotas live in their own database (optimidocQ), which is easy to forget during backups, migrations and maintenance.

Solution:

• Include the quota database in every backup and migration plan alongside the main database.

• If quota checks fail or quota history is missing after a server move, confirm the quota database connection string and that its migrations have been applied. See Migration to a New Server.

Performance Optimisation Recommendations

In Summary

Most performance problems at scale come down to history growth, LocalDB limits, or stale indexes/statistics — all addressable in-product through Maintenance (Database Info, Clear Old Jobs, Database Optimization) without SSMS. When the app hangs, read the logs: a connection-pool timeout is logged and cleared by recycling; a completely silent hang with background work still running is the sync-over-async deadlock signature. And never forget the separate quota database when backing up or migrating.

Related articles

• Slow Query Monitoring

• LocalDB to SQL Server

• Maintenance

• Cluster and Failover Issues

• Troubleshooting Guide