December 4, 2012 at 8:02 am
We have a query running on SQL Server 2008 that pulls data from about 6 Linked SQL Servers hourly. It runs great for the most part, but about once a week it hangs and locks all tables it uses. I'll try to kill the job, but it sits there with this until I stop and restart the MS SQL.
SPID ##: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
The wait info is PREEMPTIVE_COM_RELEASE so I assume while it's linked to a server the OS interrupts the link for some reason causing the job to hang, so is there any options or timeout I can set to remedy this? I could use SSIS to pull from each SQL Server, but it's just so convenient to have this process in one slim sproc as opposed to a clunky SSIS script.
Thanks for any suggestions.
December 4, 2012 at 9:30 am
While I agree that having a single query to accomplish this remains a convenient thing, it may not be quite sufficient from a reliability perspective. Having 6 different sources can be a tricky thing, as each SQL Server may have it's own difficulties to deal with, and an hourly process is eventually going to trip over one of those problems, so it's pretty much a necessity to take that possibility into account in designing your process. However inconvenient, it might be better to design 6 SSIS packages to transfer the required data across to a single destination, with some kind of versioning information to go along with it, so that the data tells the story of whether or not that particular transfer was fully completed. You might consider using PUSH instead of PULL - meaning those six packages run on their respective source servers, which isolates a problem server to itself from a reliability perspective.
Then you can decide whether you can proceed without all 6 servers worth of data, or if the previous hours data can be combined when current data is missing for a given server, and design around your answer to that question.
Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 4, 2012 at 9:51 am
I like the idea of pushing the data from each source database, one catcher is most of them are SQL 2000 servers (yeah I know...) so I'd have to dust off my DTS skills to push from those 🙂
So yeah, I'm afraid I'll have to scrap my linked server procedure to get this process more streamlined. I just thought there may be some magic bullet that would fix the problem... but from a few other forums I've read where people get this same problem while using Linked servers the only common solution has been not to use Linked Servers.
Take care --
December 4, 2012 at 10:21 am
Well...
You could use a linked server from each of the source SQL 2000 instances.... all pointing to the same place. Of course, that's not a lot different from using a DTS package, but it might eliminate the need to do much more than retry the SQL Agent job that runs the query, and I think you can put that condition in the Agent job somehow... It might mean a fixed number of retries, but that's probably better than zero.
Linked servers are often tossed up as the convenient victim, but that stems largely from their mis-use / abuse, such as when you discover a good thing, suddenly it's being used all over the place. There IS such a thing as too much of a good thing, where Linked Servers are concerned. It's still a network connection, subject to the limitations thereof, and to any local instance constraints, such as any problems on the instance itself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply