By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,640 Members | 1,598 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,640 IT Pros & Developers. It's quick & easy.

Maintenance job fails

P: n/a
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 "Optimizations" 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..sysprocesses
where dbid = db_id('foo')
---------

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

thx
/jim
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

P: n/a
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

Jul 23 '05 #5

P: n/a
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

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.