July 20, 2009 at 1:47 am
I have the following situation:
An exportproces from a table to a flatfile runs every night and takes about 2-2.5 hours to finish. This proces has been running for months without a problem. Last week it crashed after more then 6 hours(!) with a 16943 error: (Could not complete cursor operation because the table schema changed after the cursor was declared)
Several other processes run during the night, as they did during all the months without problem. We are talking about SQL2005 on W2003 with the latest updates. I am trying to find out the cause for this error, first by trying to find what may have changed. It was rather busy that week during office hours, which stretch till about 22:00; it has been as busy in other weeks, where there were no errors. This exportprocess runs at night and there it was not busier then usual. There are no databaseprocesses added, and I am assured no outside processes, which connect to the database either.
I have analysed every object involved in this proces: no objectchanges have occurred. I have checked every possible situation where this error 16943 could occur, and those situations do not exists in this environment: SP3 with apropriate hotfixes are installed, de database involved is not part of a replicationprocess, etc.
I have created and run a trace during a night, where the error occurred. I only captured SQLstatements with endtime, but ended with a huge amount of data; we just don't have the room to loaded into a table, it will grow up to several 100 Gb. It also takes an insane amount of time to load it into a table when you try. Mind, we're talking about very decent hardware here: 8 quadcores, 32 Gb RAM, very fast drives, etc.
This downloadprocess is generated by a thirdparty application, and this whole procedure is a very inefficient 1. But, since it IS a third-party application that is not going to change soon. Network monitoring during those nights shows nothing significant which might shed light on what is happening here.
Strangely so starting last saterdaymorning(18th of july) the downloadprocess ended succesfully, albeit after more then 4 hours; which is less then the previous failures which lasted 6 hours, but more then the usual run nof 2-2,5 hours. Since then it runs succesfully everynight within 2.5 hours.
May questions are:
- Is there any other obscure reason why this error 16943 is generated? I myself tend to believe this is not the real error, but there is something else happening.
- The amount of tracedata is staggering and unworkable. Is there any way to check this amount of data ina convenient way? Currently I am loading just a part of it and analyse this, then truncate it and load the next batch, but this is tedious and timeconsuming work. Also, in light of what I have described, is there a better way to monitor database/server activities to search for any anomalies, which m,ight cause this?
I have to admit, currently I am flkabbergasted.
Greetz,
Hans Brouwer
July 20, 2009 at 3:01 am
Hans,
I'd suggest you go through these two articles by Gail. Excellent on how to create a server side trace, identify poor performing queries and then rectify/tune those queries.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply