Linked Server Mayhem…

Background:
I recently encountered an issue with an ETL process pulling in data from an Excel Spread-Sheet for use within an SSAS cube. The ETL process uses the Microsoft.ACE.OLEDB.12.0 provider which opens up the SpreadSheet on a remote UNC share to read the data into a temporary table.

The Problem:
Seemingly randomly, the ACE Provider would crash and leave an open transaction running with no hope of ever completing. The SQL Server ‘Kill’ command would mark the transaction and the session as “Killed / RolledBack”, but the session would still be present, and the SpreadSheet would still be locked and inaccessible.

The Solution:
TCPView (www.sysinternals.com) seems to help out significantly here. Given that there doesn’t seem to be any way of killing the session (and hence freeing up the SpreadSheet) without re-starting SQL Server, and SQL Server is sitting waiting for a response through the linked server which is never going to arrive, the options are a little limited.

It’s not a walk in the park though – TCPView and SQL Server’s sys.dm_exec_connections DMV don’t seem to offer a direct means of correlating which session / connection and which TCPView entry is the offending connection, so a little bit of consideration (guesswork) is in order – but given that the alternative is to re-start an active production SQL Server instance, I know which one I prefer!