If a SQL Agent job step executing a stored procedure on a linked server times out, does the agent kill the SPID on the linked server or does it continue to run on the linked server?
SQL Job Step Timeout – Troubleshooting Guide
linked-serversql serversql-server-2012sql-server-agent
Related Question
- SQL Server Agent – Job Step Fails Due to Missing Database Snapshot
- SQL Server – Stored Procedures vs Inline SQL in Job Steps
- SQL Server – Fixing ‘Current Security Context is Not Trusted’ Error for Jobs Accessing Linked Server
- Sql-server – Linked Server Error on SQL Agent Job
- Sql-server – SQL Server Agent Jobs – Long running job delaying other jobs
- SQL Server Query Timeout – Exceeding Timeout on Linked Server
- Sql-server – Handling ‘Variable not found’ in a tokenised job step
Best Answer
Whenever a client disconnects from SQL Server, the database engine rolls-back any open transactions prior to the spid terminating.
This applies to all clients, even a "client" Agent Job running via a linked server.
If a stored procedure running via a linked server times out, the connection to the target server will disconnect, causing all open transactions in that session to roll back.
This answer shows many of the ways an open transaction can be rolled back.