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.
Showing posts with label checks. Show all posts
Showing posts with label checks. Show all posts
Friday, March 30, 2012
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.
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.
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.
Wednesday, March 28, 2012
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
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.
>
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.
>
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.
>
Monday, March 12, 2012
Maint Plan via wizard, doesnt show bad db when it actually is..
Win2003Stdx64sp2,sql2005x64sp2
I setup maintenance job to do database checks via the wizard to do all user db's (and tired specific too). I downloaded a corrupt database (Broke) from msdn blog area. Ran the job, but it did not find the error as I would have hoped. Ran check in QA and got the error as expected…Am I missing something? I was hoping it would be similar to 2000 and state a failure if there was and error found.
Job report:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042 Report was generated on " XXXXXXXX ".
Maintenance Plan: User_MaintPlan_CheckDB
Duration: 00:00:12
Status: Succeeded.
Details:
Check Database Integrity (XXXXXXXX)
Check Database integrity on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Include indexes
Task start: 2007-05-08T11:14:53.
Task end: 2007-05-08T11:15:03.
Success
Command:USE [ReportServer]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [ReportServerTempDB]
GO
DBCC CHECKDB WITH NO_INFOMSGS
GO
USE [AdventureWorksDW]
GO
USE [Broke]
GO
DBCC CHECKDB WITH NO_INFOMSGS
======================================
Then I ran this in QA:
USE [Broke]
GO
DBCC CHECKDB WITH NO_INFOMSGS
QA Results:
Msg 8928, Level 16, State 1, Line 1
Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data): Page (1:143) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data), page (1:143). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'brokentable' (object ID 2073058421).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Broke'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Broke).
Itappears to be an issue with sp2 and doing muplitle db. I was at sp2 (3042) I applied GDR2 now at 3054. It is now showing the error in the maint job but it appears to be stopping after it finds the error and not finishing.
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3054 Report was generated on "XXXXXXXXXXXXX".
Maintenance Plan: User_MaintPlan_CheckDB
Duration: 00:01:08
Status: Warning: One or more tasks failed..
Details:
Check Database Integrity (XXXXXXXXXXX)
Check Database integrity on Target server connection Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Include indexes
Task start: 2007-05-08T18:20:05.
Task end: 2007-05-08T18:21:11.
Failed
Table error: Object ID 2073058421, index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data), page (1:143). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'brokentable' (object ID 2073058421).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Broke'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Broke).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
|||I doubt due to the backward compatibility issues running it from sql 2005 towards sql 70, you might need to depend upon SQL Enterprise manager to tackle such maintenance plans on ver.70 databases.
Subscribe to:
Posts (Atom)