maintenance plan

We have two SQL 2000 servers, one runs on SP2 and another
one runs on SP3, I set up the same maintanence plans on
them and selected 'attemp to repair any minor problems'
but the job 'Integrity Check' failed at 'Check data and
index linkage' due to 'database needs to be in single user
mode' on both user and system databases. Any idea what I
did wrong? OS is Win2K SP3.
Many thanks!You didn't do anything wrong, it needs to be in single user mode to do this
and if there is any connection in the db it can't put it in single user
mode. You should uncheck this anyway. Let it report the errors and then
you decide how and when to fix them.
--
Andrew J. Kelly
SQL Server MVP
"Ray Yan" <remonyan@.hotmail.com> wrote in message
news:61d901c34107$597b1180$7d02280a@.phx.gbl...
> We have two SQL 2000 servers, one runs on SP2 and another
> one runs on SP3, I set up the same maintanence plans on
> them and selected 'attemp to repair any minor problems'
> but the job 'Integrity Check' failed at 'Check data and
> index linkage' due to 'database needs to be in single user
> mode' on both user and system databases. Any idea what I
> did wrong? OS is Win2K SP3.
> Many thanks!

Maintenance Plan

I'm running an SBS2000 server with SQL serving up about 20 small databases.
I have a maintenance plan in place that runs every night backing up each of
the databases to a folder which in turn gets backed up by tape two hours
later. I have the plan configured to delete transaction logs and database
files older than 2 days. The backups are running fine, but the plan is not
deleting the old backup files and transaction logs.I have had that issue in the past and found that the
backups don't always complete at the same time. If you
setup a maintenance plan to remove backups older than 1
day and you do the following;
You had the 1st backup run and complete at 12:04.
The next day, the backup is a little quicker and finished
at 12:03. The backup from the previous day won't be
deleted as a FULL 24 hours haven't passed. I modified my
setting to delete after 23 hours.
Hope this helps.
Thanks
Fred
>--Original Message--
>I'm running an SBS2000 server with SQL serving up about
20 small databases.
>I have a maintenance plan in place that runs every night
backing up each of
>the databases to a folder which in turn gets backed up by
tape two hours
>later. I have the plan configured to delete transaction
logs and database
>files older than 2 days. The backups are running fine,
but the plan is not
>deleting the old backup files and transaction logs.
>.
>|||Could be that you have included databases for log backup that are in simple
recovery mode.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RScott" <anonymous@.discussions.microsoft.com> wrote in message
news:0E5C4CEC-1C97-4ADB-8D5E-97F549E0C495@.microsoft.com...
> I'm running an SBS2000 server with SQL serving up about 20 small
databases.
> I have a maintenance plan in place that runs every night backing up each
of
> the databases to a folder which in turn gets backed up by tape two hours
> later. I have the plan configured to delete transaction logs and database
> files older than 2 days. The backups are running fine, but the plan is
not
> deleting the old backup files and transaction logs.sql

Maintenance job fails

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
/jimYou 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|||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|||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|||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|||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

maint plan job hangs

I have a maint plan that backups up the t-logs of all the user databases,
while executing, the job runs nice and fast, but at the very end, when all
the backups are complete and you expect to see the job status to change from
'Executing' to 'Not Running', it doesn't.
the report is missing the last portion also, which should be :
"End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
SQLMAINT.EXE Process Exit Code: 0 (Success)"
Anybody know why ?
Is there a way to set a more verbose output ?
"Johnny" <Johnny@.Knoxville.com> wrote in message
news:OBdqNwruEHA.3976@.TK2MSFTNGP09.phx.gbl...
> I have a maint plan that backups up the t-logs of all the user databases,
> while executing, the job runs nice and fast, but at the very end, when all
> the backups are complete and you expect to see the job status to change
from
> 'Executing' to 'Not Running', it doesn't.
Do you have it setup to email upon completion?
Is Email working?
This is the most common cause I've seen.

> the report is missing the last portion also, which should be :
> "End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
> SQLMAINT.EXE Process Exit Code: 0 (Success)"
> Anybody know why ?
> Is there a way to set a more verbose output ?
>
>
|||no e-mail setup
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:kChfd.329041$bp1.38790@.twister.nyroc.rr.com.. .[vbcol=seagreen]
> "Johnny" <Johnny@.Knoxville.com> wrote in message
> news:OBdqNwruEHA.3976@.TK2MSFTNGP09.phx.gbl...
databases,[vbcol=seagreen]
all
> from
> Do you have it setup to email upon completion?
> Is Email working?
> This is the most common cause I've seen.
>
>

maint plan job hangs

I have a maint plan that backups up the t-logs of all the user databases,
while executing, the job runs nice and fast, but at the very end, when all
the backups are complete and you expect to see the job status to change from
'Executing' to 'Not Running', it doesn't.
the report is missing the last portion also, which should be :
"End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
SQLMAINT.EXE Process Exit Code: 0 (Success)"
Anybody know why ?
Is there a way to set a more verbose output ?"Johnny" <Johnny@.Knoxville.com> wrote in message
news:OBdqNwruEHA.3976@.TK2MSFTNGP09.phx.gbl...
> I have a maint plan that backups up the t-logs of all the user databases,
> while executing, the job runs nice and fast, but at the very end, when all
> the backups are complete and you expect to see the job status to change
from
> 'Executing' to 'Not Running', it doesn't.
Do you have it setup to email upon completion?
Is Email working?
This is the most common cause I've seen.
> the report is missing the last portion also, which should be :
> "End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
> SQLMAINT.EXE Process Exit Code: 0 (Success)"
> Anybody know why ?
> Is there a way to set a more verbose output ?
>
>|||no e-mail setup
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:kChfd.329041$bp1.38790@.twister.nyroc.rr.com...
> "Johnny" <Johnny@.Knoxville.com> wrote in message
> news:OBdqNwruEHA.3976@.TK2MSFTNGP09.phx.gbl...
> > I have a maint plan that backups up the t-logs of all the user
databases,
> > while executing, the job runs nice and fast, but at the very end, when
all
> > the backups are complete and you expect to see the job status to change
> from
> > 'Executing' to 'Not Running', it doesn't.
> Do you have it setup to email upon completion?
> Is Email working?
> This is the most common cause I've seen.
>
> >
> > the report is missing the last portion also, which should be :
> > "End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
> > SQLMAINT.EXE Process Exit Code: 0 (Success)"
> >
> > Anybody know why ?
> > Is there a way to set a more verbose output ?
> >
> >
> >
>

maint plan job hangs

I have a maint plan that backups up the t-logs of all the user databases,
while executing, the job runs nice and fast, but at the very end, when all
the backups are complete and you expect to see the job status to change from
'Executing' to 'Not Running', it doesn't.
the report is missing the last portion also, which should be :
"End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
SQLMAINT.EXE Process Exit Code: 0 (Success)"
Anybody know why ?
Is there a way to set a more verbose output ?"Johnny" <Johnny@.Knoxville.com> wrote in message
news:OBdqNwruEHA.3976@.TK2MSFTNGP09.phx.gbl...
> I have a maint plan that backups up the t-logs of all the user databases,
> while executing, the job runs nice and fast, but at the very end, when all
> the backups are complete and you expect to see the job status to change
from
> 'Executing' to 'Not Running', it doesn't.
Do you have it setup to email upon completion?
Is Email working?
This is the most common cause I've seen.

> the report is missing the last portion also, which should be :
> "End of maintenance plan 'All_General' on 10/24/2004 3:00:30 PM
> SQLMAINT.EXE Process Exit Code: 0 (Success)"
> Anybody know why ?
> Is there a way to set a more verbose output ?
>
>|||no e-mail setup
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:kChfd.329041$bp1.38790@.twister.nyroc.rr.com...
> "Johnny" <Johnny@.Knoxville.com> wrote in message
> news:OBdqNwruEHA.3976@.TK2MSFTNGP09.phx.gbl...
databases,[vbcol=seagreen]
all[vbcol=seagreen]
> from
> Do you have it setup to email upon completion?
> Is Email working?
> This is the most common cause I've seen.
>
>

Mail failure in DTS package

Hi, Im trying to do a DTS that exports data to a excel file and then
mail it. It runs fine all way throw until the mail object get an
error. Like this:
Logon failed: Unable to get the default MAPI Message Store due to MAPI
error 273: Det g=E5r inte att =F6ppna informationsarkivet.
Does any one know what to do?
Cheers! /Karinkarin.w74@.hotmail.com wrote:
> Hi, Im trying to do a DTS that exports data to a excel file and then
> mail it. It runs fine all way throw until the mail object get an
> error. Like this:
> Logon failed: Unable to get the default MAPI Message Store due to MAPI
> error 273: Det går inte att öppna informationsarkivet.
> Does any one know what to do?
> Cheers! /Karin
Hi,
It sounds like the user that runs the SQLServer service hasn't got
access to logon to either the mailbox or to the Exchange server you are
using. You might have to check the the user that runs SQL Server also
is the one that has an Outlook profile setup on the server - otherwise
you might run into the problem.
Regards
Steen Schlüter Persson
CRM System Specialist / DBA
Denmark

Mail failure in DTS package

Hi, Im trying to do a DTS that exports data to a excel file and then
mail it. It runs fine all way throw until the mail object get an
error. Like this:
Logon failed: Unable to get the default MAPI Message Store due to MAPI
error 273: Det g=E5r inte att =F6ppna informationsarkivet.
Does any one know what to do?
Cheers! /K"liteblandat@.hotmail.com" wrote:
> Hi, Im trying to do a DTS that exports data to a excel file and then
> mail it. It runs fine all way throw until the mail object get an
> error. Like this:
> Logon failed: Unable to get the default MAPI Message Store due to MAPI
> error 273: Det går inte att öppna informationsarkivet.
> Does any one know what to do?
> Cheers! /K
>
Hi
Are you running this package from the designer or a job? The issue is that
the account under which it is running, does not have access to the MAPI
profile that you have configured for SQLMail. Check the accounts that SQL
Server and SQL Agent have been configured to run as and that they can access
the profile you have set up. Try logging in on the server as these accounts
and sending a test email.
HTH
John