[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1024
  • Last Modified:

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



0
concern_support
Asked:
concern_support
  • 10
  • 10
1 Solution
 
wittysloganCommented:
have you looked in maintenance plan history?  Under maintenance palns, click on the relevant plan and right click and view history.
0
 
concern_supportAuthor Commented:
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
 
wittysloganCommented:
it looks like the image?
maint-plan.JPG
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
concern_supportAuthor Commented:
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
 
concern_supportAuthor Commented:
Sorry, this might be clearer.
managment-jobs2.JPG
0
 
wittysloganCommented:
you recreated the job using the gui i assume?
0
 
concern_supportAuthor Commented:
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
 
wittysloganCommented:
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
 
wittysloganCommented:
also is this box ticked?
maintplan-history.JPG
0
 
concern_supportAuthor Commented:
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
 
concern_supportAuthor Commented:
0
 
wittysloganCommented:
TICK the box.  It will write to maint plan history and give you a fuller response.
0
 
concern_supportAuthor Commented:
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
 
concern_supportAuthor Commented:
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
 
wittysloganCommented:
you can just do select * from msdo.dbo.sysdbmaintplan_history

how many rows have you set it to?
0
 
wittysloganCommented:
by the way are any other users in the database?
0
 
concern_supportAuthor Commented:
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
 
wittysloganCommented:
what was it?
0
 
concern_supportAuthor Commented:
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
 
wittysloganCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now