How To Calculate Values for HVR_SCAN_SEQ_BITS_MSSQL Environment Variable

Question

How can I calculate values for HVR_SCAN_SEQ_BITS_MSSQL environment variable?

Environment

HVR 5

Answer

There are cases when Capture on SQL Server terminates with an error message:

F_JD0A15: Component of the SQL Server LSN [00077FE3:00014C1A:023D], commit no 64, exceeds the expected limits of [FFFFFFFF:03FFFFFF], max commit no 63. The LSN component boundaries may be changed by setting the HVR_SCAN_SEQ_BITS_MSSQL environment variable; when the value of this variable is changed, hvr in it should be run to re-enroll the channel.

Reference

$HVR_SCAN_SEQ_BITS_MSSQL

[Capture] [Default: “32,26,6”]

The number of bits of LSN components to be used in scan sequence number calculation. This variable should have the <vlf_bits>,<blk_bits>,<txn_bits> format.

Details

This error happens when Capture is deriving the internal HVR transaction sequence value based on the SQL Server LSN values in the transaction logs. The internal storage size is limited to 64 bits but the overall LSN might result in a larger value.

The SQL Server LSN values consist of the following three parts:

• VLF (Virtual Log File) number (32 bit)
• block number (32 bit)
• slot number (16 bit)

The default value of the HVR_SCAN_SEQ_BITS_MSSQL variable is 32,26,6. The sum of these values should always be 64. For example, let’s refer to those three component values of 32, 26, 6 = x, y, z.

Taking into account the SQL Server's maximum block size and the minimum log record length, we can reduce the slot number to 12 bits. To save more bits from the slot number, and taking into account that hvr_seq values have to be unique for COMMIT records only, we do not use a slot number in hvr_seq calculation, but count the COMMIT records in each log block instead.

z=6 (the default) gives us 2^z = 2^6 = 64 commit records per log block. That is usually enough, but in case it is not, we usually suggest increasing z by decreasing y.

The value of y constrains the maximum VLF size. The default y = 26 means that the maximum VLF size that HVR supports in its default setting is 32G (2^26 log blocks, 512 bytes each). It is then a matter of compromise on how much you can decrease the value of y, based on the actual state in the database, in order to increase z. There’s no reasonable maximum VLF size value that fits all cases, but in each case, your DBAs should know the maximum SQL Server VLF size that is reasonable for them. You can also check the maximum size of the existing VLFs. You can do this using DBCC LOGINFO. This command returns a list of VLFs where the FileSize column indicates the size of each VLF in bytes. You cannot filter or aggregate directly on the output of the DBCC commands, therefore if the list of VLFs is huge it is impractical to find the max VLF size manually. You can create a temporary table, populate it with output or DBCC LOGINFO using INSERT INTO tmp_tbl EXEC(N’dbcc loginfo’) and then run SELECT MAX(FileSize) FROM tmp_tbl.

If required, you can decrease the value of x. It limits the maximum VLF number, which basically means limiting the supported database age. You can populate huge VLFs with slower log records, this in turn leads to VLF numbers growing slower. Similarly, smaller VLFs lead to VLF numbers growing faster.

Workaround

If your capture job terminates indicating that the LSN component boundaries need to be changed by setting the HVR_SCAN_SEQ_BITS_MSSQL environment variable, we then recommend you to update your channel definition by adding action Environment /Name= HVR_SCAN_SEQ_BITS_MSSQL with values compatible for your system. You can make this change without any data loss.

Our first recommendation is to see if you can reduce the y value. If reducing y is ok, do that.

NOTE: Make sure that the sum of x, y, and z is equal to 64. For example, the default action values for HVR_SCAN_SEQ_BITS_MSSQL. If you need to change the values to 28, 26, 10, your environment setting will be as follows:

• Once you update the channel definition, you will then need to run Initialize to only recreate the scripts and jobs (do not check any of the other advanced options) for the source and resume the capture job.
• A change request is in the queue to improve the user experience so that the users do not need to calculate the environment variables.