Query time out issues in the SQL Server

[suvbin]

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?

[john_wang]

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.

[suvbin]

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

[VijayaDeepthi]

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.

[Heinvandenheuvel]

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

[john_wang]

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.