Few Days ago, we had a particular issue with Linked Server. Before I start with the issue, let me give a small outline of the enviornment.
We have two servers A and B where a data porting is happening from A to B through a Linked server.Support person has manually invoked the job from the query analyser(SSMS). It was not a usual invoking instead as he faced some issues at connection, 1st run had failed at 45the minute and 2nd attempt failedat 6th minute. However the third attempt got succeeded in 2 hours time, for the attempt the person had kicked out and the query window got closed for first two attempts.
After the thrid successful attempt there were backgound activities running for the first two attempts with KILLED/ROLLBACK status. As it could be an issue with high transaction and a rollback we left thesituation to rollback to complete. However, after few days (4days), we again noticed that there were no changes in KILLED/rollback status and other processes has been blocked by these activity where it wasan usual behaviour and started analysing.
Our Analysis end up with few things as follows:
1. There were 2 SPIDs with status KILLED/ROLLBACK with PREEMPTIVE_OLEDBOPS wait stats in Server A.
2. These KILLED/ROLLBACK were blocking other processes in server A
3. Analysing the log growth on both servers A and B, it was steady at both servers.
4. The porting was based on PUSH method of Linked server(pushing the data from A to B which runs at Server A).
5. There were no blocking at Server B.
As the waitstats "PREEMPTIVE_OLEDBOPS", it was a strong indication of some issues outside SQL server, we then decided to restart the server. Upon restarting the machine,the server was back to normal and there were no blocking!!!
What would have happened???
From the above outcomes of the analysis, we were almost sure that the rollback has been completed successfully, however the rollback process were blocked on sending the acknoweldgement to server A from server B through OLEDB provider. In OLEDB Linked server, the transactions are happening at OLEDB throughITransactionJoin and ITransactionLocal interfaces. As there were some break in communication, the rollback at OLEDB did not happen completelty even the rollback of transactions had completed.
