---
title: "Documents index (documents reports)"
description: "How Signer builds the Documents table index used by the documents reports when updating to 3.0.0, and what to do if the automatic process fails."
canonical: "https://docs.lacunasoftware.com/en-us/articles/signer/on-premises/update-30-documents-index"
locale: "en"
product: "Signer"
last_updated: "2026-10-16"
---

# Documents index (documents reports)

This page explains the `IX_Documents_SubscriptionId_CreationDateUtc` index, built when updating to version **3.0.0** (SIG-1919), and what to do if the automatic build fails. It applies to **all** instances updating from a version earlier than 3.0.0.

> **Note**
>
> Updating at a time of low usage is strongly recommended. While the index does not exist, the application works normally. The cost is that the documents reports can be slow or time out on organizations with many documents.

## What this index improves

The organization, user and folder documents reports load every document of a period, filtering by subscription (`SubscriptionId`) and sorting by creation date (`CreationDateUtc`). Without an index on those two columns, the database must sort a large volume of documents before returning the rows, which caused timeouts when generating the monthly report of large organizations.

```text
IX_Documents_SubscriptionId_CreationDateUtc
  keys: SubscriptionId (ascending), CreationDateUtc (ascending)
```

If a report times out, the application log records a warning suggesting to raise `ReportDatabaseCommandTimeoutSeconds` or to check that the index exists.

## How the automatic process works

1. **Migration (`AddDocumentsReportIndex`).** Run on the first start. It does **not** create the index, so the `Documents` table is not locked during the update.
2. **Enqueue on startup.** `DocumentsReportIndexBuilderStarter` checks whether the index exists. If it does not (or if it cannot check), it enqueues the `CreateDocumentsReportIndexJob` in Hangfire (*Low* queue, up to 3 attempts, no concurrent execution). This repeats on every startup while the index does not exist.
3. **`CreateDocumentsReportIndexJob`.** If the index already exists, it does nothing. Otherwise, it detects the SQL Server edition and builds the index with `SORT_IN_TEMPDB = ON` and a timeout of `DocumentsReportIndexCommandTimeoutSeconds` (default `7200`):
   * **ONLINE** (does not lock the table) on Azure SQL Database, SQL Managed Instance and SQL Server Enterprise or Developer.
   * **OFFLINE** on other editions, which **locks the `Documents` table** until it finishes. The job logs a warning.
   * If the *ONLINE* build fails with an error that is not a timeout, the job tries an *OFFLINE* build. If the *ONLINE* build hits the **timeout**, it is **not** retried *OFFLINE*, so the table is not locked for even longer.

### How to follow it

* In the Hangfire dashboard (`/hangfire`), follow the `CreateDocumentsReportIndexJob`.
* In the application logs, look for `Creating IX_Documents_SubscriptionId_CreationDateUtc`, `created successfully` or `Failed to create`.
* In the database:

```sql
SELECT name, is_disabled FROM sys.indexes
WHERE name = 'IX_Documents_SubscriptionId_CreationDateUtc' AND object_id = OBJECT_ID('Documents');
```

## What to do if the automatic process fails

The job failed if it used up its 3 attempts in Hangfire, or if the logs show a timeout or a deadlock (SQL error 1205) and the query above does not return the index.

### 1. Raise the timeout and restart

Raise `DocumentsReportIndexCommandTimeoutSeconds` (see [*General* Settings](https://docs.lacunasoftware.com/en-us/articles/signer/on-premises/settings#general-settings)) and restart the application. The job is enqueued again on startup.

### 2. Create the index manually

If it still times out, create the index on the database. Do it in a low-traffic window and in **a single session**: two simultaneous *ONLINE* builds on the same table deadlock on the final lock swap, and `IF NOT EXISTS` does not protect against concurrent runs. On editions without *ONLINE* support (SQL Server Standard and Express), use `ONLINE = OFF`, which **locks the table** during the build.

```sql
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_Documents_SubscriptionId_CreationDateUtc' AND object_id = OBJECT_ID('Documents'))
CREATE INDEX [IX_Documents_SubscriptionId_CreationDateUtc] ON [Documents] ([SubscriptionId] ASC, [CreationDateUtc] ASC)
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);
```

Precautions:

* **Deadlock.** The *ONLINE* build competes for locks with the application's writes and can cause a deadlock (error 1205); the victim may be an application transaction rather than the build. After the build, check the application logs for 1205 errors in that period.
* **Azure SQL Database.** Index creation is always fully logged, so the time depends on the service tier's log rate limit. Low tiers are throttled (`LOG_RATE_GOVERNOR` wait; the `CXCONSUMER` wait is benign). If you can, scale the service tier **before** starting the build, since changing the tier midway aborts a build in progress.
* **Resumable build (optional).** On SQL Server 2019 or later and Azure SQL, add `RESUMABLE = ON` to the `WITH` clause so an interruption does not discard the progress (check `sys.index_resumable_operations` and use `ALTER INDEX ... RESUME` or `ABORT`). `RESUMABLE = ON` cannot be combined with `SORT_IN_TEMPDB = ON`; remove that option.

### 3. Confirm

Run the query in the [How to follow it](https://docs.lacunasoftware.com/en-us/articles/signer/on-premises/update-30-documents-index#how-to-follow-it) section: the index must be present with `is_disabled = 0`. With the index created, the job finishes without rebuilding it on the next startup.