Maintenance Plan

I am a newbie at this but I am a quick learner.
I am trying to setup a maintenance plans for both my Local
and Sharepoint instances of SQL 2000 SP3. When I set the
plans I enabled most every feature (obviously a newbie).
Needless to say, the maintenance plans failed on a couple
issues 1) "Attempt to repair any minor problems",
2) "Tansaction log backups".
I understand that if I disable the first item, it will
work - but what if I would like it to repair any minor
errors before it backed-up?
I also understand if I do not back-up the transaction logs
I will have problem rebuilding if necessary. It appears
that I should be able to invoke a couple of commands on
the SQL Server Agent\Jobs\Steps section to temporarily set
mode to single-user, then backup, then reset back to prior
state; Is this the way to go?
Or how can I enable both of these features?
Or do I need some serious help on this?
I appreciate any help I can get on this.
Paul
Things to remember - the 'Fix Minor Problems' select will require exclusive access to the database, so if you have attached users it will fail. Most likely you will want to run without this option, and review the logs daily to see if there are errors that
need fixing.
Also if you are running against all databases then transaction logs backups will fail, since there are system databases (MSDB, Master) that are set to simple recovery mode (ie they truncate the transaction log on checkpoints). You might want to consider u
sing a seperate plan for 'User Databases' to perform transaction log backups.
Kevin
Kevin Madsen
"Paul" wrote:

> I am a newbie at this but I am a quick learner.
> I am trying to setup a maintenance plans for both my Local
> and Sharepoint instances of SQL 2000 SP3. When I set the
> plans I enabled most every feature (obviously a newbie).
> Needless to say, the maintenance plans failed on a couple
> issues 1) "Attempt to repair any minor problems",
> 2) "Tansaction log backups".
> I understand that if I disable the first item, it will
> work - but what if I would like it to repair any minor
> errors before it backed-up?
> I also understand if I do not back-up the transaction logs
> I will have problem rebuilding if necessary. It appears
> that I should be able to invoke a couple of commands on
> the SQL Server Agent\Jobs\Steps section to temporarily set
> mode to single-user, then backup, then reset back to prior
> state; Is this the way to go?
> Or how can I enable both of these features?
> Or do I need some serious help on this?
> I appreciate any help I can get on this.
> Paul
>

Mail Profile: MAPI error 271: One or more receipts not found

I have a database server that has SQL Server 2000 with 2 instances
(default,RAC), that creates error from Mail Profile when tested. I have set
up the SQL Mail on several other servers on the network without an error.
When I test the Mail Profile under Mail Session and SQL Server Agent I
received the following error.
Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
MAPI error 271. One or more of the receipts was not found in the address
book.
Please help me resolve this error.
Thank You,
I had lots of issues with MAPI too, I ended up switching to SMTP. But you
are testing the mail from the operator?
Check your MAPI profile, and SQLAgent. One one server where I am not
allowed to switch from MAPI to SMTP. I had to recreate the profile, operator
to get everything working after network guys moved my MAPI account to another
server.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Joe K." wrote:

> I have a database server that has SQL Server 2000 with 2 instances
> (default,RAC), that creates error from Mail Profile when tested. I have set
> up the SQL Mail on several other servers on the network without an error.
> When I test the Mail Profile under Mail Session and SQL Server Agent I
> received the following error.
> Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
> MAPI error 271. One or more of the receipts was not found in the address
> book.
> Please help me resolve this error.
> Thank You,
|||I had the same problem - turned out the MAPI profile of the SQLAgent had
cached mode set up for the outlook client, so the address book it was looking
at was not current and the address I was trying to send it to was not in
there. I recommend logging in as the SQLAgent user on the server, open up
Outlook and try sending email to the address that's giving the error.
"Joe K." wrote:

> I have a database server that has SQL Server 2000 with 2 instances
> (default,RAC), that creates error from Mail Profile when tested. I have set
> up the SQL Mail on several other servers on the network without an error.
> When I test the Mail Profile under Mail Session and SQL Server Agent I
> received the following error.
> Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
> MAPI error 271. One or more of the receipts was not found in the address
> book.
> Please help me resolve this error.
> Thank You,

Mail Profile: MAPI error 271: One or more receipts not found

I have a database server that has SQL Server 2000 with 2 instances
(default,RAC), that creates error from Mail Profile when tested. I have set
up the SQL Mail on several other servers on the network without an error.
When I test the Mail Profile under Mail Session and SQL Server Agent I
received the following error.
Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
MAPI error 271. One or more of the receipts was not found in the address
book.
Please help me resolve this error.
Thank You,I had lots of issues with MAPI too, I ended up switching to SMTP. But you
are testing the mail from the operator?
Check your MAPI profile, and SQLAgent. One one server where I am not
allowed to switch from MAPI to SMTP. I had to recreate the profile, operato
r
to get everything working after network guys moved my MAPI account to anothe
r
server.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Joe K." wrote:

> I have a database server that has SQL Server 2000 with 2 instances
> (default,RAC), that creates error from Mail Profile when tested. I have se
t
> up the SQL Mail on several other servers on the network without an error.
> When I test the Mail Profile under Mail Session and SQL Server Agent I
> received the following error.
> Error 22022: SQLServerAgent: Unable to get default MAPI messge store due t
o
> MAPI error 271. One or more of the receipts was not found in the address
> book.
> Please help me resolve this error.
> Thank You,|||I had the same problem - turned out the MAPI profile of the SQLAgent had
cached mode set up for the outlook client, so the address book it was lookin
g
at was not current and the address I was trying to send it to was not in
there. I recommend logging in as the SQLAgent user on the server, open up
Outlook and try sending email to the address that's giving the error.
"Joe K." wrote:

> I have a database server that has SQL Server 2000 with 2 instances
> (default,RAC), that creates error from Mail Profile when tested. I have se
t
> up the SQL Mail on several other servers on the network without an error.
> When I test the Mail Profile under Mail Session and SQL Server Agent I
> received the following error.
> Error 22022: SQLServerAgent: Unable to get default MAPI messge store due t
o
> MAPI error 271. One or more of the receipts was not found in the address
> book.
> Please help me resolve this error.
> Thank You,

Mail Profile: MAPI error 271: One or more receipts not found

I have a database server that has SQL Server 2000 with 2 instances
(default,RAC), that creates error from Mail Profile when tested. I have set
up the SQL Mail on several other servers on the network without an error.
When I test the Mail Profile under Mail Session and SQL Server Agent I
received the following error.
Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
MAPI error 271. One or more of the receipts was not found in the address
book.
Please help me resolve this error.
Thank You,I had lots of issues with MAPI too, I ended up switching to SMTP. But you
are testing the mail from the operator?
Check your MAPI profile, and SQLAgent. One one server where I am not
allowed to switch from MAPI to SMTP. I had to recreate the profile, operator
to get everything working after network guys moved my MAPI account to another
server.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Joe K." wrote:
> I have a database server that has SQL Server 2000 with 2 instances
> (default,RAC), that creates error from Mail Profile when tested. I have set
> up the SQL Mail on several other servers on the network without an error.
> When I test the Mail Profile under Mail Session and SQL Server Agent I
> received the following error.
> Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
> MAPI error 271. One or more of the receipts was not found in the address
> book.
> Please help me resolve this error.
> Thank You,|||I had the same problem - turned out the MAPI profile of the SQLAgent had
cached mode set up for the outlook client, so the address book it was looking
at was not current and the address I was trying to send it to was not in
there. I recommend logging in as the SQLAgent user on the server, open up
Outlook and try sending email to the address that's giving the error.
"Joe K." wrote:
> I have a database server that has SQL Server 2000 with 2 instances
> (default,RAC), that creates error from Mail Profile when tested. I have set
> up the SQL Mail on several other servers on the network without an error.
> When I test the Mail Profile under Mail Session and SQL Server Agent I
> received the following error.
> Error 22022: SQLServerAgent: Unable to get default MAPI messge store due to
> MAPI error 271. One or more of the receipts was not found in the address
> book.
> Please help me resolve this error.
> Thank You,

Mail notifications for scheduled jobs

I have a sql server 2000 sp3a box with 4 named instances.

I have read all of the articles and I cannot get the 'send mail' stuff working.

I have a mailbox associated with the Domanin account that starts the sql server and sql server agent services. I installed Outlook and created a mail profile for this account. I bounced the SQL server agent on one of the Instances but when I go into the properties for the Agent, on the General Tab, the option to choose a mail profile is GREYED out. I tried 'xp_startmail' and I get this error: xp_startmail: failed with mail error 0x80040111

can anyone help???Are you logging on to the server as the account SQL runs under? You will need to be logged onto to the server with the service account in order to set the mail properties in the agent|||are you sure you have covered all of the basics. does the sql and sql agent services run under a network account that maps to the exchange account you are attempting to use?

have you attempted to use the outlook client installed on the db server.|||You've got about 9 months until maintstream support for SQL 2000 ends. Upgrade to SQL 2005 and use the Database Mail feature (a lightweight SMTP client, instead of the clunky MAPI compliant interface).

In case that is not an option, here are some MS KB articles on SQL 2000 and setting up mail profiles.

http://support.microsoft.com/kb/315886

http://support.microsoft.com/default.aspx/kb/281293

http://support.microsoft.com/kb/263556/EN-US/

Some CRITICAL things to remember:

1. Outlook is single-threaded; if it pops an error message at the console, it can and will lock up SQL's job system.

2. SQL Mail and SQL Agent mail do not have to use the same profile. However, you must set up each profile separately (ie, to use alerts you must set up SQL Agent Mail, to use xp_sendmail you must set up SQL Mail). The two features are set up in different places (SQL Agent mail is set up by right-clicking on SQL Agent and selecting properties; SQL Mail is set up under support services).

3. You will have to configure SQL Mail/SQL Agent Mail separately for each instance. You might want to consider a separate profile for each. Be careful of the overhead associated with running four MAPI client instances (plus for SQL instances) on the same server.

Having said all that, I don't really recommed using SQL Agent Mail or SQL mail on a production server. Like I said, upgrade to SQL 2005.

Regards,

hmscott

LSN Issues..

As a standard backup plan I am making Full backup, differential backup and
transactional backup. I had some instances where the SQL Server was throwing
error while restoring the transaction log related to log serial number. Some
thing it was saying too early to restore.. Or restore the previous log
before restoring this.
Anybody suggest me on what considerations I should make not to break this
LSN?
In summary, what jobs or routine that I should not run to make sure that my
transactional logs are recoverable?
SQL 2K.
Thanks
SmithIf you stay in full (or bulk logged) recovery mode, then it shouldn't be abl
e to break the log chain
sequence. If you see such a case, you have hit a bug in SQL Server and it is
time to hunt KB and
possibly open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
> As a standard backup plan I am making Full backup, differential backup and
> transactional backup. I had some instances where the SQL Server was throwi
ng
> error while restoring the transaction log related to log serial number. So
me
> thing it was saying too early to restore.. Or restore the previous log
> before restoring this.
> Anybody suggest me on what considerations I should make not to break this
> LSN?
> In summary, what jobs or routine that I should not run to make sure that m
y
> transactional logs are recoverable?
> SQL 2K.
> Thanks
> Smith
>|||Thanks for your answer.
One more clarification,
when you say "If you stay in full (or bulk logged) recovery mode", does it
means that after fullbackup I should not switch between recovery modes? Let
us say I switch between recovery modes (full or bulk logged) If I take a
fullbackup should it be fine?
or
After switching between should I restart the SQL Server service?
Thanks,
Smith
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OKQ$lwwcFHA.612@.TK2MSFTNGP12.phx.gbl...
> If you stay in full (or bulk logged) recovery mode, then it shouldn't be
able to break the log chain
> sequence. If you see such a case, you have hit a bug in SQL Server and it
is time to hunt KB and
> possibly open a case with MS Support.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
> news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
throwing[vbcol=seagreen]
Some[vbcol=seagreen]
this[vbcol=seagreen]
my[vbcol=seagreen]
>|||You can switch between full and bulk logged. But if you go to simple, you br
eak the log chain
sequence. No need to restart.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
news:%23945kL2cFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks for your answer.
> One more clarification,
> when you say "If you stay in full (or bulk logged) recovery mode", does it
> means that after fullbackup I should not switch between recovery modes? Le
t
> us say I switch between recovery modes (full or bulk logged) If I take a
> fullbackup should it be fine?
> or
> After switching between should I restart the SQL Server service?
> Thanks,
> Smith
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OKQ$lwwcFHA.612@.TK2MSFTNGP12.phx.gbl...
> able to break the log chain
> is time to hunt KB and
> and
> throwing
> Some
> this
> my
>

LSN Issues..

As a standard backup plan I am making Full backup, differential backup and
transactional backup. I had some instances where the SQL Server was throwing
error while restoring the transaction log related to log serial number. Some
thing it was saying too early to restore.. Or restore the previous log
before restoring this.
Anybody suggest me on what considerations I should make not to break this
LSN?
In summary, what jobs or routine that I should not run to make sure that my
transactional logs are recoverable?
SQL 2K.
Thanks
Smith
If you stay in full (or bulk logged) recovery mode, then it shouldn't be able to break the log chain
sequence. If you see such a case, you have hit a bug in SQL Server and it is time to hunt KB and
possibly open a case with MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
> As a standard backup plan I am making Full backup, differential backup and
> transactional backup. I had some instances where the SQL Server was throwing
> error while restoring the transaction log related to log serial number. Some
> thing it was saying too early to restore.. Or restore the previous log
> before restoring this.
> Anybody suggest me on what considerations I should make not to break this
> LSN?
> In summary, what jobs or routine that I should not run to make sure that my
> transactional logs are recoverable?
> SQL 2K.
> Thanks
> Smith
>
|||Thanks for your answer.
One more clarification,
when you say "If you stay in full (or bulk logged) recovery mode", does it
means that after fullbackup I should not switch between recovery modes? Let
us say I switch between recovery modes (full or bulk logged) If I take a
fullbackup should it be fine?
or
After switching between should I restart the SQL Server service?
Thanks,
Smith
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OKQ$lwwcFHA.612@.TK2MSFTNGP12.phx.gbl...
> If you stay in full (or bulk logged) recovery mode, then it shouldn't be
able to break the log chain
> sequence. If you see such a case, you have hit a bug in SQL Server and it
is time to hunt KB and[vbcol=seagreen]
> possibly open a case with MS Support.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
> news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
and[vbcol=seagreen]
throwing[vbcol=seagreen]
Some[vbcol=seagreen]
this[vbcol=seagreen]
my
>
|||You can switch between full and bulk logged. But if you go to simple, you break the log chain
sequence. No need to restart.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
news:%23945kL2cFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks for your answer.
> One more clarification,
> when you say "If you stay in full (or bulk logged) recovery mode", does it
> means that after fullbackup I should not switch between recovery modes? Let
> us say I switch between recovery modes (full or bulk logged) If I take a
> fullbackup should it be fine?
> or
> After switching between should I restart the SQL Server service?
> Thanks,
> Smith
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OKQ$lwwcFHA.612@.TK2MSFTNGP12.phx.gbl...
> able to break the log chain
> is time to hunt KB and
> and
> throwing
> Some
> this
> my
>

LSN Issues..

As a standard backup plan I am making Full backup, differential backup and
transactional backup. I had some instances where the SQL Server was throwing
error while restoring the transaction log related to log serial number. Some
thing it was saying too early to restore.. Or restore the previous log
before restoring this.
Anybody suggest me on what considerations I should make not to break this
LSN?
In summary, what jobs or routine that I should not run to make sure that my
transactional logs are recoverable?
SQL 2K.
Thanks
SmithIf you stay in full (or bulk logged) recovery mode, then it shouldn't be able to break the log chain
sequence. If you see such a case, you have hit a bug in SQL Server and it is time to hunt KB and
possibly open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
> As a standard backup plan I am making Full backup, differential backup and
> transactional backup. I had some instances where the SQL Server was throwing
> error while restoring the transaction log related to log serial number. Some
> thing it was saying too early to restore.. Or restore the previous log
> before restoring this.
> Anybody suggest me on what considerations I should make not to break this
> LSN?
> In summary, what jobs or routine that I should not run to make sure that my
> transactional logs are recoverable?
> SQL 2K.
> Thanks
> Smith
>|||Thanks for your answer.
One more clarification,
when you say "If you stay in full (or bulk logged) recovery mode", does it
means that after fullbackup I should not switch between recovery modes? Let
us say I switch between recovery modes (full or bulk logged) If I take a
fullbackup should it be fine?
or
After switching between should I restart the SQL Server service?
Thanks,
Smith
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OKQ$lwwcFHA.612@.TK2MSFTNGP12.phx.gbl...
> If you stay in full (or bulk logged) recovery mode, then it shouldn't be
able to break the log chain
> sequence. If you see such a case, you have hit a bug in SQL Server and it
is time to hunt KB and
> possibly open a case with MS Support.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
> news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
> > As a standard backup plan I am making Full backup, differential backup
and
> > transactional backup. I had some instances where the SQL Server was
throwing
> > error while restoring the transaction log related to log serial number.
Some
> > thing it was saying too early to restore.. Or restore the previous log
> > before restoring this.
> >
> > Anybody suggest me on what considerations I should make not to break
this
> > LSN?
> >
> > In summary, what jobs or routine that I should not run to make sure that
my
> > transactional logs are recoverable?
> >
> > SQL 2K.
> >
> > Thanks
> >
> > Smith
> >
> >
>|||You can switch between full and bulk logged. But if you go to simple, you break the log chain
sequence. No need to restart.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
news:%23945kL2cFHA.3488@.tk2msftngp13.phx.gbl...
> Thanks for your answer.
> One more clarification,
> when you say "If you stay in full (or bulk logged) recovery mode", does it
> means that after fullbackup I should not switch between recovery modes? Let
> us say I switch between recovery modes (full or bulk logged) If I take a
> fullbackup should it be fine?
> or
> After switching between should I restart the SQL Server service?
> Thanks,
> Smith
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OKQ$lwwcFHA.612@.TK2MSFTNGP12.phx.gbl...
>> If you stay in full (or bulk logged) recovery mode, then it shouldn't be
> able to break the log chain
>> sequence. If you see such a case, you have hit a bug in SQL Server and it
> is time to hunt KB and
>> possibly open a case with MS Support.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Smith" <Hose_smith_Ireland@.hotmail.com> wrote in message
>> news:%23EAZvJrcFHA.4040@.TK2MSFTNGP10.phx.gbl...
>> > As a standard backup plan I am making Full backup, differential backup
> and
>> > transactional backup. I had some instances where the SQL Server was
> throwing
>> > error while restoring the transaction log related to log serial number.
> Some
>> > thing it was saying too early to restore.. Or restore the previous log
>> > before restoring this.
>> >
>> > Anybody suggest me on what considerations I should make not to break
> this
>> > LSN?
>> >
>> > In summary, what jobs or routine that I should not run to make sure that
> my
>> > transactional logs are recoverable?
>> >
>> > SQL 2K.
>> >
>> > Thanks
>> >
>> > Smith
>> >
>> >
>>
>