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 optimizations. Show all posts
Showing posts with label optimizations. 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.
Monday, March 26, 2012
Maintenance Plan - Optimizations
I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully.
Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backup
s
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/Real...fraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully.
Seriously, you might consider doing full backups right aftera reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backup
s
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/Real...fraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Maintenance Plan - Optimizations
I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?
Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backups
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?
Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully.
Seriously, you might consider doing full backups right aftera reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backups
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Maintenance Plan - Optimizations
I ran a Optimization plan on Sunday morning and my database grew from 14GB
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. :) Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backups
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
to 19GB. No data was added, but my index grew considerably with much free
space.
I have Reorganize data and index pages with "change free space per page
percentage to: 10%.
My fill factor on my clustered indexs is set to 90%.
Name Rows Reserved Data index_size
Unused
RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
The only thing this killed me on was my backups and drive space. With my
backup schedule, it completely wiped out my drive with Differential backups
and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
Questions are:
1. How should we plan for Index growth?
2. How should we schedule backups surrounding Index re-orgs?
3. How does the re-org affect full and differential backups?
4. Anything else I should be aware of?Comments Inline
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:%237FkM0lPHHA.1276@.TK2MSFTNGP04.phx.gbl...
>I ran a Optimization plan on Sunday morning and my database grew from 14GB
>to 19GB. No data was added, but my index grew considerably with much free
>space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
These represent the same value, expressed in different terms.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential
> backups and Transaction Backups. (I didn't expect the logfile to grow to
> 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
Disk space is cheap. At least compared to other resources used by SQL. Set
the DB size appropriately and check it once a month or so. Auto-grow should
be left as an emergency safety valve only.
> 2. How should we schedule backups surrounding Index re-orgs?
Carefully. :) Seriously, you might consider doing full backups right after
a reindex to save recovery time.
> 3. How does the re-org affect full and differential backups?
No effect on full, huge effect on differential. The correct quesiton is
"How does reindexing affect my ability to restore my systems?" Once you
answer that question, the backup schedule resolves itself.
> 4. Anything else I should be aware of?
Don't store backups on the local file system. Disk space is even cheaper on
another server.
Never, Ever, Ever check the "Attempt to Repair Minor Problems" box in the
maintenance plan wizard. It should be labeled "hide minor disk faults until
they become critical and eat my data."
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP|||Kevin A wrote:
> I ran a Optimization plan on Sunday morning and my database grew from 14GB
> to 19GB. No data was added, but my index grew considerably with much free
> space.
> I have Reorganize data and index pages with "change free space per page
> percentage to: 10%.
> My fill factor on my clustered indexs is set to 90%.
> Name Rows Reserved Data index_size
> Unused
> RawData 152823906 33001168 KB 5776200 KB 15393952 KB 11831016 KB
> The only thing this killed me on was my backups and drive space. With my
> backup schedule, it completely wiped out my drive with Differential backups
> and Transaction Backups. (I didn't expect the logfile to grow to 24GB!)
> Questions are:
> 1. How should we plan for Index growth?
> 2. How should we schedule backups surrounding Index re-orgs?
> 3. How does the re-org affect full and differential backups?
> 4. Anything else I should be aware of?
>
Couple of suggestions:
1. Dump the maintenance plan, and use a "smarter" reindexing strategy.
The maintenance wizard rebuilds all indexes, which is usually
unnecessary. You rebuild indexes to remove fragmentation, so why not
rebuild only those that are actually fragmented? Here's a script that I
use:
http://realsqlguy.com/bin/view/RealSQLGuy/DefraggingIndexes
2. Reindexing is a logged operation. If the log file size is a
problem, then consider putting the database into Simple recovery mode
during the reindex. This WILL break any log shipping processes that you
have in place, so you'll need to plan for that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)