deAttach database: dbreindex

Showing posts with label dbreindex. Show all posts

Saturday, February 25, 2012

DBREINDEX/INDEXDEFRAG a few questions

I pretty much understand the differences between DBCC DBREINDEX and DBCC INDEXDEFRAG. However, I need the forums help to understand a few specific issues relating to clustered/non-clustered indexes and the advantages/disadvantages of running the DBCC DBREINDEX/INDEXDEFRAG against the table or against each specific index...

"If a table has a clustered index, it's only necessary to re-index the clustered index because any non-clustered indexes on that table will be automatically re-indexed as well."

I think the above statement is true for DBCC DBREINDEX but is the following statement true for DBCC INDEXDEFRAG:-

"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

Following on from the above, is there any advantage with an index maintenance strategy to individually running DBCC DBREINDEX against each specific index as opposed to running it against the table and letting SQL sort out the underlying indexes? Does the same apply to DBCC INDEXDEFRAG?

Regards,

Clive"If a table has a clustered index, it's only necessary to Index Defrag the clustered index because any non-clustered indexes on that table will be automatically defragged as well."

I just did a test of this, and the answer is no, the non-clustered indexes will not be defragged as well. This works out as a slight benefit to indexdefrag, as it saves you the time of rebuilding the non-clustered indexes at the same time as the clustered index.

If you have all non-clustered indexes, then there would be a slight benefit in space-savings by running dbcc dbreindex on each index separately. If there is a clustered index, then running dbreindex on the clustered index rebuilds all of the indexes. This would be a waste of any time spent on each index being rebuilt individually. Does this help?|||Yes, that helps. Thank you. In fact, I just put together a test myself and found the same result. Not unsurprisingly, the DBREINDEX produced 100% scan density on the clustered index and the same or close to it on the non-clustered indexes. However, I was surprised that INDEXDEFRAG on the clustered index actually lowered scan-density by a few percent!

Regards,

Clive|||What was the scan density when you started?

The main problem with indexdefrag is that it is only moving pages from and to page locations that are already allocated to the index being defragmented. It may coalesce some unused space among these allocations, but it does not touch anything that is already allocated to another index page or data page of the table. Since it has to work around all of these prior allocations, you almost never get a nice 100% result from indexdefrag, unless all you had to start with was the clustered index.

dbreindex vs index defrag question

Does anyone know if dbreindex and index defrag ideally perform the same function? I have been told that index defrag does not hold locks on a table when executed and dbreindex does. Other than this is there any difference between the two functions? My understanding was that dbreindex reindexes the data stored in a table for faster reads and index defrag removes purged data. Am I correct? I am currently running both functions on my SQL server and was advised that I really only need to run the index defrag job. Is this advise correct?http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm

You can reduce fragmentation and improve read-ahead performance by using one of the following:

Dropping and re-creating an index
=================================
Best performance, but places an exclusive table lock on the table, preventing any table access by users and shared table lock on the table, preventing all
but SELECT operations to be performed on it.

OR

Rebuilding an index by using the DBCC DBREINDEX statement
================================================== =======
Faster than dropping and re-creating, but during rebuilding a clustered index, an exclusive table lock is put on the table, preventing any table access by
users. And during rebuilding a nonclustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it

OR

Defragmenting an index by using the DBCC INDEXDEFRAG statement
================================================== ============
It does not hold locks (or only for very shot time) [i.e. online operation], but takes longer time - works little by little. It is not suggested to use for
very fragmented indexes

Hope it helps ...|||Thanks for the info. So basically running both index defrag and dbreindex is redundant because they perform the same function. I will disable my dbreindex job.

Thanks|||http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Remember these are deprecated in 2005. It is also not correct to say that they perform the same function - they perform similar functions.

HTH|||http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Remember these are deprecated in 2005. It is also not correct to say that they perform the same function - they perform similar functions.

HTHWow Pootie...thanks! I read this just in time for it to help me solve the sqlservercentral Question Of The Day!!!
Question: You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?

Correct Answer: ALTER INDEX with the REORGANIZE option

You Answered: ALTER INDEX with the REORGANIZE option

Total Participants: 466

Total Correct Answers: 196 or 42.1% of participants

Explanation:
You should use the ALTER INDEX with the REORGANIZE option because the DBCC commands have been deprecated.|||Wow - you are in the top 42.1% of respondants. Congratulations :beer:|||Hi dsmbwoy,

DBCC DBREINDEX and DBCC INDEXDEFRAG are not one and the same. DBREINDEX sorts the both internal and external fragmantation, whereas INDEXDEFRAG only assists with internal fragmentation. You might want to take a look at the following link, which explains the differences: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx?pf=true

DBREINDEX takes database offline?

HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
SuchiFirst, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
(Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
See inline below for comments on your questions:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> HI All,
>
> Environment: SQL server2000, SP4, on win2003.
> I have few quetions regarding DBCC DBREINDEX..
> 1. Reindex is offline operation means..takes the db offline?
No, it isn't offline in that sense.
> we have optimzation job through maintenace plan. When it started, will
> it takes the database offline by issuing the below command? I heard one DBA
> saying this.
> ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
DATABASE would be executed, then there's no way you can access the database and execute the DBCC
DBREINDEx commands.
> What is my understanding till now is, it will take the table offline by
> issuing xcluve lock.. is that correct?
Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
lock (from memory).
> 2. Database is avaiable for use when reondex is happening?
Yes, except for the table currently being reindexes and having the lock.
>
> 3. When users are already connected, this optimation jobs starts, it will
> kill the existing users?
No
> If no, if the user is having a lock on table, reindex
> is also for the same table, reindex will wait for that user to release the
> lock on the same object or it will kill the user?
Wait. Just as any type lf locking/blocking.
>
> 4. It will allow new users while it is running?
Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
same resource.
>
> Any one can throw some light on above points.
>
> Thanks,
> Suchi|||Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> > HI All,
> >
> >
> > Environment: SQL server2000, SP4, on win2003.
> >
> > I have few quetions regarding DBCC DBREINDEX..
> >
> > 1. Reindex is offline operation means..takes the db offline?
> No, it isn't offline in that sense.
>
> > we have optimzation job through maintenace plan. When it started, will
> > it takes the database offline by issuing the below command? I heard one DBA
> > saying this.
> >
> > ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> >
> > What is my understanding till now is, it will take the table offline by
> > issuing xcluve lock.. is that correct?
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> >
> > 2. Database is avaiable for use when reondex is happening?
> Yes, except for the table currently being reindexes and having the lock.
>
> >
> >
> > 3. When users are already connected, this optimation jobs starts, it will
> > kill the existing users?
> No
>
> > If no, if the user is having a lock on table, reindex
> > is also for the same table, reindex will wait for that user to release the
> > lock on the same object or it will kill the user?
> Wait. Just as any type lf locking/blocking.
>
> >
> >
> > 4. It will allow new users while it is running?
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
> >
> >
> > Any one can throw some light on above points.
> >
> >
> > Thanks,
> > Suchi
>

DBReindex on Clustered Idex

If the rebuild causes root node of clustered index to be changed then it is
a must for SQL Server to update(rebuild) nonclustered index no matter wich k
ind!!!
Example:
Suppose that you're library (clustered index) is in Street One, and your pat
h to the library(nonclustered) is Way 1. If library moves to Street Two ther
e is no point to go through Way 1 as we never get to Street One, but Way 2.
Eh, funny, isn't it?
More info "Indexing Architecture" BOL
Hope it HelpsThat is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...

quote:

> If the rebuild causes root node of clustered index to be changed then it

is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!

quote:

> Example:
> Suppose that you're library (clustered index) is in Street One, and your

path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.

quote:

> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps

|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:

quote:

> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by t
he
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each r
ow
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link ba
ck
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine

<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...

quote:

> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
clustered[QUOTE]
the[QUOTE]
added[QUOTE]
row[QUOTE]
clustered[QUOTE]
back[QUOTE]
non-unique[QUOTE]
per[QUOTE]
> <snip>

|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:[QUOTE]
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values wil
l
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> clustered
> the
> added
> row
> clustered
> back
> non-unique
> per

DBReindex on Clustered Idex

I'm trying to reconcile a difference of information
regarding the effect of running a DBReindex on clustered
index when non-clustered indexes exist.
According to the MSCE Training Kit (SQL Server Database
Design and Implementation), the following sentence
reads "To rebuild all indexes, instruct DBCC DBReindex to
rebuild the clustered index, thereby causing a rebuild of
all indexes on a table or view".
However according to Microsoft Knowledge Base Article
304519, the symptom of bug 354670 is that using either the
Create/Drop existing syntax or DBCC DNReindex syntax on a
clustered index results in both clustered and non-
clustered indexes being rebuilt. This is consistent with
the Training Kit, but here is is mentioned as being a bug
that has been corrected with the service pack. As the
article mentions, unless a non-unique clustered index is
being rebuilt, there should be no impact on non-clustered
indexes.
Can someone point out the correct result then of applying
a rebuild on a clustered index? From my perspective, if
the condition is a bug, why was it presented not so in the
Training Kit? And if the effect of applying a service pack
does change the behaviour, it would make answering any
questions on exams difficult unless one knew the service
pack level. After all, both publications are through
Microsoft.i believe what they're calling a "bug" is that when you rebuilt a unique
clustered index before sp2, it rebuilt all of the non-clustered indexes
when it didn't need to rebuild the non-clustered indexes. see the
"more info" section of
http://support.microsoft.com/default.aspx?scid=kb;en-us;304519
Baz Star wrote:
> I'm trying to reconcile a difference of information
> regarding the effect of running a DBReindex on clustered
> index when non-clustered indexes exist.
> According to the MSCE Training Kit (SQL Server Database
> Design and Implementation), the following sentence
> reads "To rebuild all indexes, instruct DBCC DBReindex to
> rebuild the clustered index, thereby causing a rebuild of
> all indexes on a table or view".
> However according to Microsoft Knowledge Base Article
> 304519, the symptom of bug 354670 is that using either the
> Create/Drop existing syntax or DBCC DNReindex syntax on a
> clustered index results in both clustered and non-
> clustered indexes being rebuilt. This is consistent with
> the Training Kit, but here is is mentioned as being a bug
> that has been corrected with the service pack. As the
> article mentions, unless a non-unique clustered index is
> being rebuilt, there should be no impact on non-clustered
> indexes.
> Can someone point out the correct result then of applying
> a rebuild on a clustered index? From my perspective, if
> the condition is a bug, why was it presented not so in the
> Training Kit? And if the effect of applying a service pack
> does change the behaviour, it would make answering any
> questions on exams difficult unless one knew the service
> pack level. After all, both publications are through
> Microsoft.|||That is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...
> If the rebuild causes root node of clustered index to be changed then it
is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!
> Example:
> Suppose that you're library (clustered index) is in Street One, and your
path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.
> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:
> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by the
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each row
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link back
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
> >
> > That is not true.
> >
> > Non-clustered indexes do not contain physical links back to the
clustered
> > index, just logical links (i.e. the cluster keys). The root node is only
> > stored in database metadata and so is irrelevant to this discussion.
> >
> > For a unique clustered index, the cluster keys are exactly as defined by
the
> > table schema and rebuilding a clustered index does not change any of the
> > cluster keys so none of the non-clustered indexes must be rebuilt.
> >
> > For a non-unique clustered index, an artificial uniquifier column is
added
> > to the defined cluster keys to give a unique logical identifier for each
row
> > in the clustered index. Non-clustered indexes over a non-unique
clustered
> > index have to include the uniquifier column as part of the logical link
back
> > to the clustered index. The uniquifier is regenerated when the
non-unique
> > clustered index is rebuilt, so if any non-clustered indexes must also be
> > rebuilt to pick up the new uniquifier values.
> >
> > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
per
> > se, but a performance drain. The problem was fixed in SP2.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> <snip>|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values will
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> > Paul,
> >
> > if a non-unique clustered index contains no duplicate keys, will other
> > indexes still be reindexed, or is SQL-Server smart enough to only
> > reindex after finding a duplicate in the clustered index tree?
> >
> > Thanks,
> > Gert-Jan
> >
> >
> > "Paul S Randal [MS]" wrote:
> > >
> > > That is not true.
> > >
> > > Non-clustered indexes do not contain physical links back to the
> clustered
> > > index, just logical links (i.e. the cluster keys). The root node is only
> > > stored in database metadata and so is irrelevant to this discussion.
> > >
> > > For a unique clustered index, the cluster keys are exactly as defined by
> the
> > > table schema and rebuilding a clustered index does not change any of the
> > > cluster keys so none of the non-clustered indexes must be rebuilt.
> > >
> > > For a non-unique clustered index, an artificial uniquifier column is
> added
> > > to the defined cluster keys to give a unique logical identifier for each
> row
> > > in the clustered index. Non-clustered indexes over a non-unique
> clustered
> > > index have to include the uniquifier column as part of the logical link
> back
> > > to the clustered index. The uniquifier is regenerated when the
> non-unique
> > > clustered index is rebuilt, so if any non-clustered indexes must also be
> > > rebuilt to pick up the new uniquifier values.
> > >
> > > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
> per
> > > se, but a performance drain. The problem was fixed in SP2.
> > >
> > > Regards.
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > <snip>

DBREINDEX failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or extent
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:
> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or extent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
> > We have a report that uses dbcc showcontig to identify indexes with
> > fragmentation. It shows indexes that have a scan density under 85% or extent
> > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > in the report to rebuild the indexes. When I run the report again a
> > completely different index shows up. During this time no other users or
> > processes are running against the database. Any ideas what may be causing
> > this?
> > --
> > Odell|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > You may want to build all indexes for the given table rather than specific
> > index, especially if the index is a clustered.
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > We have a report that uses dbcc showcontig to identify indexes with
> > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > in the report to rebuild the indexes. When I run the report again a
> > > completely different index shows up. During this time no other users or
> > > processes are running against the database. Any ideas what may be causing
> > > this?
> > > --
> > > Odell|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
> > Thanks for the post. We tried rebuilding all the indexes. It took several
> > hours but it didn't clean up the fragmentation.
> > --
> > Odell
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You may want to build all indexes for the given table rather than specific
> > > index, especially if the index is a clustered.
> > >
> > > John
> > >
> > > "Odell Edwards" wrote:
> > >
> > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > in the report to rebuild the indexes. When I run the report again a
> > > > completely different index shows up. During this time no other users or
> > > > processes are running against the database. Any ideas what may be causing
> > > > this?
> > > > --
> > > > Odell|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > Did you specify the indexes individually or just the table?
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > Thanks for the post. We tried rebuilding all the indexes. It took several
> > > hours but it didn't clean up the fragmentation.
> > > --
> > > Odell
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You may want to build all indexes for the given table rather than specific
> > > > index, especially if the index is a clustered.
> > > >
> > > > John
> > > >
> > > > "Odell Edwards" wrote:
> > > >
> > > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > > in the report to rebuild the indexes. When I run the report again a
> > > > > completely different index shows up. During this time no other users or
> > > > > processes are running against the database. Any ideas what may be causing
> > > > > this?
> > > > > --
> > > > > Odell|||Scan density is meaningless if you have several database files (search the archives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Look at Logical
fragmentation...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes with
>> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
>> > > > > in the report to rebuild the indexes. When I run the report again a
>> > > > > completely different index shows up. During this time no other users or
>> > > > > processes are running against the database. Any ideas what may be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
--
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took
>> > > several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than
>> > > > specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes
>> > > > > with
>> > > > > fragmentation. It shows indexes that have a scan density under
>> > > > > 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes
>> > > > > identified
>> > > > > in the report to rebuild the indexes. When I run the report
>> > > > > again a
>> > > > > completely different index shows up. During this time no other
>> > > > > users or
>> > > > > processes are running against the database. Any ideas what may
>> > > > > be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell

dbreindex causes fragmentation in other indexes

DBREINDEX at threshold for all databases

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on
all indexes in a database that have a logical fragmentation percentage above
a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run
this proc with DBREINDEX instead and I want to schedule it w

ly for all
exising user databases. (The Database Maintenance Wizard is too
inefficient.) If a new database gets added to the environment, I want the
proc to dynamically pick up that new database.
The problem is that this proc has to be executed within the database to be
defragged. I'm having trouble modifying it to loop for each existing
database.
I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
having some problems. This is probably a pretty basic type of maintenance
procedure. Does anyone already have this coded that they would share?I actually have something that may work for you - but not without a little
work. I was trying to do the same thing - a w

ly job that would run on an
y
existing user db's. If you run this, it will pick up all user databases. I
use PRINT @.SQL instead of EXEC because I was unable to get it to execute the
DBREINDEX without error. And right now I'm taking the results of that and
driving the job. If you're able to get around that, please advise.
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC
DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid >6
PRINT @.SQL
-- Lynn
"Stephanie" wrote:

> There is a proc in Books Online that allows you to execute a INDEXDEFRAG o
n
> all indexes in a database that have a logical fragmentation percentage abo
ve
> a specific limit. IT useds SHOWCONTIG and a temporary table. I want to r
un
> this proc with DBREINDEX instead and I want to schedule it w

ly for all
> exising user databases. (The Database Maintenance Wizard is too
> inefficient.) If a new database gets added to the environment, I want the
> proc to dynamically pick up that new database.
> The problem is that this proc has to be executed within the database to be
> defragged. I'm having trouble modifying it to loop for each existing
> database.
> I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
> having some problems. This is probably a pretty basic type of maintenance
> procedure. Does anyone already have this coded that they would share?
>|||Maybe this helps:
http://milambda.blogspot.com/2005/0...in-current.html
It needs a wrapper that will execute it for each database, and you need to
propagate the db_id value through to the dbcc call (currently the value of
db_id is 0 - current database).
ML

DBREINDEX and Transaction Log

I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal ro
llback. This means that
you cannot resume the process. What did you reindex? What indexes does the t
able have? In general,
if you have a clustered index, you need same amount of free space in the dat
abase as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amou
nt (roughly) of space
available for the transaction log (as well for each non-clustered index). On
e thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_
logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't t
ransaction protected).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...[vbc
ol=seagreen]
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used mo
re than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any change
s to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage
.
>[/vbcol]|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.[vbcol=seagreen]
>

DBREINDEX and Transaction Log

I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.
Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>
|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>
|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...[vbcol=seagreen]
more than 5 GB of Log[vbcol=seagreen]
changes to the DB table in the[vbcol=seagreen]
usage.
>

DBREINDEX and Transaction Log

I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log space. Suddenly it needed more free space and the
DBREINDEX failed.
Is it somehow possible to resume the process? There hasn't been any changes
to the DB table in the mean time. If I can't do that then how could I
estimate the log file usage.Ansti
1) Try add more space to database's file location
2) Put the table on different physical disk array
3) Run DBCC INDEXDEFRAG rather DBCC DBREINDEX
"A)nsti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
> more than 5 GB of Log space. Suddenly it needed more free space and the
> DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any
changes
> to the DB table in the mean time. If I can't do that then how could I
> estimate the log file usage.
>|||DBREINDEX is transaction protected. If it failed, the you had an internal rollback. This means that
you cannot resume the process. What did you reindex? What indexes does the table have? In general,
if you have a clustered index, you need same amount of free space in the database as the table. Also
DBREINDEX is fully logged in FULL recovery mode, so you'd need the same amount (roughly) of space
available for the transaction log (as well for each non-clustered index). One thing you can do is to
reindex on one index for the table at a time. Or investigate simple of bulk_logged recovery mode. Or
consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't transaction protected).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ansti" <ansti@.hot.ee> wrote in message news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
>I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used more than 5 GB of Log
>space. Suddenly it needed more free space and the DBREINDEX failed.
> Is it somehow possible to resume the process? There hasn't been any changes to the DB table in the
> mean time. If I can't do that then how could I estimate the log file usage.
>|||Suggest you read the whitepaper below for more details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTg5p7$QFHA.3672@.TK2MSFTNGP10.phx.gbl...
> DBREINDEX is transaction protected. If it failed, the you had an internal
rollback. This means that
> you cannot resume the process. What did you reindex? What indexes does the
table have? In general,
> if you have a clustered index, you need same amount of free space in the
database as the table. Also
> DBREINDEX is fully logged in FULL recovery mode, so you'd need the same
amount (roughly) of space
> available for the transaction log (as well for each non-clustered index).
One thing you can do is to
> reindex on one index for the table at a time. Or investigate simple of
bulk_logged recovery mode. Or
> consider DBCC INDEXDEFRAG (which *can* produce fewer log records and isn't
transaction protected).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ansti" <ansti@.hot.ee> wrote in message
news:42635a90$0$165$bb624dac@.diablo.uninet.ee...
> >I ran a DBCC DBREINDEX on an big table 250 GB for 35 days. It never used
more than 5 GB of Log
> >space. Suddenly it needed more free space and the DBREINDEX failed.
> >
> > Is it somehow possible to resume the process? There hasn't been any
changes to the DB table in the
> > mean time. If I can't do that then how could I estimate the log file
usage.
> >
>

Friday, February 24, 2012

dbreindex and differential backup

Will DBCC DBReindex cause database pages marked as changed so differential b
ackups will bakcup the whole database? Thanks.That is very possible. It's usually a good idea to just do a full backup on
the db if you reindexed all the tables.
Andrew J. Kelly
SQL Server MVP
"digi" <anonymous@.discussions.microsoft.com> wrote in message
news:863E59AD-E1E7-4DAB-B05F-2E990563504E@.microsoft.com...
> Will DBCC DBReindex cause database pages marked as changed so differential
backups will bakcup the whole database? Thanks.|||That is very possible. It's usually a good idea to just do a full backup on
the db if you reindexed all the tables.
Andrew J. Kelly
SQL Server MVP
"digi" <anonymous@.discussions.microsoft.com> wrote in message
news:863E59AD-E1E7-4DAB-B05F-2E990563504E@.microsoft.com...
> Will DBCC DBReindex cause database pages marked as changed so differential
backups will bakcup the whole database? Thanks.

dbreindex and change recovery model

Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
DBREINDEX), is there a way to programmtically change the db recovery model
to bulk-logged before this process runs, and then change it back to full at
the conclusion?
Example...?
Thanks.
Message posted via http://www.droptable.comCheck out ALTER DATABASE in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4308fa21ddaf4db6b1242ec457edac5e@.SQ
droptable.com...
Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
DBREINDEX), is there a way to programmtically change the db recovery model
to bulk-logged before this process runs, and then change it back to full at
the conclusion?
Example...?
Thanks.
Message posted via http://www.droptable.com|||Yes
before...
alter database YourDatabase set Recovery BULK_LOGGED
after
alter database YourDatabase set Recovery Simple
or
alter database YourDatabase set Recovery Full
Depending on your cup of tea...
Enjoy
Peter
"The Gekkster via droptable.com" wrote:

> Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
> DBREINDEX), is there a way to programmtically change the db recovery model
> to bulk-logged before this process runs, and then change it back to full a
t
> the conclusion?
> Example...?
> Thanks.
> --
> Message posted via http://www.droptable.com
>|||Wow, that's easy. Thanks guys...
Message posted via http://www.droptable.com|||See "alter database" in BOL.
Example:
use master
go
alter database northwind
set recovery bulk_logged
go
AMB
"The Gekkster via droptable.com" wrote:

> Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
> DBREINDEX), is there a way to programmtically change the db recovery model
> to bulk-logged before this process runs, and then change it back to full a
t
> the conclusion?
> Example...?
> Thanks.
> --
> Message posted via http://www.droptable.com
>

dbreindex and change recovery model

Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
DBREINDEX), is there a way to programmtically change the db recovery model
to bulk-logged before this process runs, and then change it back to full at
the conclusion?
Example...?
Thanks.
Message posted via http://www.droptable.com
Check out ALTER DATABASE in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4308fa21ddaf4db6b1242ec457edac5e@.droptable.co m...
Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
DBREINDEX), is there a way to programmtically change the db recovery model
to bulk-logged before this process runs, and then change it back to full at
the conclusion?
Example...?
Thanks.
Message posted via http://www.droptable.com
|||Yes
before...
alter database YourDatabase set Recovery BULK_LOGGED
after
alter database YourDatabase set Recovery Simple
or
alter database YourDatabase set Recovery Full
Depending on your cup of tea...
Enjoy
Peter
"The Gekkster via droptable.com" wrote:

> Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
> DBREINDEX), is there a way to programmtically change the db recovery model
> to bulk-logged before this process runs, and then change it back to full at
> the conclusion?
> Example...?
> Thanks.
> --
> Message posted via http://www.droptable.com
>
|||Wow, that's easy. Thanks guys...
Message posted via http://www.droptable.com
|||See "alter database" in BOL.
Example:
use master
go
alter database northwind
set recovery bulk_logged
go
AMB
"The Gekkster via droptable.com" wrote:

> Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
> DBREINDEX), is there a way to programmtically change the db recovery model
> to bulk-logged before this process runs, and then change it back to full at
> the conclusion?
> Example...?
> Thanks.
> --
> Message posted via http://www.droptable.com
>

dbreindex and change recovery model

Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
DBREINDEX), is there a way to programmtically change the db recovery model
to bulk-logged before this process runs, and then change it back to full at
the conclusion?
Example...?
Thanks.
--
Message posted via http://www.sqlmonster.comCheck out ALTER DATABASE in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:4308fa21ddaf4db6b1242ec457edac5e@.SQLMonster.com...
Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
DBREINDEX), is there a way to programmtically change the db recovery model
to bulk-logged before this process runs, and then change it back to full at
the conclusion?
Example...?
Thanks.
--
Message posted via http://www.sqlmonster.com|||Yes
before...
alter database YourDatabase set Recovery BULK_LOGGED
after
alter database YourDatabase set Recovery Simple
or
alter database YourDatabase set Recovery Full
Depending on your cup of tea...
Enjoy
Peter
"The Gekkster via SQLMonster.com" wrote:
> Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
> DBREINDEX), is there a way to programmtically change the db recovery model
> to bulk-logged before this process runs, and then change it back to full at
> the conclusion?
> Example...?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Wow, that's easy. Thanks guys...
--
Message posted via http://www.sqlmonster.com|||See "alter database" in BOL.
Example:
use master
go
alter database northwind
set recovery bulk_logged
go
AMB
"The Gekkster via SQLMonster.com" wrote:
> Borrowing from Example 'E' in BOL for DBCC SHOWCONTIG (and modified to use
> DBREINDEX), is there a way to programmtically change the db recovery model
> to bulk-logged before this process runs, and then change it back to full at
> the conclusion?
> Example...?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com
>

dbreindex

Hi,
I would like to know what methodology other users use in
order to trim down the transaction log after running 'dbcc
dbreindex'. I can think of couple ways like the following
but would like to know any better ways to automate the
whole proces:
- place a 'trunc. trans log' statement before & after the
command to avoid the excessive log
- run shrinkfile after the command is run
Any ideas/suggestions?Why do you want to shrink it in the first place? If it needed to get that
big today, don't you think it will need to be that big again the next time
you run reindex? Growing and shrinking of the files are expensive and
absolutely un-necessary in most cases. It might be good to backup the log
after a reindex but certainly don't shrink it.
--
Andrew J. Kelly
SQL Server MVP
"Pete." <darksage618@.hotmail.com> wrote in message
news:04c101c365d1$f13ddf40$a401280a@.phx.gbl...
> Hi,
> I would like to know what methodology other users use in
> order to trim down the transaction log after running 'dbcc
> dbreindex'. I can think of couple ways like the following
> but would like to know any better ways to automate the
> whole proces:
> - place a 'trunc. trans log' statement before & after the
> command to avoid the excessive log
> - run shrinkfile after the command is run
> Any ideas/suggestions?|||In addition to Andrew's post:
Consider using DBCC INDEXDEFRAG instead. It will most probably cut doesn the amount of changes done
(hence cut down on the log records produced), depending on how much reorg there is to be performed.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Pete." <darksage618@.hotmail.com> wrote in message news:04c101c365d1$f13ddf40$a401280a@.phx.gbl...
> Hi,
> I would like to know what methodology other users use in
> order to trim down the transaction log after running 'dbcc
> dbreindex'. I can think of couple ways like the following
> but would like to know any better ways to automate the
> whole proces:
> - place a 'trunc. trans log' statement before & after the
> command to avoid the excessive log
> - run shrinkfile after the command is run
> Any ideas/suggestions?

Saturday, February 25, 2012

DBREINDEX/INDEXDEFRAG a few questions

dbreindex vs index defrag question

DBREINDEX takes database offline?

DBREINDEX takes database offline?

DBReindex on Clustered Idex

DBReindex on Clustered Idex

DBREINDEX failed with DB ONLINE and filegroup read-only

dbreindex causes fragmentation in other indexes

dbreindex causes fragmentation in other indexes

DBREINDEX at threshold for all databases

DBREINDEX and Transaction Log

DBREINDEX and Transaction Log

DBREINDEX and Transaction Log

Friday, February 24, 2012

dbreindex and differential backup

dbreindex and differential backup

dbreindex and change recovery model

dbreindex and change recovery model

dbreindex and change recovery model

dbreindex

deAttach database

Blog Archive

About Me