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 trouble. Show all posts
Showing posts with label trouble. 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 bug on SQL Server 2005
Hello, everyone:
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
Did someone meet this bug and have suggestions?
Thanks
ZYTPermissions? Do you have the same permissions to the server as you do to your local SQL Server?|||Hi, Pootle:
Thanks for reply.
I have sysadmin permissions to the server.
ZYT
Permissions? Do you have the same permissions to the server as you do to your local SQL Server?|||Hi, Pootle:
The server is the remote one that our company just got from someone else. I login by "Remote Desktop Connection" and check SQL Server. From Start - Programs - SQL Server 2005, there is only "Configuration Tools" available, no other componences like "SQL Server Management Stadio". This is first time I sam and confused. What was happened?
When I connect server from SQL Server, I can all tables and stored procedrues.
Thanks
ZYT
Permissions? Do you have the same permissions to the server as you do to your local SQL Server?|||As I remember, there was a bug in SQL 2005 SP2 that affected Maintenance plans. Check to see what hotfixes are available.
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
Did someone meet this bug and have suggestions?
Thanks
ZYTPermissions? Do you have the same permissions to the server as you do to your local SQL Server?|||Hi, Pootle:
Thanks for reply.
I have sysadmin permissions to the server.
ZYT
Permissions? Do you have the same permissions to the server as you do to your local SQL Server?|||Hi, Pootle:
The server is the remote one that our company just got from someone else. I login by "Remote Desktop Connection" and check SQL Server. From Start - Programs - SQL Server 2005, there is only "Configuration Tools" available, no other componences like "SQL Server Management Stadio". This is first time I sam and confused. What was happened?
When I connect server from SQL Server, I can all tables and stored procedrues.
Thanks
ZYT
Permissions? Do you have the same permissions to the server as you do to your local SQL Server?|||As I remember, there was a bug in SQL 2005 SP2 that affected Maintenance plans. Check to see what hotfixes are available.
Wednesday, March 21, 2012
Maintanence Plans
I am having trouble with my database maintanence plan.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.sql
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.sql
Maintanence Plans
I am having trouble with my database maintanence plan.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.
Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.
Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
Maintanence Plans
I am having trouble with my database maintanence plan.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
It will work ok for a while, then it just stops working, but even when it is
working it won't delete the old .BAK file's like it is supposed to.Dan,
Check if you plan is running log backups of databases which have their
recovery model set to simple. In these cases the database maintenance plan
runs ok until the invalid log backup occurs, and the plan terminates with a
somewhat misleading error, without deleting the old backup files.
Jon Jahren
"Dan Thannhauser" <DanThannhauser@.discussions.microsoft.com> wrote in
message news:10178E9F-0E25-413A-AFC4-6C38A662C8D2@.microsoft.com...
> I am having trouble with my database maintanence plan.
> It will work ok for a while, then it just stops working, but even when it
is
> working it won't delete the old .BAK file's like it is supposed to.
Subscribe to:
Posts (Atom)