January 11, 2018 at 9:16 am
Yesterday I ran into a situation on one of my servers. A query was attempting to create a table from data pulled from a linked (Oracle) server over a (usually) reliable WAN VPN connection using OPENQUERY.
The problem started because the link yesterday was not reliable (networking issues at the remote end,) so during the process the link went down, the query hung, and shortly after the database that the table was being created in stopped responding. Killing the offending session did nothing, it just stuck in "KILLED / ROLLBACK" state, and because of the stuck session trying to "OFFLINE WITH ROLLBACK IMMEDIATE" also didn't work (but at least I could stop the OFFLINE query.) Unfortunately at the time, I didn't think to look and see what locks were being held in sys.dm_tran_locks.
My question is, is it possible that locking eventually caused the problem? The data was going into a new table, that would be in a schema used by other tables. My gut feeling is this is what caused the non-responsiveness (I wound up stopping the SQL Server service and restarting it, using the correct method of SQL Server Configuration Manager.) Or, potentially, could there have been something else going on at the time? Other databases on the server were unaffected (until I stopped SQL, that is,) so it wasn't like it was filling up a drive.
The query in question (real table names replaced):SELECT * INTO SCHEMA.NEWTABLE
FROM OPENQUERY(LinkedServer, 'SELECT * FROM REMOTETABLE');
Thanks all.
Jason
January 11, 2018 at 11:42 am
jasona.work - Thursday, January 11, 2018 9:16 AMYesterday I ran into a situation on one of my servers. A query was attempting to create a table from data pulled from a linked (Oracle) server over a (usually) reliable WAN VPN connection using OPENQUERY.The problem started because the link yesterday was not reliable (networking issues at the remote end,) so during the process the link went down, the query hung, and shortly after the database that the table was being created in stopped responding. Killing the offending session did nothing, it just stuck in "KILLED / ROLLBACK" state, and because of the stuck session trying to "OFFLINE WITH ROLLBACK IMMEDIATE" also didn't work (but at least I could stop the OFFLINE query.) Unfortunately at the time, I didn't think to look and see what locks were being held in sys.dm_tran_locks.
My question is, is it possible that locking eventually caused the problem? The data was going into a new table, that would be in a schema used by other tables. My gut feeling is this is what caused the non-responsiveness (I wound up stopping the SQL Server service and restarting it, using the correct method of SQL Server Configuration Manager.) Or, potentially, could there have been something else going on at the time? Other databases on the server were unaffected (until I stopped SQL, that is,) so it wasn't like it was filling up a drive.
The query in question (real table names replaced):
SELECT * INTO SCHEMA.NEWTABLE
FROM OPENQUERY(LinkedServer, 'SELECT * FROM REMOTETABLE');Thanks all.
Jason
When you insert data into a table, it is wrapped in a transaction and all changes are logged in the transaction log. If the connection drops, the insert transaction cannot complete and SQL will roll it back. During this rollback, the best and only thing short of database corruption is to let it run its course. Yes, the rollback can tie up the database and cause a blocking chain, in my own experience.
January 11, 2018 at 11:56 am
RandomStream - Thursday, January 11, 2018 11:42 AMjasona.work - Thursday, January 11, 2018 9:16 AMYesterday I ran into a situation on one of my servers. A query was attempting to create a table from data pulled from a linked (Oracle) server over a (usually) reliable WAN VPN connection using OPENQUERY.The problem started because the link yesterday was not reliable (networking issues at the remote end,) so during the process the link went down, the query hung, and shortly after the database that the table was being created in stopped responding. Killing the offending session did nothing, it just stuck in "KILLED / ROLLBACK" state, and because of the stuck session trying to "OFFLINE WITH ROLLBACK IMMEDIATE" also didn't work (but at least I could stop the OFFLINE query.) Unfortunately at the time, I didn't think to look and see what locks were being held in sys.dm_tran_locks.
My question is, is it possible that locking eventually caused the problem? The data was going into a new table, that would be in a schema used by other tables. My gut feeling is this is what caused the non-responsiveness (I wound up stopping the SQL Server service and restarting it, using the correct method of SQL Server Configuration Manager.) Or, potentially, could there have been something else going on at the time? Other databases on the server were unaffected (until I stopped SQL, that is,) so it wasn't like it was filling up a drive.
The query in question (real table names replaced):
SELECT * INTO SCHEMA.NEWTABLE
FROM OPENQUERY(LinkedServer, 'SELECT * FROM REMOTETABLE');Thanks all.
JasonWhen you insert data into a table, it is wrapped in a transaction and all changes are logged in the transaction log. If the connection drops, the insert transaction cannot complete and SQL will roll it back. During this rollback, the best and only thing short of database corruption is to let it run its course. Yes, the rollback can tie up the database and cause a blocking chain, in my own experience.
I get that the transaction should rollback, but in this case (I've run into these before,) they don't because the server at the other end of the linked server connection isn't responding (whether from the link breaking, login failure, or the remote server being down.) I've had these "never-ending" queries even from just executing the "Test Linked Server" in SSMS and the only way to kill them and get them out of the list of requests (sys.dm_exec_requests) is to stop and restart SQL.
But, from what you're saying, still, I can run into a blocking chain that would "lock up" my local database in this situation.
January 11, 2018 at 3:27 pm
January 11, 2018 at 7:18 pm
So a bit more digging today on this problem turned up a Connect item from about 2006 reporting the same problem.
The OPENQUERY "hangs" sometimes (in the case of the submitter, they have a job that runs every 5 minutes and pulls data into a temp table, which makes their situation a bit different,) and the session that "contained" the OPENQUERY cannot be killed...
It can't be reasoned with, or bargained with, it just keeps going!
MS closed the item with a "Won't fix" and the only work around someone found is to use a utility to force shut the TCP session at the OS level. Which, while a way to mitigate the problem, involves a couple things that I might have problems with:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply