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 becau
se 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 yo
u either remove the stuff that
requires these settings, or write your own maint jobs and execute as TSQL jo
bsteps 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@.pos
ting.google.com...
> 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 bec
ause 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 bec
ause 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 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 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.com...
> 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 email report failure

Hello,
I have a simple daily maintenance plan for backups.(check integrity, back up, clean up history and then report) However, sending the email report always fails with the following error:

"Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.Incorrect syntax near 'sys'."

How can the wizard output Incorrect syntax?

I have set up and tested Database mail and set the default public profile.
I have enabled the mail profile in SQL server agent properties and then restarted SQL server agent.
I have set up operator to receive the mail.
The frustrating thing is that if I set up and schedule a really simple test maintenance plan, the email report works! But it always fails on the real one.
How can I get more detail in the error message and actually see the SQL it is trying to execute when it send the mail?

Thanks,
Adam

More detail.

Having done a few tests it seems that this mail report error only occurs after the check integrity job fails. eg.

Executing the query "DBCC CHECKDB WITH NO_INFOMSGS
" failed with the following error:

"Incorrect PFS free space information for page (1:278) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).

CHECKDB found 0 allocation errors and 1 consistency errors in database 'RHJ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (RHJ).".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This failure does not affect the backup and maintenance cleanup tasks that run afterwards, only the email report that it tries to send at the end.

Maintenance Plan DB Integrity Job Failed

I created a SQL Server 2000 database maintenance plan with the wizard.
I selected integrity checks and complete backups for my database and
completed the requiremenents.
I received the following error listed below.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42
000]
(Error 22029). The step failed.
Please help me resolve this error.Joe
Did you check an Error log for any info there?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Yes, I checked the SQL Server error log.
Nothing was in the error log that correponds to this error.
Thanks,
"Uri Dimant" wrote:

> Joe
> Did you check an Error log for any info there?
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
>
>|||Also, which job did this fail on?
For example, if you have a tran log backup job and you have mixed full
recovery model databases with simple model, you will get an error (even
though the backups completed, the error will be thrown). Its always a good
idea to separate out the system, full model user, bulk mode user and simple
model user database backups into separate plans, and configure each
appropriately.
Awaiting your response for more info as per Uri.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Joe K." wrote:

> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Specify a report file for the main plan and check that report file for error
messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>sql

Maintenance Plan DB Integrity Job Failed

I created a SQL Server 2000 database maintenance plan with the wizard.
I selected integrity checks and complete backups for my database and
completed the requiremenents.
I received the following error listed below.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
Please help me resolve this error.
Joe
Did you check an Error log for any info there?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>
|||Yes, I checked the SQL Server error log.
Nothing was in the error log that correponds to this error.
Thanks,
"Uri Dimant" wrote:

> Joe
> Did you check an Error log for any info there?
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
>
>
|||Also, which job did this fail on?
For example, if you have a tran log backup job and you have mixed full
recovery model databases with simple model, you will get an error (even
though the backups completed, the error will be thrown). Its always a good
idea to separate out the system, full model user, bulk mode user and simple
model user database backups into separate plans, and configure each
appropriately.
Awaiting your response for more info as per Uri.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Joe K." wrote:

> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>
|||Specify a report file for the main plan and check that report file for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>

Maintenance Plan DB Integrity Job Failed

I created a SQL Server 2000 database maintenance plan with the wizard.
I selected integrity checks and complete backups for my database and
completed the requiremenents.
I received the following error listed below.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
(Error 22029). The step failed.
Please help me resolve this error.Joe
Did you check an Error log for any info there?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Yes, I checked the SQL Server error log.
Nothing was in the error log that correponds to this error.
Thanks,
"Uri Dimant" wrote:
> Joe
> Did you check an Error log for any info there?
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> >
> > I created a SQL Server 2000 database maintenance plan with the wizard.
> > I selected integrity checks and complete backups for my database and
> > completed the requiremenents.
> >
> > I received the following error listed below.
> >
> > Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> > 42000]
> > (Error 22029). The step failed.
> >
> > Please help me resolve this error.
> >
>
>|||Also, which job did this fail on?
For example, if you have a tran log backup job and you have mixed full
recovery model databases with simple model, you will get an error (even
though the backups completed, the error will be thrown). Its always a good
idea to separate out the system, full model user, bulk mode user and simple
model user database backups into separate plans, and configure each
appropriately.
Awaiting your response for more info as per Uri.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Joe K." wrote:
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>|||Specify a report file for the main plan and check that report file for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:2B2D49CA-5E9F-4B76-AE3E-CE9F52E6B802@.microsoft.com...
> I created a SQL Server 2000 database maintenance plan with the wizard.
> I selected integrity checks and complete backups for my database and
> completed the requiremenents.
> I received the following error listed below.
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000]
> (Error 22029). The step failed.
> Please help me resolve this error.
>

Maintenance Job failure

I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?Yes, you can run sp_who to see who is in the database currently.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
I'm getting the following error when my database is
integrity checked. Error 5070: Database state cannot be
changed while other users are using database.
Is there anyway to tell who or what is using the database?|||Run sp_who2 from within Query Analyzer, to see who are accessing the
database you are checking.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?|||You should not run the automatic repair option as part of checking
consistency (this option has been removed in SQL Server 2005). Firstly it
requires changing the database state to single_user mode and secondly you
should always do root cause analysis of any inconsistencies.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:223c001c45d25$a6206310$a301280a@.phx.gbl...
> I'm getting the following error when my database is
> integrity checked. Error 5070: Database state cannot be
> changed while other users are using database.
> Is there anyway to tell who or what is using the database?

maintenance fails

Hi
Im running a Windows 2003 server and MSSQL 2000 that manages some 45
databases. I created an maintenance plan that does an integrity check,
backups all databases, and backup all transactionlogs.
If I look at the "Jobs" list it looks like backing up the databases runs OK,
but both the integrity check and transaction log seams to fail.
If I look at the folder holding the backupfiles all transactionfiles looks
to be there (backup of all the transaction logs). So what makes the job
fail? If I look at the event log all transaction-log-backups complete OK,
but still the job ends in failure and the eventlog only states that the job
faild - not why it failed.
How can I track this problem? And is this why the transaction logs are not
truncated?
Regards
Jan Agermose
Hi
Within a job specify an output file to see what is going on.
<msde@.csite.com> wrote in message
news:epoOEnVUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs
OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the
job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>
|||Hello Jan,
Go to your maintenance plan in enterprise manager and right click on it.
Select "Maintenance Plan History...". In the Status dropdown, select
"Failed". You should now see a list of failed tasks in your plan. Look for a
plan with an activity of "Backup Transaction Log" and double click it. The
reason for failure will appear. My guess is that the database is in simple
recovery mode and you will get an error similar to "Backup can not be
performed on this database. This sub task is ignored".
All you have to do in this instance is remove databases that have simple
recovery from your maintenance plan, or change them to a different recovery
model (if that's what you need).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"msde@.csite.com" wrote:

> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>
|||Ahhh :-) Of cause - I selsected to backup all databases - this includes
"master", "tempdb" and the test database "northwind". Doing a backup of the
transaction log fails.
I guess that means I need two plans
One for all user databases but not the system databases - this plan does a
databasebackup and a transactionlog backup.
And one for the system databases that does not try to backup the
transactionlog?
Regards
Jan
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...[vbcol=seagreen]
> Hello Jan,
> Go to your maintenance plan in enterprise manager and right click on it.
> Select "Maintenance Plan History...". In the Status dropdown, select
> "Failed". You should now see a list of failed tasks in your plan. Look for
> a
> plan with an activity of "Backup Transaction Log" and double click it. The
> reason for failure will appear. My guess is that the database is in simple
> recovery mode and you will get an error similar to "Backup can not be
> performed on this database. This sub task is ignored".
> All you have to do in this instance is remove databases that have simple
> recovery from your maintenance plan, or change them to a different
> recovery
> model (if that's what you need).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "msde@.csite.com" wrote:
|||You got the point, Jan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<msde@.csite.com> wrote in message news:O8YUBjbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Ahhh :-) Of cause - I selsected to backup all databases - this includes "master", "tempdb" and the
> test database "northwind". Doing a backup of the transaction log fails.
> I guess that means I need two plans
> One for all user databases but not the system databases - this plan does a databasebackup and a
> transactionlog backup.
> And one for the system databases that does not try to backup the transactionlog?
> Regards
> Jan
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...
>

maintenance fails

Hi
Im running a Windows 2003 server and MSSQL 2000 that manages some 45
databases. I created an maintenance plan that does an integrity check,
backups all databases, and backup all transactionlogs.
If I look at the "Jobs" list it looks like backing up the databases runs OK,
but both the integrity check and transaction log seams to fail.
If I look at the folder holding the backupfiles all transactionfiles looks
to be there (backup of all the transaction logs). So what makes the job
fail? If I look at the event log all transaction-log-backups complete OK,
but still the job ends in failure and the eventlog only states that the job
faild - not why it failed.
How can I track this problem? And is this why the transaction logs are not
truncated?
Regards
Jan AgermoseHi
Within a job specify an output file to see what is going on.
<msde@.csite.com> wrote in message
news:epoOEnVUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs
OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the
job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>|||Hello Jan,
Go to your maintenance plan in enterprise manager and right click on it.
Select "Maintenance Plan History...". In the Status dropdown, select
"Failed". You should now see a list of failed tasks in your plan. Look for a
plan with an activity of "Backup Transaction Log" and double click it. The
reason for failure will appear. My guess is that the database is in simple
recovery mode and you will get an error similar to "Backup can not be
performed on this database. This sub task is ignored".
All you have to do in this instance is remove databases that have simple
recovery from your maintenance plan, or change them to a different recovery
model (if that's what you need).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"msde@.csite.com" wrote:
> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>|||Ahhh :-) Of cause - I selsected to backup all databases - this includes
"master", "tempdb" and the test database "northwind". Doing a backup of the
transaction log fails.
I guess that means I need two plans
One for all user databases but not the system databases - this plan does a
databasebackup and a transactionlog backup.
And one for the system databases that does not try to backup the
transactionlog?
Regards
Jan
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...
> Hello Jan,
> Go to your maintenance plan in enterprise manager and right click on it.
> Select "Maintenance Plan History...". In the Status dropdown, select
> "Failed". You should now see a list of failed tasks in your plan. Look for
> a
> plan with an activity of "Backup Transaction Log" and double click it. The
> reason for failure will appear. My guess is that the database is in simple
> recovery mode and you will get an error similar to "Backup can not be
> performed on this database. This sub task is ignored".
> All you have to do in this instance is remove databases that have simple
> recovery from your maintenance plan, or change them to a different
> recovery
> model (if that's what you need).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "msde@.csite.com" wrote:
>> Hi
>> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
>> databases. I created an maintenance plan that does an integrity check,
>> backups all databases, and backup all transactionlogs.
>> If I look at the "Jobs" list it looks like backing up the databases runs
>> OK,
>> but both the integrity check and transaction log seams to fail.
>> If I look at the folder holding the backupfiles all transactionfiles
>> looks
>> to be there (backup of all the transaction logs). So what makes the job
>> fail? If I look at the event log all transaction-log-backups complete OK,
>> but still the job ends in failure and the eventlog only states that the
>> job
>> faild - not why it failed.
>> How can I track this problem? And is this why the transaction logs are
>> not
>> truncated?
>> Regards
>> Jan Agermose
>>
>>|||You got the point, Jan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<msde@.csite.com> wrote in message news:O8YUBjbUFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Ahhh :-) Of cause - I selsected to backup all databases - this includes "master", "tempdb" and the
> test database "northwind". Doing a backup of the transaction log fails.
> I guess that means I need two plans
> One for all user databases but not the system databases - this plan does a databasebackup and a
> transactionlog backup.
> And one for the system databases that does not try to backup the transactionlog?
> Regards
> Jan
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...
>> Hello Jan,
>> Go to your maintenance plan in enterprise manager and right click on it.
>> Select "Maintenance Plan History...". In the Status dropdown, select
>> "Failed". You should now see a list of failed tasks in your plan. Look for a
>> plan with an activity of "Backup Transaction Log" and double click it. The
>> reason for failure will appear. My guess is that the database is in simple
>> recovery mode and you will get an error similar to "Backup can not be
>> performed on this database. This sub task is ignored".
>> All you have to do in this instance is remove databases that have simple
>> recovery from your maintenance plan, or change them to a different recovery
>> model (if that's what you need).
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602m.html
>>
>>
>> "msde@.csite.com" wrote:
>> Hi
>> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
>> databases. I created an maintenance plan that does an integrity check,
>> backups all databases, and backup all transactionlogs.
>> If I look at the "Jobs" list it looks like backing up the databases runs OK,
>> but both the integrity check and transaction log seams to fail.
>> If I look at the folder holding the backupfiles all transactionfiles looks
>> to be there (backup of all the transaction logs). So what makes the job
>> fail? If I look at the event log all transaction-log-backups complete OK,
>> but still the job ends in failure and the eventlog only states that the job
>> faild - not why it failed.
>> How can I track this problem? And is this why the transaction logs are not
>> truncated?
>> Regards
>> Jan Agermose
>>
>>
>sql

maintenance fails

Hi
Im running a Windows 2003 server and MSSQL 2000 that manages some 45
databases. I created an maintenance plan that does an integrity check,
backups all databases, and backup all transactionlogs.
If I look at the "Jobs" list it looks like backing up the databases runs OK,
but both the integrity check and transaction log seams to fail.
If I look at the folder holding the backupfiles all transactionfiles looks
to be there (backup of all the transaction logs). So what makes the job
fail? If I look at the event log all transaction-log-backups complete OK,
but still the job ends in failure and the eventlog only states that the job
faild - not why it failed.
How can I track this problem? And is this why the transaction logs are not
truncated?
Regards
Jan AgermoseHi
Within a job specify an output file to see what is going on.
<msde@.csite.com> wrote in message
news:epoOEnVUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs
OK,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the
job
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>|||Hello Jan,
Go to your maintenance plan in enterprise manager and right click on it.
Select "Maintenance Plan History...". In the Status dropdown, select
"Failed". You should now see a list of failed tasks in your plan. Look for a
plan with an activity of "Backup Transaction Log" and double click it. The
reason for failure will appear. My guess is that the database is in simple
recovery mode and you will get an error similar to "Backup can not be
performed on this database. This sub task is ignored".
All you have to do in this instance is remove databases that have simple
recovery from your maintenance plan, or change them to a different recovery
model (if that's what you need).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"msde@.csite.com" wrote:

> Hi
> Im running a Windows 2003 server and MSSQL 2000 that manages some 45
> databases. I created an maintenance plan that does an integrity check,
> backups all databases, and backup all transactionlogs.
> If I look at the "Jobs" list it looks like backing up the databases runs O
K,
> but both the integrity check and transaction log seams to fail.
> If I look at the folder holding the backupfiles all transactionfiles looks
> to be there (backup of all the transaction logs). So what makes the job
> fail? If I look at the event log all transaction-log-backups complete OK,
> but still the job ends in failure and the eventlog only states that the jo
b
> faild - not why it failed.
> How can I track this problem? And is this why the transaction logs are not
> truncated?
> Regards
> Jan Agermose
>
>|||Ahhh :-) Of cause - I selsected to backup all databases - this includes
"master", "tempdb" and the test database "northwind". Doing a backup of the
transaction log fails.
I guess that means I need two plans
One for all user databases but not the system databases - this plan does a
databasebackup and a transactionlog backup.
And one for the system databases that does not try to backup the
transactionlog?
Regards
Jan
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...[vbcol=seagreen]
> Hello Jan,
> Go to your maintenance plan in enterprise manager and right click on it.
> Select "Maintenance Plan History...". In the Status dropdown, select
> "Failed". You should now see a list of failed tasks in your plan. Look for
> a
> plan with an activity of "Backup Transaction Log" and double click it. The
> reason for failure will appear. My guess is that the database is in simple
> recovery mode and you will get an error similar to "Backup can not be
> performed on this database. This sub task is ignored".
> All you have to do in this instance is remove databases that have simple
> recovery from your maintenance plan, or change them to a different
> recovery
> model (if that's what you need).
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
>
> "msde@.csite.com" wrote:
>|||You got the point, Jan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<msde@.csite.com> wrote in message news:O8YUBjbUFHA.3280@.TK2MSFTNGP09.phx.gbl...en">
> Ahhh :-) Of cause - I selsected to backup all databases - this includes "m
aster", "tempdb" and the
> test database "northwind". Doing a backup of the transaction log fails.
> I guess that means I need two plans
> One for all user databases but not the system databases - this plan does a
databasebackup and a
> transactionlog backup.
> And one for the system databases that does not try to backup the transacti
onlog?
> Regards
> Jan
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:7D5CF0AB-9C9C-4675-8CCA-BFFA2E3092FB@.microsoft.com...
>

Maintenance - Integrity Check failure

Found log for job:
'Error: 4062, Cannot open user default database '<ID>'.
Using master db instead.'
Will check Microsoft Error list.

>--Original Message--
>I've got a database in SQL Server 7/Windows 2000 that is
>maintained through 'Database Maintenance Plans'.
>Integrity of the database is checked once a week
>using 'Integrity Check'. The weekly 'Integrity Check'
is
>failing. The error message given is 'The job has failed
>in step one, please review step 1' and nothing is put
>into the application event log. The transaction sql
>command for checking the integrity check of a database
is
>DBCC Checkdb('database'). The command line integrity
>check works fine and returns no errors.
>Failures are also occuring for 'Update Statistics used
by
>query optimizer' and 'Reorganize data and index pages'
in
>Maintenance.
>What is causing these maintenance plans to fail?
>.
>If you get this when SQL Agent logs in to run a job then
most likely the default database for the SQL Agent service
account is assigned to a database that no longer exists on
the server. You may want to check the default database for
that account. You'd probably want it set to master.
-Sue
On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
<anonymous@.discussions.microsoft.com> wrote:
>Found log for job:
>'Error: 4062, Cannot open user default database '<ID>'.
>Using master db instead.'
>Will check Microsoft Error list.
>
>is
>is
>by
>in|||You should also not automatically repair errors. You shoudl always do
root-cause analysis as these errors are usually an indication that you have
a hardware problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:73ma70t1m49tgqnqmk0ccuiopd3qd4n4rj@.
4ax.com...
> If you get this when SQL Agent logs in to run a job then
> most likely the default database for the SQL Agent service
> account is assigned to a database that no longer exists on
> the server. You may want to check the default database for
> that account. You'd probably want it set to master.
> -Sue
> On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
> <anonymous@.discussions.microsoft.com> wrote:
>
>

Maintenance - Integrity Check failure

Found log for job:
'Error: 4062, Cannot open user default database '<ID>'.
Using master db instead.'
Will check Microsoft Error list.

>--Original Message--
>I've got a database in SQL Server 7/Windows 2000 that is
>maintained through 'Database Maintenance Plans'.
>Integrity of the database is checked once a week
>using 'Integrity Check'. The weekly 'Integrity Check'
is
>failing. The error message given is 'The job has failed
>in step one, please review step 1' and nothing is put
>into the application event log. The transaction sql
>command for checking the integrity check of a database
is
>DBCC Checkdb('database'). The command line integrity
>check works fine and returns no errors.
>Failures are also occuring for 'Update Statistics used
by
>query optimizer' and 'Reorganize data and index pages'
in
>Maintenance.
>What is causing these maintenance plans to fail?
>.
>
If you get this when SQL Agent logs in to run a job then
most likely the default database for the SQL Agent service
account is assigned to a database that no longer exists on
the server. You may want to check the default database for
that account. You'd probably want it set to master.
-Sue
On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
<anonymous@.discussions.microsoft.com> wrote:
>Found log for job:
>'Error: 4062, Cannot open user default database '<ID>'.
>Using master db instead.'
>Will check Microsoft Error list.
>
>is
>is
>by
>in
|||You should also not automatically repair errors. You shoudl always do
root-cause analysis as these errors are usually an indication that you have
a hardware problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:73ma70t1m49tgqnqmk0ccuiopd3qd4n4rj@.4ax.com...
> If you get this when SQL Agent logs in to run a job then
> most likely the default database for the SQL Agent service
> account is assigned to a database that no longer exists on
> the server. You may want to check the default database for
> that account. You'd probably want it set to master.
> -Sue
> On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
> <anonymous@.discussions.microsoft.com> wrote:
>

Maintenance - Integrity Check failure

I've got a database in SQL Server 7/Windows 2000 that is
maintained through 'Database Maintenance Plans'.
Integrity of the database is checked once a week
using 'Integrity Check'. The weekly 'Integrity Check' is
failing. The error message given is 'The job has failed
in step one, please review step 1' and nothing is put
into the application event log. The transaction sql
command for checking the integrity check of a database is
DBCC Checkdb('database'). The command line integrity
check works fine and returns no errors.
Failures are also occuring for 'Update Statistics used by
query optimizer' and 'Reorganize data and index pages' in
Maintenance.
What is causing these maintenance plans to fail?Found log for job:
'Error: 4062, Cannot open user default database '<ID>'.
Using master db instead.'
Will check Microsoft Error list.
>--Original Message--
>I've got a database in SQL Server 7/Windows 2000 that is
>maintained through 'Database Maintenance Plans'.
>Integrity of the database is checked once a week
>using 'Integrity Check'. The weekly 'Integrity Check'
is
>failing. The error message given is 'The job has failed
>in step one, please review step 1' and nothing is put
>into the application event log. The transaction sql
>command for checking the integrity check of a database
is
>DBCC Checkdb('database'). The command line integrity
>check works fine and returns no errors.
>Failures are also occuring for 'Update Statistics used
by
>query optimizer' and 'Reorganize data and index pages'
in
>Maintenance.
>What is causing these maintenance plans to fail?
>.
>|||Hi Jim,
The only thing I can think of is that its found a minor
error and attempted to fix it. Unfortunaly it hasn't
worked as there are still users attached to the db (it
needs to be in single user mode to do it), try a dbcc on
your DB.
J
>--Original Message--
>I've got a database in SQL Server 7/Windows 2000 that is
>maintained through 'Database Maintenance Plans'.
>Integrity of the database is checked once a week
>using 'Integrity Check'. The weekly 'Integrity Check' is
>failing. The error message given is 'The job has failed
>in step one, please review step 1' and nothing is put
>into the application event log. The transaction sql
>command for checking the integrity check of a database is
>DBCC Checkdb('database'). The command line integrity
>check works fine and returns no errors.
>Failures are also occuring for 'Update Statistics used by
>query optimizer' and 'Reorganize data and index pages' in
>Maintenance.
>What is causing these maintenance plans to fail?
>.
>|||If you get this when SQL Agent logs in to run a job then
most likely the default database for the SQL Agent service
account is assigned to a database that no longer exists on
the server. You may want to check the default database for
that account. You'd probably want it set to master.
-Sue
On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
<anonymous@.discussions.microsoft.com> wrote:
>Found log for job:
>'Error: 4062, Cannot open user default database '<ID>'.
>Using master db instead.'
>Will check Microsoft Error list.
>
>>--Original Message--
>>I've got a database in SQL Server 7/Windows 2000 that is
>>maintained through 'Database Maintenance Plans'.
>>Integrity of the database is checked once a week
>>using 'Integrity Check'. The weekly 'Integrity Check'
>is
>>failing. The error message given is 'The job has failed
>>in step one, please review step 1' and nothing is put
>>into the application event log. The transaction sql
>>command for checking the integrity check of a database
>is
>>DBCC Checkdb('database'). The command line integrity
>>check works fine and returns no errors.
>>Failures are also occuring for 'Update Statistics used
>by
>>query optimizer' and 'Reorganize data and index pages'
>in
>>Maintenance.
>>What is causing these maintenance plans to fail?
>>.|||You should also not automatically repair errors. You shoudl always do
root-cause analysis as these errors are usually an indication that you have
a hardware problem.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:73ma70t1m49tgqnqmk0ccuiopd3qd4n4rj@.4ax.com...
> If you get this when SQL Agent logs in to run a job then
> most likely the default database for the SQL Agent service
> account is assigned to a database that no longer exists on
> the server. You may want to check the default database for
> that account. You'd probably want it set to master.
> -Sue
> On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
> <anonymous@.discussions.microsoft.com> wrote:
> >Found log for job:
> >
> >'Error: 4062, Cannot open user default database '<ID>'.
> >Using master db instead.'
> >
> >Will check Microsoft Error list.
> >
> >
> >>--Original Message--
> >>I've got a database in SQL Server 7/Windows 2000 that is
> >>maintained through 'Database Maintenance Plans'.
> >>
> >>Integrity of the database is checked once a week
> >>using 'Integrity Check'. The weekly 'Integrity Check'
> >is
> >>failing. The error message given is 'The job has failed
> >>in step one, please review step 1' and nothing is put
> >>into the application event log. The transaction sql
> >>command for checking the integrity check of a database
> >is
> >>DBCC Checkdb('database'). The command line integrity
> >>check works fine and returns no errors.
> >>
> >>Failures are also occuring for 'Update Statistics used
> >by
> >>query optimizer' and 'Reorganize data and index pages'
> >in
> >>Maintenance.
> >>
> >>What is causing these maintenance plans to fail?
> >>.
> >>
>

Maint plan: integerity check & backup

In the maintenance plans Integrity tab there is a check box for: "Preform
these tests before backing up the database, or transaction logs".
I want to be sure I understand this. If I check this box, does that mean an
integrity check (and potentially a repair) will run ANYTIME a database, OR
transaction log backup is run, as part of the backup procedure and that one
would not need to set a schedule for the integrity checks?
Given the posibility of the function locking the database (to do a repair),
I don't know if this is a good idea, or not.
JayKon wrote:
> In the maintenance plans Integrity tab there is a check box for: "Preform
> these tests before backing up the database, or transaction logs".
> I want to be sure I understand this. If I check this box, does that mean an
> integrity check (and potentially a repair) will run ANYTIME a database, OR
> transaction log backup is run, as part of the backup procedure and that one
> would not need to set a schedule for the integrity checks?
> Given the posibility of the function locking the database (to do a repair),
> I don't know if this is a good idea, or not.
Checking that box means that the maintenance plan will perform an
integrity check before running a backup. Running a backup manually,
from QA for instance, will NOT invoke an integrity check. I would
suggest NOT using this option.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||What I thought, thanks guys.
"Hari Prasad" wrote:
[vbcol=seagreen]
> Checking that option will verify the allocation and structural integrity of
> user and system tables, and indexes in the database.
> This will incur lots of resource usage and will lock resouces in SQL 2000.
> So I recommend you to schedule a DBCC CHECKDB during offpeak time.
> Thanks
> Hari
> "Tracy McKibben" wrote:

Maint plan: integerity check & backup

In the maintenance plans Integrity tab there is a check box for: "Preform
these tests before backing up the database, or transaction logs".
I want to be sure I understand this. If I check this box, does that mean an
integrity check (and potentially a repair) will run ANYTIME a database, OR
transaction log backup is run, as part of the backup procedure and that one
would not need to set a schedule for the integrity checks?
Given the posibility of the function locking the database (to do a repair),
I don't know if this is a good idea, or not.JayKon wrote:
> In the maintenance plans Integrity tab there is a check box for: "Preform
> these tests before backing up the database, or transaction logs".
> I want to be sure I understand this. If I check this box, does that mean a
n
> integrity check (and potentially a repair) will run ANYTIME a database, OR
> transaction log backup is run, as part of the backup procedure and that on
e
> would not need to set a schedule for the integrity checks?
> Given the posibility of the function locking the database (to do a repair)
,
> I don't know if this is a good idea, or not.
Checking that box means that the maintenance plan will perform an
integrity check before running a backup. Running a backup manually,
from QA for instance, will NOT invoke an integrity check. I would
suggest NOT using this option.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Checking that option will verify the allocation and structural integrity of
user and system tables, and indexes in the database.
This will incur lots of resource usage and will lock resouces in SQL 2000.
So I recommend you to schedule a DBCC CHECKDB during offpeak time.
Thanks
Hari
"Tracy McKibben" wrote:

> JayKon wrote:
> Checking that box means that the maintenance plan will perform an
> integrity check before running a backup. Running a backup manually,
> from QA for instance, will NOT invoke an integrity check. I would
> suggest NOT using this option.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||What I thought, thanks guys.
"Hari Prasad" wrote:
[vbcol=seagreen]
> Checking that option will verify the allocation and structural integrity o
f
> user and system tables, and indexes in the database.
> This will incur lots of resource usage and will lock resouces in SQL 2000.
> So I recommend you to schedule a DBCC CHECKDB during offpeak time.
> Thanks
> Hari
> "Tracy McKibben" wrote:
>