Maintenance job fails

[Jim Andersen]

Hi there,

Used the wizard to create a maintenance plan for a db. But it fails most of
the time. I checked the log-files.

At 1.00 AM, it runs the "Optimizati ons" job for 6-9 seconds. It succeeds
always.

At 1.05 AM, for 1 second, it runs "integrity check", but most of the time it
fails, and says that it couldn't switch to single-user because other users
are using the database.

And when "integrity check" fails the "backup" job won't run at 1.10 AM. When
it DOES run it takes 2 seconds.

How do I find out who is using the database ? How do I put a sp_who (or
similar) in the "integrity check" job ?
I got this tip:
----------
Assuming you have SQL2000, then you can use something like this to
kick out all the users:

ALTER DATABASE foo SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

If you want to know who is using the database, then this is one way:

select suser_sname(sid )
from master..sysproc esses
where dbid = db_id('foo')
---------

But how do I actually do it ? How do I edit a maintenance plan ?

thx
/jim

[Stu]

You don't have to edit the maintenance plan; you can either add a step
to the job before the maintenance plan runs, or create a new job that
runs prior to you maintenance plan.

Stu

[rcamarda]

in EM, Managment> SQL Server Agent>Jobs > Properties > Steps . You will
see the steps, which you can edit or insert. I would imagine you could
insert a step before the integrity to do the commands you posted.
HTH

[Simon Hayes]

I'm not sure I completely follow your series of events, but I would try
using multiple steps in a single job. In the first step, set the
database to RESTRICTED_USER ; in the second, run your integrity checks;
in the third, run the backup; in the fourth, set the database back to
MULTI_USER.

If you do set this up, one common error is to set the Database in the
job steps to the user database, instead of master. If you don't choose
master, then the job can block itself.

Simon

[johnbandettini]

Guys

We seem to be missing the main point here. The maint plan is trying to
put the database into single user mode because you have ticked the
attempt to repair minor problems option for the database integrity
checks.

As the vast majority of times this check runs, it will find no errors,
a lot of sites consider this unecessary to do every night. An option to
consider is to remove this option, which solves your imediate problem.
Check the output from the maint plan regularly and if you do get any
errors, schedule a run of DBCC CHECKDB with applicable options at a
time when you can arrange to not have users on the system.

As for editing your maintenance plan. In EM open the management folder
and highlight Database maintenance plans. Double click the plan in the
right paine and you can now change it.

Hope this helps

John

[Mario]

My trick is to schedule the maintenance right after my system is
recycled during the night.
That way, I dont have to deal with this nagging issue.

Mario