I have a SQL Server database and have a linked server connection to an Oracle DB.

I had the following query running on my SQL Server:

INSERT INTO dbo.my_table_on_sql_server
    SELECT * 
    FROM OPENQUERY (linkedservername, ‘SELECT * FROM target_table’)

The target_table has 50 million rows and I'm aware the query takes time to execute but has successfully completed before.

This time though, my PC had an automatic restart in the middle of the query. SSMS 2017 automatically reopened as soon as the PC fired back up, but I could not longer see the query running. my_table_on_sql_server has no data.

I'd like to understand what happens in SQL Server in the event of such a situation. Am I correct in assuming that the query was killed / rolled back? Is there any query running in the background? I've seen some related answers on this forum but wanted to specifically understand this for linked servers, as I use them a lot to retrieve data from other DBs for my job.

I'm more concerned about the Oracle DB as I don't want my query to impact any performance upstream. I only have a read-only access permission to the Oracle DB.

Thank you!

CodePudding user response：

On shutdown the query will be aborted, and the INSERT rolled back. The rollback may happen during shutdown, or after restart, and may take some time to complete.

There's no automatic retry or anything that will access the linked server Oracle after the shutdown.