How to calculate values for the HVR_SCAN_SEQ_BITS_MSSQL environment variable
There are cases when HVR Capture on SQL Server terminates in error with a 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, hvrinit should be run to reenroll the channel.
[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 following format: ‘<vlf_bits>,<blk_bits>,<txn_bits>’.
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 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 exactly 64. For example, let’s refer to those three component values of 32, 26, 6 = x, y, z.
Taking into account the SQL Server maximum block size and the minimum log record length, we can safely reduce the slot number to 12 bits only. To save even 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 slot number in hvr_seq calculation at all, 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 the maximum VLF size that HVR would support in its default setting is 32G (2^26 log blocks, 512 bytes each). It is then a matter of compromise on how far 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 what is the maximum SQL Server VLF size which is reasonable for them. It is also a good idea to check the maximum size of the existing VLFs. This can be done using DBCC LOGINFO. This command returns a list of VLFs where the FileSize column indicates the size of each VLF in bytes. Unfortunately, you cannot do any filtering or aggregation directly on the output of the DBCC commands, so in case the list of VLFs is so huge it is impractical to find the max VLF size manually you will need to create a temporary table, populate it with output or DBCC LOGINFO using something like INSERT INTO tmp_tbl EXEC(N’dbcc loginfo’) and then run SELECT MAX(FileSize) FROM tmp_tbl.
If necessary, the value of x can also be decreased. It limits the maximum VLF number, which basically means limiting the supported database age. Huge VLFs are being populated with log records slower, which leads to VLF number growing slower. Similarly, smaller VLFs lead to VLF numbers growing faster.
If your Capture job terminated indicating that the LSN component boundaries need to be changed by setting the HVR_SCAN_SEQ_BITS_MSSQL environment variable, then we recommend you update your channel definition by adding the Environment action, /Name= HVR_SCAN_SEQ_BITS_MSSQL with values that will work for your system. This change can be made 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: remember that the sum of x, y, z must equal 64. For example, here is an example of the default Action values for HVR_SCAN_SEQ_BITS_MSSQL:
For example, if you need to change the values to be: 28,26,10 your Environment setting would be:
Once you update the channel definition, you will then need to run the HVR Initialize to only recreate 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 environment variables are not required to be calculated by users.