Edit

sys.sp_verify_database_ledger (Transact-SQL)

  • Article
  • 2 minutes to read

Applies to: SQL Server 2022 (16.x) Preview Azure SQL Database

Verifies the database ledger and the table ledgers. For each row in the sys.database_ledger view, the stored procedure:

  1. Recomputes a value stored in the previous_block_hash column of the row.
  2. Checks if the recomputed value matches the value currently stored in the previous_block_hash column.
  3. If the specified list of digests contains a digest for the ledger block the row represents, it verifies the recomputed value matches the hash in the digest.
  4. If a ledger table name is specified using the table_name argument, the stored procedure verifies a table hash for the specified table, if it exists in the table_hashes column of sys.database_ledger. Otherwise, it verifies all table hashes existing in the table_hashes column of sys.database_ledger, except table hashes for non-existing (dropped) tables. When verifying a table hash for a ledger table, the stored procedure:
    1. Scans the history table of the ledger table to recompute the table hash, which is a hash of all rows updated by the transaction represented by the current row in sys.database_ledger in the ledger table.
    2. Checks if the recomputed table hash matches the value stored in the table_hashes column of sys.database_ledger for the given ledger table.

In addition, the stored procedure verifies all non-clustered indexes are consistent with the specified ledger table. If no ledger table is specified, it verifies all non-clustered indexes for each existing ledger table referenced in the table_hashes column in any row of sys.database_ledger.

For more information on database ledger, see Ledger

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_verify_database_ledger [@digests = ] 'digests' [ , [@table_name = ] 'table_name' ]

Arguments

[ @digests = ] 'digests'

A JSON document containing a list of transaction digests, each of which has been obtained by querying the sys.database_ledger_latest_digest view. The JSON document must contain at least one digest.

[ @table_name = ] 'table_name'

The name of the table that you want to verify.

Return code values

0 (success) or 1 (failure).

Result sets

1 row with 1 column called: last_verified_block_id

Permissions

Requires the VIEW LEDGER CONTENT permission.

See also