index and key: version

Showing posts with label version. Show all posts

Monday, March 26, 2012

Index Rebuild fails over SET options

Wednesday, March 7, 2012

Index Fails over SET options

This came up before and it did not get fixed correctly on my end. Anyone?
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'BIGSERVER' as OURCORP\BIGSERVER (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
[1] Database DB1: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'tAmexDemands'
Rebuilding indexes for table 'tAmexDemandsHistory'
Rebuilding indexes for table 'tAthensWarning'
Rebuilding indexes for table 'tATPOLines'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
Regards,
Jamie
Hi
For indexes on Computed columns or views you need this setting to be ON,
DBCC USEROPTIONS will show you what the current settings are and you can use
the SET command to set them. If you want to change the system wide use
sp_configure to change the 'user options' value or sp_dboption to set the
specific values at database level.
John
"thejamie" wrote:

> This came up before and it did not get fixed correctly on my end. Anyone?
> --
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'tAmexDemands'
> Rebuilding indexes for table 'tAmexDemandsHistory'
> Rebuilding indexes for table 'tAthensWarning'
> Rebuilding indexes for table 'tATPOLines'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> --
> Regards,
> Jamie
|||The database is inherited and no one seems to know where the computed columns
exist. It is a rather large database (roughly 40 gig on the primary and 60
on the history filegroup). I'm running into problems searching for these
conditions. If I simply change options on the database, it will likely
present problems. I used a routine on the MSDN to rebuild indexes, but
there is no time to run it. TDay is filled up (11/23) with catch up
processes. Rebuilding indexes on this particular database is not an option
although some get rebuilt at night but not all. Also not all four processors
are used, only one. Apparently there is concern the production server is
about to crash. A backup plan is being implemented in this event, but there
are bottlenecks due to the physical size of the disks. The processor spikes
into the 90 % area and averages in the 75% during peak production time.
While the machine manages during the day, it is failing to do the work at
night. Not sure what other options may be available.
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> For indexes on Computed columns or views you need this setting to be ON,
> DBCC USEROPTIONS will show you what the current settings are and you can use
> the SET command to set them. If you want to change the system wide use
> sp_configure to change the 'user options' value or sp_dboption to set the
> specific values at database level.
> John
>
> "thejamie" wrote:

Index Fails over SET options

This came up before and it did not get fixed correctly on my end. Anyone?
--
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'BIGSERVER' as OURCORP\BIGSERVER (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
[1] Database DB1: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'tAmexDemands'
Rebuilding indexes for table 'tAmexDemandsHistory'
Rebuilding indexes for table 'tAthensWarning'
Rebuilding indexes for table 'tATPOLines'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
--
Regards,
JamieHi
For indexes on Computed columns or views you need this setting to be ON,
DBCC USEROPTIONS will show you what the current settings are and you can use
the SET command to set them. If you want to change the system wide use
sp_configure to change the 'user options' value or sp_dboption to set the
specific values at database level.
John
"thejamie" wrote:

> This came up before and it did not get fixed correctly on my end. Anyone?
> --
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'tAmexDemands'
> Rebuilding indexes for table 'tAmexDemandsHistory'
> Rebuilding indexes for table 'tAthensWarning'
> Rebuilding indexes for table 'tATPOLines'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET option
s have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> --
> Regards,
> Jamie|||The database is inherited and no one seems to know where the computed column
s
exist. It is a rather large database (roughly 40 gig on the primary and 60
on the history filegroup). I'm running into problems searching for these
conditions. If I simply change options on the database, it will likely
present problems. I used a routine on the MSDN to rebuild indexes, but
there is no time to run it. TDay is filled up (11/23) with catch up
processes. Rebuilding indexes on this particular database is not an option
although some get rebuilt at night but not all. Also not all four processor
s
are used, only one. Apparently there is concern the production server is
about to crash. A backup plan is being implemented in this event, but there
are bottlenecks due to the physical size of the disks. The processor spike
s
into the 90 % area and averages in the 75% during peak production time.
While the machine manages during the day, it is failing to do the work at
night. Not sure what other options may be available.
--
Regards,
Jamie
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> For indexes on Computed columns or views you need this setting to be ON,
> DBCC USEROPTIONS will show you what the current settings are and you can u
se
> the SET command to set them. If you want to change the system wide use
> sp_configure to change the 'user options' value or sp_dboption to set the
> specific values at database level.
> John
>
> "thejamie" wrote:
>

Index Fails over SET options

This came up before and it did not get fixed correctly on my end. Anyone?
--
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'BIGSERVER' as OURCORP\BIGSERVER (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
[1] Database DB1: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'tAmexDemands'
Rebuilding indexes for table 'tAmexDemandsHistory'
Rebuilding indexes for table 'tAthensWarning'
Rebuilding indexes for table 'tATPOLines'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
--
Regards,
JamieHi
For indexes on Computed columns or views you need this setting to be ON,
DBCC USEROPTIONS will show you what the current settings are and you can use
the SET command to set them. If you want to change the system wide use
sp_configure to change the 'user options' value or sp_dboption to set the
specific values at database level.
John
"thejamie" wrote:
> This came up before and it did not get fixed correctly on my end. Anyone?
> --
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'tAmexDemands'
> Rebuilding indexes for table 'tAmexDemandsHistory'
> Rebuilding indexes for table 'tAthensWarning'
> Rebuilding indexes for table 'tATPOLines'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> Server Driver][SQL Server]DBCC failed because the following SET options have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> --
> Regards,
> Jamie|||The database is inherited and no one seems to know where the computed columns
exist. It is a rather large database (roughly 40 gig on the primary and 60
on the history filegroup). I'm running into problems searching for these
conditions. If I simply change options on the database, it will likely
present problems. I used a routine on the MSDN to rebuild indexes, but
there is no time to run it. TDay is filled up (11/23) with catch up
processes. Rebuilding indexes on this particular database is not an option
although some get rebuilt at night but not all. Also not all four processors
are used, only one. Apparently there is concern the production server is
about to crash. A backup plan is being implemented in this event, but there
are bottlenecks due to the physical size of the disks. The processor spikes
into the 90 % area and averages in the 75% during peak production time.
While the machine manages during the day, it is failing to do the work at
night. Not sure what other options may be available.
--
Regards,
Jamie
"John Bell" wrote:
> Hi
> For indexes on Computed columns or views you need this setting to be ON,
> DBCC USEROPTIONS will show you what the current settings are and you can use
> the SET command to set them. If you want to change the system wide use
> sp_configure to change the 'user options' value or sp_dboption to set the
> specific values at database level.
> John
>
> "thejamie" wrote:
> > This came up before and it did not get fixed correctly on my end. Anyone?
> > --
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'BIGSERVER' as OURCORP\BIGSERVER (trusted)
> > Starting maintenance plan 'DB Maintenance Plan1' on 11/22/2006 00:45:03
> > [1] Database DB1: Index Rebuild (leaving 10%% free space)...
> >
> > Rebuilding indexes for table 'tAmexDemands'
> > Rebuilding indexes for table 'tAmexDemandsHistory'
> > Rebuilding indexes for table 'tAthensWarning'
> > Rebuilding indexes for table 'tATPOLines'
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]DBCC failed because the following SET options have
> > incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> > --
> > Regards,
> > Jamie

Friday, February 24, 2012

index corruption ?

We have a problem with a result set returned from a query:
select number, version, division, account
from probill
where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
and rebill = 1
order by number
Primary key on number, version ; non-clustered index on currentstatus,
non-clustered index on rebill.
Each row of the result set returned is duplicated, 306 rows.
If I reindex the table, the problem goes away temporarily. If I
execute : sp_configure 'max degree of parallelism', '1' and
reconfigure, I get the correct result set, 153 rows. Setting the
maxdop to '0' again and I'm back to 306 rows.
Any idea(s) what could cause this type of problem? Hardware? Corrupt
indexes? SQL server bug? All the hardware diagnostics check out fine.
Sounds like a bug with the parallel plan to me. Are you current on service pack? Have you checked
against KB?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nospam15151@.mailinator.com> wrote in message news:chptbj$a9c@.odak26.prod.google.com...
> We have a problem with a result set returned from a query:
> select number, version, division, account
> from probill
> where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
> and rebill = 1
> order by number
> Primary key on number, version ; non-clustered index on currentstatus,
> non-clustered index on rebill.
> Each row of the result set returned is duplicated, 306 rows.
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
>
|||<nospam15151@.mailinator.com> wrote in message
news:chptbj$a9c@.odak26.prod.google.com...
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
Sounds like a MAXDOP bug, since that fixes it... Are you running the
lastest Service Pack (3a)?
If so, you might want to check http://support.microsoft.com and see if
there's a hotfix available for what you're experiencing.
|||Hi,
We are running sp3a. I wasn't able to find anything specific in the
knowledge base. Q 330212 looked close though. That's what led me to
try changing the maxdop setting.

index corruption ?

We have a problem with a result set returned from a query:
select number, version, division, account
from probill
where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
and rebill = 1
order by number
Primary key on number, version ; non-clustered index on currentstatus,
non-clustered index on rebill.
Each row of the result set returned is duplicated, 306 rows.
If I reindex the table, the problem goes away temporarily. If I
execute : sp_configure 'max degree of parallelism', '1' and
reconfigure, I get the correct result set, 153 rows. Setting the
maxdop to '0' again and I'm back to 306 rows.
Any idea(s) what could cause this type of problem? Hardware? Corrupt
indexes? SQL server bug? All the hardware diagnostics check out fine.Sounds like a bug with the parallel plan to me. Are you current on service pack? Have you checked
against KB?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<nospam15151@.mailinator.com> wrote in message news:chptbj$a9c@.odak26.prod.google.com...
> We have a problem with a result set returned from a query:
> select number, version, division, account
> from probill
> where currentstatus in ( '0', 'B', 'D', 'J', 'R', 'A' )
> and rebill = 1
> order by number
> Primary key on number, version ; non-clustered index on currentstatus,
> non-clustered index on rebill.
> Each row of the result set returned is duplicated, 306 rows.
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
>|||<nospam15151@.mailinator.com> wrote in message
news:chptbj$a9c@.odak26.prod.google.com...
> If I reindex the table, the problem goes away temporarily. If I
> execute : sp_configure 'max degree of parallelism', '1' and
> reconfigure, I get the correct result set, 153 rows. Setting the
> maxdop to '0' again and I'm back to 306 rows.
> Any idea(s) what could cause this type of problem? Hardware? Corrupt
> indexes? SQL server bug? All the hardware diagnostics check out fine.
Sounds like a MAXDOP bug, since that fixes it... Are you running the
lastest Service Pack (3a)?
If so, you might want to check http://support.microsoft.com and see if
there's a hotfix available for what you're experiencing.

Monday, March 26, 2012

Index Rebuild fails over SET options

Index Rebuild fails over SET options

Index Rebuild fails over SET options

Wednesday, March 7, 2012

Index Fails over SET options

Index Fails over SET options

Index Fails over SET options

Friday, February 24, 2012

index corruption ?

index corruption ?

index and key

Blog Archive

About Me