August 13, 2010 at 9:01 am
I am having a problem where an openquery isn't populating a table. It just hangs.
Ok let me give you some background on my set up and what's going on. My coworker has an application that I developed in visual studio that allow him to easily pull data from our databases. Basically he just plugs in a time period and a drug name. This application then runs queries on my SS2k5 machine and returns the data to him excel.
The report he is running runs 2 openqueries on my box, which are used to pull data from an Oracle server. So the order is this.
Openquery1 pulls data
Local Query 1 joins some tables to get data then populates in excel
Openquery2 pulls more data
Local Query 2 joins some table to get data then populates in excel
For the most part, the queries are identical, except the user can use two different time periods for them.
So what is happening, that after a while openquery2 will never finish. I am thinking that the table may be getting locked out somehow, but not sure how to check this. He has tried closing his application and rerunning it. In this case, Openquery1 and Local query 1 will work fine, but once again hang on openquery2. So the connection to the oracle server is fine. Restarting the server fixes this problem. Any ideas on how to troubleshoot? Thanks.
August 13, 2010 at 10:07 am
tomiboy59 (8/13/2010)
So what is happening, that after a while openquery2 will never finish. I am thinking that the table may be getting locked out somehow, but not sure how to check this. He has tried closing his application and rerunning it. In this case, Openquery1 and Local query 1 will work fine, but once again hang on openquery2. So the connection to the oracle server is fine. Restarting the server fixes this problem. Any ideas on how to troubleshoot?
I would ask Oracle DBA to trace the session and see what is actually happening to OpenQuery#2. A 10053 trace would do the trick.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 18, 2010 at 9:48 am
Ok. The query hanging hasn't happened for a while, but now I got the error in the picture attached. This gets fixed if I restart the server. It happens once every week or two. I googled around and couldn't really figure out. I tried using the -g parameter to allocate more memory to non-Sql Server processes. But that still hasn't prevented the problem. Any ideas?
August 18, 2010 at 10:45 am
tomiboy59 (8/18/2010)
Ok. The query hanging hasn't happened for a while, but now I got the error in the picture attached. This gets fixed if I restart the server. It happens once every week or two. I googled around and couldn't really figure out. I tried using the -g parameter to allocate more memory to non-Sql Server processes. But that still hasn't prevented the problem. Any ideas?
Since it is obvious components are installed and availabe but somehow the system periodically "forgets" about them, it looks like a memory leakage.
I bet a weekly server recycle will either minimize or avoid the occurrence of this issue.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 19, 2010 at 7:41 am
PaulB-TheOneAndOnly (8/18/2010)
tomiboy59 (8/18/2010)
Ok. The query hanging hasn't happened for a while, but now I got the error in the picture attached. This gets fixed if I restart the server. It happens once every week or two. I googled around and couldn't really figure out. I tried using the -g parameter to allocate more memory to non-Sql Server processes. But that still hasn't prevented the problem. Any ideas?Since it is obvious components are installed and availabe but somehow the system periodically "forgets" about them, it looks like a memory leakage.
I bet a weekly server recycle will either minimize or avoid the occurrence of this issue.
Thanks, but I have a dumb question. What do you mean by recycle? Should I just restart the computer once a week? Or is there something I can do to automate the recycling?
August 19, 2010 at 8:24 am
tomiboy59 (8/19/2010)
PaulB-TheOneAndOnly (8/18/2010)
tomiboy59 (8/18/2010)
Ok. The query hanging hasn't happened for a while, but now I got the error in the picture attached. This gets fixed if I restart the server. It happens once every week or two. I googled around and couldn't really figure out. I tried using the -g parameter to allocate more memory to non-Sql Server processes. But that still hasn't prevented the problem. Any ideas?Since it is obvious components are installed and availabe but somehow the system periodically "forgets" about them, it looks like a memory leakage.
I bet a weekly server recycle will either minimize or avoid the occurrence of this issue.
Thanks, but I have a dumb question. What do you mean by recycle? Should I just restart the computer once a week? Or is there something I can do to automate the recycling?
Sorry, my fault 🙂
Recycle == Reboot, in this case restart the server.
If you can afford it I would reboot the system once a week and see if issue goes away.
In regards to automate the process, all depends on the size and resources of your organization... on mine I would open a recurrent ticket with systems team asking to reboot specific server at specific day-of-the-week and time.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 19, 2010 at 9:22 am
Ha ok. Thanks. This server is actually a really low cost operation. Something I did on the side to help out another team. The box sits next to me, so I can just restart it myself. I was hoping for another option other than restarting the server weekly. But I was about to go down that path anyway, depending on what this thread turned up.
August 26, 2010 at 2:35 pm
So restarting it once a week does not help. I did a manual reboot today, and when my coworker tried to run a report later that day he got the error... I rebooted again and it works fine, but for how long. I am really clueless as to why it is being like this. Any ideas? It's a pretty annoying thing to deal with on my part and his. Thanks ahead time.
August 26, 2010 at 3:29 pm
tomiboy59 (8/26/2010)
So restarting it once a week does not help. I did a manual reboot today, and when my coworker tried to run a report later that day he got the error... I rebooted again and it works fine, but for how long. I am really clueless as to why it is being like this. Any ideas? It's a pretty annoying thing to deal with on my part and his. Thanks ahead time.
I think it is time to open a ticket with Microsoft support.
May be this is related to patching or component version inconsistency but only them can tell for sure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply