March 26, 2008 at 7:28 am
I had a backup job that had been running for several days, or at least it said it was running. Yesterday I was able to stop that job and run a manual full backup. However, the process is still showing in the Activity Monitor and the scheduled backup from last night is still showing as running.
I've ran KILL ## WITH STATUS ONLY and it returns that the rollback is 100% complete. This server is clustered, but I have NO EXPERIENCE with a clustered environment.
1. Does the fact that this process is still showing cause a concern?
2. Could that be causeing the Backup Job to hang?
3. Would stopping and restarting the SQL Agent fix this?
4. What would be the concerns/effects of restarting the SQL Agent? There are no other jobs running.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 26, 2008 at 8:47 am
I haven't dealt with this in a clustered environment, but there are times when SPIDs seem to get stuck. Can you failover to the other side and reboot this one? That might clear it.
I'd try the SQLAgent restart if you can.
March 26, 2008 at 8:52 am
Steve Jones - Editor (3/26/2008)
I haven't dealt with this in a clustered environment, but there are times when SPIDs seem to get stuck. Can you failover to the other side and reboot this one? That might clear it.
Well, this is at a client site, and not having experience with clusters I'm apprehensive to do that.
I'd try the SQLAgent restart if you can.
I know jobs would fail, but there aren't any running. Do you know if this would have ANY affect on the cluster environment or anything other than jobs?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 26, 2008 at 9:34 am
Like Steve said, processes sometimes get "stuck". I have not seen this happen more or less in clustered environments. The only thing I have really noticed is that it happens most often to me during processes that are suceptible to susceptible to a non-SQL process failing. Backups to network drives on unstable networks has caused this at a few sites on me.
Check if the spid has any resources locked. If it doesn't, let it be for awhile (days) and as long as it is not hurting anything, leave it. Since you had a process run for several days before you killed it, these processes sometimes end up in a state that takes as long to clean themselves up.
If the process has resources locked (even share locks would bother me) and you are concerned about it, I have had little luck correcting this without stopping the SQL service. Since you have killed the process, it is no longer connected to it's client application (in your case, the SQL Agent) so it is unlikely that stopping the SQL Agent will do anything for you.
Client site or not, sometimes you have to test the failover procedures. This may be a good opportunity for you to make sure they are working correctly.
March 26, 2008 at 9:38 am
Good excuse to see if the procedures work! Put on your serious face and "sell it"!
March 26, 2008 at 9:41 am
Thanks Michael, that clears it up a bit.
And the spids have finally dropped out of activity monitor. The transaction logs are now running as normal. So for now, it's ok.
It's interesting that you mention a spid getting hung because of backups to a network drive, because that was the change that was made just before this happened. Not by me, but the client. It's a fairly large hospital so I would assume their network isn't "poor".
I'll need to do some reading up on Clustering and then test in house. I'm not going to take any chances with a customer site, just for learning experieces ...LOL
Thanks
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 26, 2008 at 9:42 am
Steve Jones - Editor (3/26/2008)
Good excuse to see if the procedures work! Put on your serious face and "sell it"!
I understand and would agree with that statement, but I'm too rusty in that area. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply