SQL Server 2000 Backup Job Issue

Posted on 2010-04-05
Medium Priority
Last Modified: 2012-05-09
One of our old production servers running SQL 2000 enterprise edition(SP4) suddenly stopped performing backups on 4/1/2010. When I manually start the jobs they do create backup files but the job never ends. It has to be manually stopped. No errors are evident in the SQL error log or in the job history. Also, when I create a new job by creating a new maintenance plan and then run the job, it does not create any backup files and never completes. Suggestions on troubleshooting and/or resolving the issue will be greatly appreciated. Thank you.
Question by:rjthomps123
  • 2
LVL 25

Expert Comment

by:Lee Savidge
ID: 29801207
Besides the obvious answer of upgrading to a SQL version that is not 10 years old, how big is the database? What OS is it running? What changed on or around that date?


LVL 13

Expert Comment

ID: 29802104
Is there any date/time change done on the server? Is there any major change or patch installation done on the server?

Author Comment

ID: 29812591
IT guys say no updates done on the server recently, e.g. 30 days. I found another old server running SQL 2000(SP4) where the jobs stopped running on the same date. Both servers are runnning Windows 2003 server(SP2).  Two other SQL 2000 servers running in a different domain are not effected. I also found that only the jobs created by Database Maintenance Plans are effected. Other jobs seem to run ok. Also, the rebuild index and integrity check jobs created through a Database Maintenance Plan work as well. It appears to be isolated to only the actual backup jobs created through Database Maintenance Plans. It seems like a probelm with some update or patch that was applied to these two servers. I am a programmer and not part of the IT group that does the updates. The IT guys say the last updates/patches were done a month or more ago. How can I tell what patches were applied that could have effected SQL? All the SQL servers are on the same version:   8.00.2055(SP4). My experience has been that an applied update would show a different version number. How can I troubleshoot this issue? Is there a way to tell what patches have been applied? Could Windows 2003 server patches be the source of the problem?

Accepted Solution

rjthomps123 earned 0 total points
ID: 29899460
I found that if I log in as 'sa' then I am able to create and run Maintenance Plans as well as run existing jobs created by a maintenance plan. If this were an update problem then changing my login would not affect the issue. My conclusion is that our normal sysadmin logins which are windows logins were somehow changed so that they no longer had the necessary permission to execute Maintenance Plans.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question