asked on

Microsoft SQL 2000, maintenance plans failing, any advice

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).

wittyslogan

have you looked in maintenance plan history? Under maintenance palns, click on the relevant plan and right click and view history.

Concern Support

ASKER

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.

wittyslogan

it looks like the image?
maint-plan.JPG

Concern Support

ASKER

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

Concern Support

ASKER

Sorry, this might be clearer.
managment-jobs2.JPG

wittyslogan

you recreated the job using the gui i assume?

Concern Support

ASKER

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.

wittyslogan

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

wittyslogan

also is this box ticked?
maintplan-history.JPG

Concern Support

ASKER

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

Concern Support

ASKER

maint plan
database-maintenance-plan.JPG

wittyslogan

TICK the box. It will write to maint plan history and give you a fuller response.

Concern Support

ASKER

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?

Concern Support

ASKER

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.

ASKER CERTIFIED SOLUTION

wittyslogan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wittyslogan

by the way are any other users in the database?

Concern Support

ASKER

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.

wittyslogan

what was it?

Concern Support

ASKER

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?

wittyslogan

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