Database Maintenance Plan Optimize all DB´s fails 1

[AHand]

Hello can somebody answer me this question please!!!
(Please excuse my bad English spelling! I´m native germane…)

I´m pondering over a problem on sql server 7.0 SP4 and 2000. SP3
To maintain a good database performance I like to create database maintenance plans
Through the sql server enterprise manager.
I want the job to run on all databases, I want it to optimise with reorg data and indexes
And check db integrity, indexes included, perform before backup!

Now if I check the job in the sqlserveragent it tells me that integrity and all backup jobs
Failed because the DB´s are not in SINGEL USER mode!!! Ok so I tried to create a job
That runs before the maintenance plan with the ALTER DATABASE statement.
The ALTER DATABASE statement work fine in Query Analyser but fails if I want
To insert the statement in the job under STEPS.

So now I’m quite stuck?!!?!! But why can I create a Maintenance Plan with those
Settings if doesn’t run with the default settings taken during set up, what is my mistake?

Maybe because the recovery models are diffent???

Thx Andi

 

[osjohnm]

Hi

Have you selected the "Attempt to repair minor errors" option?

For SQL Server to do the above it has to put the database into single-user mode to "fix" the errors but if there is a connection to the database it won't be able to exclusively lock the database.

The other problem with that option is once it has changed the database to single-user mode and attempts to fix an error and can't fix the error the process fails and often the database remains in single-user mode.

That means that when your users come to work the following morning the application can't log onto the database because it is runnung in single-user mode.

Have a look at the following article about Maintenance Plans which should help you a lot.

http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp

Here are 2 links to Microsoft support articles on your problem, the first for sql 2000 and the 2nd for sql 7:

-- sql server 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;290622

-- sql server 7

http://support.microsoft.com/default.aspx?scid=kb;en-us;259551

John

 

[AHand]

Thx very much for your advise! At least most of the jobs are running without error. I just cant get a transaktionlog of the master db?! but i still try!

So i followed the advise in the links!

1. Create jobs (ad-hoc or using the wizard) for full backups of your databases with DBCC CHECKDB and CHECKCATALOG, and set up the option to perform the backup only if they didn’t report any errors. Create this job for all databases which don’t demand transaction log backups.
2. Create separate jobs for only transaction log backups for all databases which need it.
3. Create separate job(s) for data optimizations, depending on your demands.
4. For all of your jobs, add the HTML report option and direct it to a share point for easy access with a browser.

 

[osjohnm]

Hi

You won't find a transaction log backup as the master is a system database and uses the SIMPLE recovery model which doesn't support transaction log backups. You can't change the recovery model of the master database.

Hope this helps

John