January 7, 2009 at 4:22 pm
Hello,
I noticed that starting on 01/02/2009, one of our maintenance plans started hanging. The server in question is running SQL 2000.
The maintenance plan has these tasks:
Backup Database (with removal of files older than 3 days; Simple recovery, so no log backups)
Optimizations Job
Integrity Check
Here is what I know so far. Let me know if you need more information.
1. The backup job runs every day in the late afternoon, while the other jobs are staggered to run every other day (one Monday, the other Tuesday, etc.) in the middle of the night. So I noticed the backup job behavior first.
2. The job starts fine, and backs up all of the relevant databases - except for one, which I noticed is selected in the properties of the job but had not been backed up since 01/01/2009. I manually backed up the database, though, with the verify option checked, and the backup was created successfully.
3. The backup job does not remove the files older than 3 days, and when I look at the Jobs section of the Management node in Enterprise Manager, the job status says: Executing Job Step '1 (Step 1)'.
4. It looks like the job will remain in that state forever, unless I select the Stop Job command. When I first noticed the problem, the backup job had been in that state for 37 hours. Even though I noticed the backup job behavior first, eventually all of the jobs in the maintenance plan get stuck in the same "Step 1" manner.
5. I tried creating a new job with the same settings, and disabling the old one, but I am getting the same behavior.
6. We have other maintenance plans that perform the above tasks for other databases, and they are working fine, including cleaning up the files older than 3 days.
Has anyone else seen this behavior? Does anyone know why the jobs would hang this way? Is it one database in particular out of the 10 or so involved that is causing the problem? If so, is there a way to find out which one? Also, is this a SQL 2000 issue? We are running this version:
Microsoft SQL Server 2000 - 8.00.2050 (Intel X86) Mar 7 2008 21:29:56 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Thanks for any help - this is driving me crazy!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 8, 2009 at 1:48 pm
Did you stop and start the SQL Server Agent? It would be the least invasive thing to try during business hours.
-- You can't be late until you show up.
January 8, 2009 at 2:04 pm
tosscrosby (1/8/2009)
Did you stop and start the SQL Server Agent? It would be the least invasive thing to try during business hours.
Thanks for your reply - I've just restarted SQL Server Agent. I'll see if it works and will write back to the thread with the results.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 8, 2009 at 3:45 pm
tosscrosby (1/8/2009)
Did you stop and start the SQL Server Agent? It would be the least invasive thing to try during business hours.
Update: I waited for the backup job to run after having restarted SQL Server Agent, but unfortunately the job hung in the same way.
Next I am going to try taking the database that doesn't appear to be getting backups made (see item 2 in my original post) out of the plan to see if that makes a difference.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 9, 2009 at 4:26 pm
Update 2: We restarted the SQL server as part of a firmware upgrade, and, as odd as it may sound, the jobs in question are now running properly.
I'm not sure whether it was the reboot of the server, the restart of SQL Server, or the upgrade that solved the problem, but I'm happy that the jobs are working now.
If anyone has any ideas about which one it might have been, I'd love to know.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 9, 2009 at 4:33 pm
you'll never be 100% sure, but I'll bet a restart of SQL service alone would have done, as this is enough to clear out all resources allocated by SQL.
Even nowadays, the occasional server bounce can't harm.....................
---------------------------------------------------------------------
May 18, 2009 at 11:50 am
Restart of SQL Server may resloved the above problem, but if there are more than one database in that server then you can not restart the SQL Server or reboot the server, what I think the best possible way is to drop that database and restore from backup.
June 4, 2009 at 3:57 am
Got exactly the same issue, don't understand why it would go tiz like that. I've checked the plan databases via checkdb etc and backed up manually using the verify option. Hmm , i'll try the re-boot tonight on the server to see if this will fix the issue ...
June 4, 2009 at 4:18 am
If you can afford the risk of it not working might be worth trying just restarting SQL, would be interesting to know if thats enough to clear the problem.
---------------------------------------------------------------------
June 5, 2009 at 10:34 am
george sibbald (6/4/2009)
If you can afford the risk of it not working might be worth trying just restarting SQL, would be interesting to know if thats enough to clear the problem.
Yes, I'm still wondering about that myself, though we can't afford to try that out on the SQL server in question (it's in production). I'd like to hear from anyone else if they can confirm one way or the other.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 5, 2009 at 2:00 pm
webrunner (6/5/2009)
george sibbald (6/4/2009)
If you can afford the risk of it not working might be worth trying just restarting SQL, would be interesting to know if thats enough to clear the problem.Yes, I'm still wondering about that myself, though we can't afford to try that out on the SQL server in question (it's in production). I'd like to hear from anyone else if they can confirm one way or the other.
Thanks,
webrunner
I'm hoping altafhahmed can tell us.
---------------------------------------------------------------------
June 7, 2009 at 10:20 am
ok , the server in question was a production server for us, i rebooted the sql server on the windows server around 10pm when we have nothing running on the servers . The re-boot seems to have fixed the issue and maintenenace plan started working again. Still not sure why this started happening but for now its fixed ...
June 7, 2009 at 11:11 am
thanks for the feedback, so to clarify, you restarted the SQL instance only and did not reboot the server itself?
---------------------------------------------------------------------
June 12, 2009 at 4:52 pm
Yes , just re-booted the sql server instance, i did not re-boot the windows server
July 31, 2009 at 12:41 pm
Hello all have same problem on a couple of servers.
In Task Manager- Processes. I stopped slqmaint - which had multiple instances running and it cleared the status in EM from Exectuing... to Not Running.
A reboot would effectively do the same thing.
So I am hoping this will cure the problem.
What caused it tho?
Will let all know next week on out come
Very frustrating indeed
Cheers
DC
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply