DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this makes
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:
> > The database recovery models are set to full, is it simply all the index's
> > are being rebuilt and under the full recovery model everything is being
> > logged?
> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> > Hi all
> >
> > I include the 'Reorganize pages with the original amount of free space'
> > option on my database maintenance plan. On some of my databases this makes
> > the transaction log balloon. What's the mechanics of this option?
> >
> > The database recovery models are set to full, is it simply all the index's
> > are being rebuilt and under the full recovery model everything is being
> > logged?
>sql

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command wil
l first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only
reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/pr...r />
idbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX
or INDEXDEFRAG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this make
s
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:

> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command w
ill first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will onl
y reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/pr.../>
2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREIND
EX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
>

DBCC Reindex

Hi all
I include the 'Reorganize pages with the original amount of free space'
option on my database maintenance plan. On some of my databases this makes
the transaction log balloon. What's the mechanics of this option?
The database recovery models are set to full, is it simply all the index's
are being rebuilt and under the full recovery model everything is being
logged?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
index, then drop the old one. All is logged. Two suggestions:
Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
are fragmented in the first place.
But first read http://www.microsoft.com/technet/pro.../ss2kidbp.mspx so you
can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
> Hi all
> I include the 'Reorganize pages with the original amount of free space'
> option on my database maintenance plan. On some of my databases this makes
> the transaction log balloon. What's the mechanics of this option?
> The database recovery models are set to full, is it simply all the index's
> are being rebuilt and under the full recovery model everything is being
> logged?
|||Cool thanks for your help Tibor
"Tibor Karaszi" wrote:

> Correct. Maint Plan executes DBCC DBREINDEX for all tables. This command will first create a new
> index, then drop the old one. All is logged. Two suggestions:
> Use the code in Books Online, under DBCC SHOWCONTIG instead. This will only reindex the indexes that
> are fragmented in the first place.
> But first read http://www.microsoft.com/technet/pro.../ss2kidbp.mspx so you
> can determine whether to do reindex or not and using which method (DBREINDEX or INDEXDEFRAG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:4E359BFE-8A82-445C-BD22-8136F6F9C506@.microsoft.com...
>
sql

DBCC DBREINDEX

Does "DBCC DBREINDEX" include pk's?
The reason I ask is because I know that pk's are first and formost a
constraint... But I also am aware that an index is created on the pk and th
e
pk index nam is stored in sysindexes.
So I guess I'm just verifying that DBCC DBREINDEX does include the pk's inde
x.
Thanks...Yes, if it was declared as a PK constraint it will have an index behind it
to enforce it. And yes DBREINDEX will treat it like any other index.
Andrew J. Kelly SQL MVP
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:6E625833-97A6-4A54-B263-D427B10B220E@.microsoft.com...
> Does "DBCC DBREINDEX" include pk's?
> The reason I ask is because I know that pk's are first and formost a
> constraint... But I also am aware that an index is created on the pk and
> the
> pk index nam is stored in sysindexes.
> So I guess I'm just verifying that DBCC DBREINDEX does include the pk's
> index.
> Thanks...
>|||Thanks...
Does DBCC DBREINDEX use the SORT_IN_TEMPDB option?
"Andrew J. Kelly" wrote:

> Yes, if it was declared as a PK constraint it will have an index behind it
> to enforce it. And yes DBREINDEX will treat it like any other index.
> --
> Andrew J. Kelly SQL MVP
>
> "BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
> news:6E625833-97A6-4A54-B263-D427B10B220E@.microsoft.com...
>
>|||Not in 2000 but ALTER INDEX has that option in 2005.
Andrew J. Kelly SQL MVP
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:76E334B6-42A4-44AA-B608-CF0B2B99C2D6@.microsoft.com...
> Thanks...
> Does DBCC DBREINDEX use the SORT_IN_TEMPDB option?
>
> "Andrew J. Kelly" wrote:
>

DBCC DBREINDEX

Does "DBCC DBREINDEX" include pk's?
The reason I ask is because I know that pk's are first and formost a
constraint... But I also am aware that an index is created on the pk and the
pk index nam is stored in sysindexes.
So I guess I'm just verifying that DBCC DBREINDEX does include the pk's index.
Thanks...
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:C0D8618B-3836-4BD5-A7A6-14B91E91A87E@.microsoft.com...
> Does "DBCC DBREINDEX" include pk's?
> The reason I ask is because I know that pk's are first and formost a
> constraint... But I also am aware that an index is created on the pk and
> the
> pk index nam is stored in sysindexes.
> So I guess I'm just verifying that DBCC DBREINDEX does include the pk's
> index.
> Thanks...

DBCC DBREINDEX

Does "DBCC DBREINDEX" include pk's?
The reason I ask is because I know that pk's are first and formost a
constraint... But I also am aware that an index is created on the pk and the
pk index nam is stored in sysindexes.
So I guess I'm just verifying that DBCC DBREINDEX does include the pk's index.
Thanks...Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:C0D8618B-3836-4BD5-A7A6-14B91E91A87E@.microsoft.com...
> Does "DBCC DBREINDEX" include pk's?
> The reason I ask is because I know that pk's are first and formost a
> constraint... But I also am aware that an index is created on the pk and
> the
> pk index nam is stored in sysindexes.
> So I guess I'm just verifying that DBCC DBREINDEX does include the pk's
> index.
> Thanks...

DBCC DBREINDEX

Does "DBCC DBREINDEX" include pk's?
The reason I ask is because I know that pk's are first and formost a
constraint... But I also am aware that an index is created on the pk and th
e
pk index nam is stored in sysindexes.
So I guess I'm just verifying that DBCC DBREINDEX does include the pk's inde
x.
Thanks...Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:C0D8618B-3836-4BD5-A7A6-14B91E91A87E@.microsoft.com...
> Does "DBCC DBREINDEX" include pk's?
> The reason I ask is because I know that pk's are first and formost a
> constraint... But I also am aware that an index is created on the pk and
> the
> pk index nam is stored in sysindexes.
> So I guess I'm just verifying that DBCC DBREINDEX does include the pk's
> index.
> Thanks...