My SQL Agent job runs every morning at 4 AM. It has 10 steps.
The last few weeks, it has been failing a couple times a week on Step 3 with this error.
The OLE DB provider "SQLNCLI11" for linked server "RemoteServerName" reported an error.
Execution terminated by the provider because a resource limit was reached. [SQLSTATE 42000] (Error 7399)
Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "RemoteServerName". . [SQLSTATE 42000] (Error 7421)
OLE DB provider "SQLNCLI11" for linked server "RemoteServerName" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.
When step 3 is successful, the duration is between 1 - 7 minutes.
When step 3 fails, the duration is between 12 - 15 minutes.
Both SQL Servers have 'Remote Query Timeout' set to 600 seconds ( 10 minutes ).
The entirety of Step 3, oddly enough, is just a simple DELETE statement.
DELETE FROM [RemoteServerName].DbNameHere.dbo.CompanyProductLink_Copy
If the timeout is set to 10 minutes, and the error says 'query timeout expired', why is the step duration varying between 12 and 15 minutes?
Is there a more efficient way to delete ~1,000,000 records from a remote table?
I can pretty easily increase the timeout... I just want to understand
Running SQL Server v12, on SSD's with 24GB RAM allocated.
It can take time to delete a table. Assuming you have no triggers or cascading foreign key references, then use truncate table instead:
TRUNCATE TABLE [RemoteServerName].DbNameHere.dbo.CompanyProductLink_Copy;
This should be much faster than a DELETE.