Qlik Replicate: SQL Server Source Query Timeout for Backup Logs

[Dana_Baldwin]

A query timeout expired error is displayed.

Example query run that resulted in the error:

select bmf.physical_device_name, bs.position, [dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn), [dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn), bs.backup_set_id from msdb.dbo.backupmediafamily bmf, msdb.dbo.backupset bs where bmf.media_set_id = bs.media_set_id and bs.backup_set_id > 0 and bs.database_name=db_name() and bs.type='L' and ( cast('00042ead:00024eb8:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) >= cast([dbo].[attrep_fn_NumericLsnToHexa](bs.first_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) and cast('00042ead:00024eb8:0001' collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) < cast([dbo].[attrep_fn_NumericLsnToHexa](bs.last_lsn) collate SQL_Latin1_General_CP1_CI_AS as varchar(24)) ) and bmf.device_type in(2, 102, 0

Environment

• Qlik Replicate 
• SQL Server Source Endpoint

Resolution

This timeout can happen when at least one of the tables msdb.dbo.backupmediafamily and msdb.dbo.backupset has grown in size and delays the query that locates backup files to load. You can purge backup data that is above your retention by using sp_delete_backuphistory with @oldest_date set with a value that should be enough for your retention policy to work fine.

[danielshryock]

I was able to see a significant improvement in several Replicate queries by replacing the final return statement in [dbo].[attrep_fn_NumericLsnToHexa] with the following code:

return lower( convert(varchar(16),@lsnLeftSeg ,2) +':'+ convert(varchar(16),@lsnMidSeg ,2) +':'+ convert(varchar( 8),@lsnRightSeg,2) )

It uses a built in function for the binary to hex conversion, and is significantly faster.  This is only a mitigation, the best solution would be to fix the queries that Replicate uses.  The current form is poorly optimized and forces SQL Server to do large amounts of extra work to search all of the backups.  If the query were altered to convert the HEX LSN to a numeric(25) first, and then use that to query the tables, this problem would likely never occur, even with large volumes of backups in the backupset table.

I also wanted to mention that some older versions of Replicate do a poor job of logging the error.  I actually had to upgrade the software so I could find error message.  Before the upgrade, I was receiving errors like "the task stopped abnormally", similar to what is seen in this post:

https://community.qlik.com/t5/Qlik-Replicate/Replicate-task-fails-with-quot-the-task-stopped-abnorma...

[Dana_Baldwin]

Hi @danielshryock 

Thanks for your feedback. Could you please submit this as a feature request here, so our Product Management team will see it? https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana