maintenance plan in enterprise manager not functioning quite right

hello,
wondering if anyone could give me some insight. i've created two
maintenance plans for each of two sql servers (production and test). the
first of each is supposed to check the master, model, and msdb dbs and then
do a complete backup of them as well as their transaction logs. the second
of each does the same for all my user dbs.
all dbs are set to recovery model full except for the master and msdb dbs.
the plan is basically working, except that it is skipping a few of the user
dbs, and it is also skipping the master and msdb dbs on both servers. that
is, i see no mention of them in the logs, and no backup files are created on
disk.
what am i doing wrong?
appreciated,
matthew
Well it can't issue a log backup on Master and MSDB since they are in simple
mode. My guess is the others are as well.
Andrew J. Kelly SQL MVP
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in
message news:8EC5F016-98C3-4712-8F4D-F1B84B2A20E5@.microsoft.com...
> hello,
> wondering if anyone could give me some insight. i've created two
> maintenance plans for each of two sql servers (production and test). the
> first of each is supposed to check the master, model, and msdb dbs and
then
> do a complete backup of them as well as their transaction logs. the
second
> of each does the same for all my user dbs.
> all dbs are set to recovery model full except for the master and msdb dbs.
> the plan is basically working, except that it is skipping a few of the
user
> dbs, and it is also skipping the master and msdb dbs on both servers.
that
> is, i see no mention of them in the logs, and no backup files are created
on
> disk.
> what am i doing wrong?
> appreciated,
> matthew

Maintenance Plan failures - Incorrect SET options

All,
I'm having trouble figuring this one out. I've got a maintenance plan
performing both integrity checks and optimizations on several user
dbs. The plan succeeds on 8 of the databases but fails on the other
six with the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Now, here is the weird part, when I check the settings of the
databases that are succeding vs. the ones that are failing, the exact
same SET options are turned on. Here is an example:
Maintenance succeeds on this db:
The following options are set:
torn page detection
arithabort
ANSI null default
quoted identifier
auto create statistics
auto update statistics
Maintenance fails on this db:
The following options are set:
torn page detection
arithabort
ANSI null default
quoted identifier
auto create statistics
auto update statistics
Has anyone experienced this behavior?
Thanks in advance.
The settings at the database level are confusing, and IMO worthless, as they are overridden by the
applications (SQL Agent and sqlmaint in this case) anyhow. I suggest that you either remove the stuff that
requires these settings, or write your own maint jobs and execute as TSQL jobsteps from where you can execute
the needed SET commands.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sqlboy2000" <sqlboy2000@.hotmail.com> wrote in message news:49e85cd3.0406080459.3b2819e8@.posting.google.c om...
> All,
> I'm having trouble figuring this one out. I've got a maintenance plan
> performing both integrity checks and optimizations on several user
> dbs. The plan succeeds on 8 of the databases but fails on the other
> six with the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Now, here is the weird part, when I check the settings of the
> databases that are succeding vs. the ones that are failing, the exact
> same SET options are turned on. Here is an example:
> Maintenance succeeds on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
>
> Maintenance fails on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
> Has anyone experienced this behavior?
> Thanks in advance.
|||do you have any computed columns in those db's that it fails?
i've had a very similar problem where statistics were getting created on
computed columns causing problems with some of the dbmp jobs.
if you have computed columns, go make sure there are no indexes on them
and delete any statistics for those columns.
sqlboy2000 wrote:
> All,
> I'm having trouble figuring this one out. I've got a maintenance plan
> performing both integrity checks and optimizations on several user
> dbs. The plan succeeds on 8 of the databases but fails on the other
> six with the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the
> following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
> Now, here is the weird part, when I check the settings of the
> databases that are succeding vs. the ones that are failing, the exact
> same SET options are turned on. Here is an example:
> Maintenance succeeds on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
> Maintenance fails on this db:
> The following options are set:
> --
> torn page detection
> arithabort
> ANSI null default
> quoted identifier
> auto create statistics
> auto update statistics
> Has anyone experienced this behavior?
> Thanks in advance.

Maintenance Plan Configuration

Hi,
I've just started using SQL Server 2005 Standard and I'm moving database
across from SQL Server 2000.
I have setup a maintenance plan to backup my databases in the new
maintenance plan tool / SSIS but I cannot find any option to delete old
database backup files like I have in SQL Server 2000.
I found the Clean-up History task, but this does not appear to be related to
deleting database backup files.
Can someone please point me in the right direction for configuring this
option so I do not need to write my own job to delete old backup files.
John
add a "Maintenance Clean Up" task to your Maintenance plan. This will
give you the option to delete old backfiles or logfiles.
Markus

Maintainance Plan - Transaction Log Backups Not Deleted

Hi
Ive got a Maintainance Plan set up for all my databases.
The Database backup part works fine but I have a problem
with the Transaction log backup. I have set it to delete
old TRN files after 4 days. However it does not to this.
It creates a new TRN file, but does not delete the old
ones. I have looked in the job history - it just says the
job failed. I have checked the NTFS permissions - they
seem ok. Any ideas
TIA
RobProblem is that one of your databases recovery model is
set to simple. (microsoft knowledge base article 303292)
go to enterprise manager| click down to databases| right
click on each database in your maintenance plan | select
properties| select options| each one should NOT be simple
select either full or bulk
>--Original Message--
>Hi
>Ive got a Maintainance Plan set up for all my databases.
>The Database backup part works fine but I have a problem
>with the Transaction log backup. I have set it to delete
>old TRN files after 4 days. However it does not to this.
>It creates a new TRN file, but does not delete the old
>ones. I have looked in the job history - it just says
the
>job failed. I have checked the NTFS permissions - they
>seem ok. Any ideas
>TIA
>Rob
>.
>|||You pretty much just get that generic message when you use
the maintenance plans. To find the specific errors, you need
to go to the maintenance plans, right click on the plan that
is failing and select Database Maintenance Plan History.
The problem with deleting the backups xx days old is most
likely related to one of the issues that Allan Mitchell
listed for you in his post - permissions or a sharing
violation.
-Sue
On 22 Sep 2003 04:36:22 -0700, rss@.abasoft.co.uk (Rob Smith)
wrote:
>Thanks for that tip but, each database was set to Full
>In the step detail in job history I get this
>sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
>Rob
>
>"bob schwartz" <robtschwartz@.sbcglobal.net> wrote in message news:<01c801c37c7f$411100b0$a401280a@.phx.gbl>...
>> Problem is that one of your databases recovery model is
>> set to simple. (microsoft knowledge base article 303292)
>> go to enterprise manager| click down to databases| right
>> click on each database in your maintenance plan | select
>> properties| select options| each one should NOT be simple
>> select either full or bulk
>>|||I had tried Allans advice, but it didnt seem to make a difference who
was running it. In the end I have got it working by going into SQL
Agent/Jobs/Transaction Log Backup/Properties/Steps and changing the
database from master to one of my user DBs. This ran ok with no errors
and removed the old files.
Thanks to everyone who helped with this
Rob
Sue Hoegemeier <Sue_H@.nomail.please> wrote in message news:<8n2vmvk74vhupjjfj9h96lrjpn0kcsvvd0@.4ax.com>...
> You pretty much just get that generic message when you use
> the maintenance plans. To find the specific errors, you need
> to go to the maintenance plans, right click on the plan that
> is failing and select Database Maintenance Plan History.
> The problem with deleting the backups xx days old is most
> likely related to one of the issues that Allan Mitchell
> listed for you in his post - permissions or a sharing
> violation.
> -Sue
> On 22 Sep 2003 04:36:22 -0700, rss@.abasoft.co.uk (Rob Smith)
> wrote:
> >Thanks for that tip but, each database was set to Full
> >
> >In the step detail in job history I get this
> >
> >sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
> >
> >Rob
> >
> >
> >"bob schwartz" <robtschwartz@.sbcglobal.net> wrote in message news:<01c801c37c7f$411100b0$a401280a@.phx.gbl>...
> >> Problem is that one of your databases recovery model is
> >> set to simple. (microsoft knowledge base article 303292)
> >> go to enterprise manager| click down to databases| right
> >> click on each database in your maintenance plan | select
> >> properties| select options| each one should NOT be simple
> >> select either full or bulk
> >>
> >>

mail without mapi

Hi All,
I've got a SQL cluster in its own domain that I need to get notifications
from when the maintenance jobs fail. If I can't use an outlook mapi client
to send mail how can I get notified that jobs fail? Someone said I could use
SMTP mail but I don't know how to do this. Please help.
TIA!!
jj
Use xpsmtp from www.sqldev.net. I have used this in clustered environments
for a long time and have had zero problems.
Geoff N. Hiten
Microsoft SQL Server MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OcWeEBAeFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I've got a SQL cluster in its own domain that I need to get notifications
> from when the maintenance jobs fail. If I can't use an outlook mapi client
> to send mail how can I get notified that jobs fail? Someone said I could
> use SMTP mail but I don't know how to do this. Please help.
> TIA!!
> jj
>