Solved

Microsoft SQL 2000, maintenance plans failing, any advice

Posted on 2010-09-09
20
982 Views
Last Modified: 2012-05-10
I worked on this for a morning. The automatic backups are not working  for SQL , they are failing. Any advice welcome. Database COMPANY here is not the actual name of our db, just changed it for reference.

Product: SQL Server Developer Edition
Operating System: Microsoft Windows NT – 5.2 (3790)
Product Version 8.00.2039 (SP4)
Language: English (United States)

Steps Taken so far:

Deleted existing maintenance plans.

In SQL Server Enterprise Manager – Management – Database Management Plans, I have created a maintenance plan that does nothing except “Back up the database as part of the maintenance plan”, set to be created on the local E: drive of the machine. It backs up the company database <COMPANY> only, not master, model, DYNAMICS or msdb.  I would not normally only backup the company database, but I was testing to see if I could narrow the area of failure.

Checked that the location specified for back up files exist and is reachable – straight copy and paste into windows explorer to ensure path was correct.

Restarted the SQL Agent (also rebooted the server).

Checked the SQL Agent permissions, running under SA account.

In SQL Server Enterprise Manager – Management – SQL Server Agent – Jobs I can see my maintenance job listed “DB Backup Job for DB Maintenance Plan ‘MGP Backup Daily’ listed with a “Last Run Status” of Failed.

When I double click the Job to see the properties, there is only one step listed which is this:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 298B2D9D-1A4A-4177-8F63-EC41A3C31CB4  -BkUpMedia DISK -BkUpDB "E:\OffsiteBackup\Daily" -BkExt "BAK"'

When I run this command in the query analyser I get the message
(7 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.

I can’t remember the command I used for this, but I checked that the PlanID referenced above exists in the database, it does .
I also ran this command in the query analyser
EXECUTE master.dbo.xp_sqlmaint N' -PlanID 298B2D9D-1A4A-4177-8F63-EC41A3C31CB4  -Rpt "e:\Maintenance Plan4.txt" -DelTxtRpt 4WEEKS -WriteHistory  -VrfyBackup  -BkUpOnlyIfClean  -CkDBRepair   -BkUpMedia DISK  -BkUpDB "E:\OffSiteBackup"  -DelBkUps 4WEEKS  -BkExt "BAK"

Get the message:
(39 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.

When I remove only the switch –BkUpOnlyIfClean and run this script in query analyser, it does create the backup but the Jobs list it as “Failed”.


Log file:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server '****-****\GP' as 'sa' (non-trusted)
Starting maintenance plan 'MGP Backup Daily' on 9/3/2010 5:28:04 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'COMPANY'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database COMPANY: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database COMPANY: Database Backup...
    Destination: [E:\OffSiteBackup\NSUD_db_201009031728.BAK]

    ** Execution Time: 0 hrs, 2 mins, 13 secs **

[3] Database COMPANY: Verifying Backup...

    ** Execution Time: 0 hrs, 1 mins, 11 secs **

[4] Database COMPANY: Delete Old Backup Files...
    0 file(s) deleted.

Deleting old text reports...    0 file(s) deleted.

End of maintenance plan 'MGP Backup Daily' on 9/3/2010 5:31:29 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)


When I remove only the switch –CKDBRepair and run this script in query analyser, it does NOT create the backup AND the Jobs list it as “Failed”.
Log File:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server '*********\GP' as 'sa' (non-trusted)
Starting maintenance plan 'MGP Backup Daily' on 9/9/2010 3:05:14 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'COMPANY'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database COMPANY: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[2] Database COMPANY: Database Backup...

    The backup was not performed since data verification errors were found.

Deleting old text reports...    0 file(s) deleted.

End of maintenance plan 'MGP Backup Daily' on 9/9/2010 3:05:14 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)



All db’s (COMPANY, DYNAMICS, master, msdb, TWO) except for “model” are marked as simple. “Model” is marked as full.
Also I ran DBCC CHECKDB against db COMPANY and if came back with:
"CHECKDB found 0 allocation errors and 0 consistency errors in database 'COMPANY'

I am out of ideas on this now? Also I am confused, tying myself up in circles and I am really at the limits of my knowledge.
Anyone any suggestions. (I think I have included everything I have tried but not necessarily in the same order).



0
Comment
Question by:concern_support
  • 10
  • 10
20 Comments
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33636829
have you looked in maintenance plan history?  Under maintenance palns, click on the relevant plan and right click and view history.
0
 

Author Comment

by:concern_support
ID: 33637196
I deleted the prior maintenance plans and recreated the basic one described above to see if that work so do not have the logs from then.

The existing basic job has history of nothing, it is completely blank?

The Management - Jobs section lists the "DB BAckup Job for DB Maintenance Plan ...." with a run status of Failed.

I just changed the time on the maintenance plan to make it run again, and still no entries in the maintenance plan history.




0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33637598
it looks like the image?
maint-plan.JPG
0
 

Author Comment

by:concern_support
ID: 33637717
I know where it is - there are no entries in there, like this (attachment maint-plan.jpg).
 
But management - jobs shows the listing for where I attempted to run a basic maintenance plan (by changing the job time to today). Attachment management_jobs.jpg.
 

maint-plan.JPG
managment-jobs.JPG
0
 

Author Comment

by:concern_support
ID: 33637771
Sorry, this might be clearer.
managment-jobs2.JPG
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33637869
you recreated the job using the gui i assume?
0
 

Author Comment

by:concern_support
ID: 33638037
Yes, I recreated the job using the gui and unticked every option except for "Back up the database as part of the maintenance plan" to make it is as simple as possible.

But I think at one point this morning I might have cleared all the maintenance job history entries in order to see what new ones where generated. Eek.
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33638109
can you back up the database using   just see if the database lets you


use [master]

declare @date char (14)

declare @filename varchar (100)

set @date = replace(replace(replace(convert(char(20),getdate(),120),'-',''),' ',''),':','')

set @filename = 'g:\Backup\company\company_db_' +@date+'.BAK'

select @filename

BACKUP DATABASE company

   TO DISK=@filename

GO

Open in new window

0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33638173
also is this box ticked?
maintplan-history.JPG
0
 

Author Comment

by:concern_support
ID: 33638297
I ran the script, yes it let's me create the backup this way no problem.

No, that box is not ticked. Everything on the maintenance plan is UNTICKED except for "Back up the database as part of the maintenance plan" as I previously said.

I also previously ran a backup by script successfully as detailed in the original question. The maintenance plan seems to fall over on the -BKUpOnlyIfClean switch.

backup-db.JPG
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:concern_support
ID: 33638310
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33638334
TICK the box.  It will write to maint plan history and give you a fuller response.
0
 

Author Comment

by:concern_support
ID: 33638532
How do I view the detail generated to "msdo.dbo.sysdbmaintplan_history".

I changed the timing on the job again (to 7:08pm when it was 7:06pm, applied change) and it ran, it shows in sql server group etc - management - jobs as "failed" but still no detail in maintenance plan history showing.
Also went into the maintenance plan properties, reporting tab, clicked on the "View History" button and no details listed.

Does it take a long time to show up here, it should be relatively quick?
0
 

Author Comment

by:concern_support
ID: 33638578
Just quickly created a new seperate plan to backup db and write history to table msdb.dbo.sysdb.... , set it to run, it ran and failed, no plan history for that one either.
0
 
LVL 7

Accepted Solution

by:
wittyslogan earned 500 total points
ID: 33638637
you can just do select * from msdo.dbo.sysdbmaintplan_history

how many rows have you set it to?
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33638724
by the way are any other users in the database?
0
 

Author Comment

by:concern_support
ID: 33638732
for Backup database, one for Verify Backup), both flagged as succeded.

GRRR!
But I suppose my only question now, could this be caused by users being in the database (they are in a remote location to me, I asked them to exit the application that uses the db, they said they did but who knows). My remote location faces some environmental issues, energy is a big one, they work off a generator that is shut down every night so the window would be very small; how can I force them out to make the backup run?

Also, I will try adding on to the maintenace to get it to do fully all I want.

Thanks for sticking with me!
Fi.
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33638735
what was it?
0
 

Author Comment

by:concern_support
ID: 33638772
Sorry - the above should have read like this: .

The new maintenance plan which I just created on the fly has now run, is now flagged in Management - Jobs as "succeeded", and the select displays 2 rows (one for Backup database, one for Verify Backup), both flagged as succeded.
GRRR!
But I suppose my only question now, could this be caused by users being in the database (they are in a remote location to me, I asked them to exit the application that uses the db, they said they did but who knows). My remote location faces some environmental issues, energy is a big one, they work off a generator that is shut down every night so the window would be very small; how can I force them out to make the backup run.

Also, I will try adding on to the maintenace to get it to do fully all I want.

Thanks for sticking with me!
Fi.


I don't know what it was. I swear I deleted and recreated that plan about 7 times today and now it decides to work. Could it have been the users being in there?
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33638786
go to activity monitor and kill them.

Stop the agent.

I think when you are checking the backup or thnigs like that the database needs them out.  

Personally I would shout at their manger and get them to give you a window.

Cheers
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Visual Studio Front End "Web Forms" For SQL Server Tables 5 41
Help with SQL Query 23 39
SQL JOIN 6 37
Sql query 34 22
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now