topic Re: Query time out issues in the SQL Server in Qlik Replicate

Query time out issues in the SQL Server

suvbin — Sat, 25 Dec 2021 16:59:24 GMT

Facing Query time out issues many times with SQL server. What could be the issue. i have a table with 66,000,000 records. Its taking around 5 hours to load. And then its getting failed .

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Time-out occurred while waiting for buffer latch type 2 for page (1:43), database ID 2.

[Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired.

How to resolve this?

Re: Query time out issues in the SQL Server

john_wang — Sun, 26 Dec 2021 01:26:25 GMT

Hello @suvbin ,

The information is pretty limited. Please confirm "database ID 2" is the target endpoint database, sample query is:

select DB_NAME(2)

If the assuming is correct then:

1- Add the internal parameter executeTimeout to the target endpoint and set the value to a reasonable value eg 3600 (it's seconds, means 1 hour).

2- If you still want to speed up the Full Load then Parallel Load helps however please confirm if the source endpoint & target endpoints are supported, see Supported endpoints .

Hope this helps.

Regards,

John.

Re: Query time out issues in the SQL Server

suvbin — Sun, 26 Dec 2021 02:18:15 GMT

Thank you John for the reply. Do I need to run this query at source or target?

Re: Query time out issues in the SQL Server

VijayaDeepthi — Sun, 26 Dec 2021 02:27:20 GMT

Error is : Failed to commit 10000 rows to target <table-name>
RetCode: SQL_SUCCESS SqlState: 42000 NativeError: 9002 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

I think by looking above error itseems like its target database. the replicate job is from logstream staging to target.

Both source and target is Microsft SQL Server.

Re: Query time out issues in the SQL Server

Heinvandenheuvel — Sun, 26 Dec 2021 15:52:55 GMT

Vijaya,

Where did you get that "The transaction log for database 'tempdb' is full " message from ?

It would appear self explanatory and easily fixable able the DBA for the target DB.

How is this related to the question Suvbin posted about? Was there a log attached which I missed?

hth,

Hein

Re: Query time out issues in the SQL Server

john_wang — Mon, 27 Dec 2021 11:31:58 GMT

Hello @Heinvandenheuvel ,

I do not think you missed anything. Looks like @suvbin and @VijayaDeepthi comes from same company. is that correct?

Regarding "The transaction log for database 'tempdb' is full ", the above internal parameter does not help however there are options:

1- Remove the limit on the transaction log size and let it expands until the load is finished, or

2. Change the recovery model to simple so the transaction logs are minimized

Regards,

John.