dbcc reindex & dbcc shrinkdatabase?

Hello,
When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
database I've noticed that the amount of space and free space in the databas
e
goes up. This sort makes sense as when creating the indexes originally, the
fillfactor was set to 90%
My question is, if I run "dbcc shrinkdatabase" right after "dbcc reindex" wi
ll
it take back the free space from the leaf level page where there should be 2
0%
free space left for new indexes, effectively screwing up the fillfactor
previously defined in the "dbcc reindex" command?
Thanks,
Craig.Craig,
I don't believe that shrink will mess with the page contents, but it will
relocatee pages, which will effectively undo much of the value of the
reindex. Whatever the technical details, please be assured that it will
undo some of the performance gain that you just created for yourself.
Advice: view the freespace in the database as the price of doing business.
Russell Fields
"Craig" <spam@.[at]thehurley.[dot]com> wrote in message
news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
> Hello,
> When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
> database I've noticed that the amount of space and free space in the
database
> goes up. This sort makes sense as when creating the indexes originally,
the
> fillfactor was set to 90%
>
> My question is, if I run "dbcc shrinkdatabase" right after "dbcc reindex"
will
> it take back the free space from the leaf level page where there should be
20%
> free space left for new indexes, effectively screwing up the fillfactor
> previously defined in the "dbcc reindex" command?
> Thanks,
> Craig.|||Shrink in SQL Server 2000 will definitely not mess with page contents (i.e.
fillfactor) but may screw up the index fragmentation as a result of moving
pages around.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OLm2#$K$DHA.2512@.TK2MSFTNGP11.phx.gbl...
> Craig,
> I don't believe that shrink will mess with the page contents, but it will
> relocatee pages, which will effectively undo much of the value of the
> reindex. Whatever the technical details, please be assured that it will
> undo some of the performance gain that you just created for yourself.
> Advice: view the freespace in the database as the price of doing business.
> Russell Fields
>
> "Craig" <spam@.[at]thehurley.[dot]com> wrote in message
> news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
> database
> the
reindex"
> will
be
> 20%
>|||Paul,

> Shrink in SQL Server 2000 will definitely not mess with page contents
(i.e.
> fillfactor)
Does this apply also for heap data pages? I did some tests a while ago and
the tests indicates that individual rows are not moved for heap pages.
However, the MS MOC SQL Server programming course has a picture indicating
that for heap pages, individual rows are moved. Would be nice to have a
confirmation. :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23RAFFzM$DHA.2524@.tk2msftngp13.phx.gbl...
> Shrink in SQL Server 2000 will definitely not mess with page contents
(i.e.
> fillfactor) but may screw up the index fragmentation as a result of moving
> pages around.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hello again,
An increase in the data file is acceptable but not the log file. What I've
started testing out is scheduling the "dbcc reindex", then "dbcc shrinkfile
(logFileName)" but the log file stays pretty much the same size due to the f
act
that the majority of the log file is being used by the virtual log so there'
s
not much free space to reclaim.
I don't want to change the db mode to simple as the transaction log is too
important.
I've read a tip on the web about issuing "backup log dbname with no_log", an
d
this does indeed shrink the virtual logs size, but not the logical size. No
problem, I just issue "dbcc shrinkfile (logFileName)" to shrink the logical
size. What the tip didn't explain was how much data am I loosing from the lo
g?
Can someone please explain what this actually does or explain a better metho
d of
keeping the log file size to a minimum while using "dbcc reindex"?
TIA,
Craig.
Russell Fields wrote:

> Craig,
> I don't believe that shrink will mess with the page contents, but it will
> relocatee pages, which will effectively undo much of the value of the
> reindex. Whatever the technical details, please be assured that it will
> undo some of the performance gain that you just created for yourself.
> Advice: view the freespace in the database as the price of doing business.
> Russell Fields
>
> "Craig" <spam@.[at]thehurley.[dot]com> wrote in message
> news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
>
> database
>
> the
>
> will
>
> 20%
>
>
>|||I've just tested out using bulk logging mode and the amount of log space use
d
when using "dbcc reindex" has dramatically reduced. Here's a script to chang
e to
bulk logging mode, reindex then change back to full logging mode.
Regards,
Craig.
-- ========================================
=================================
====
-- Date: 2004.FEB.27
-- Name: m_dbreindex
-- Description: Create maintenance procedure to reindex *dbname*. Change db
mode -- to bulk to reduce logging while creating indexes. When done,
-- change db mode back to full. Mode BULK_LOGGED reduces logging
-- for: SELECT INTO, CREATE INDEX and bulk loading.
-- Parameters: @.pFillFactor (int) - percentage fillfactor
-- Example: EXEC m_dbreindex 90
-- Note: Rename *dbname* to db name!
-- ========================================
=================================
====
USE *dbname* -- rename
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'm_dbreindex' AND type =
'P')
DROP PROCEDURE m_dbreindex
GO
CREATE PROCEDURE m_dbreindex
@.pFillFactor int
AS
ALTER DATABASE *dbname* SET RECOVERY BULK_LOGGED -- rename | change db mode
DECLARE @.TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@.TableName, ' ', @.pFillFactor)
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
ALTER DATABASE *dbname* SET RECOVERY FULL -- rename | change db mode
GO
Craig wrote:
> Hello again,
> An increase in the data file is acceptable but not the log file. What
> I've started testing out is scheduling the "dbcc reindex", then "dbcc
> shrinkfile (logFileName)" but the log file stays pretty much the same
> size due to the fact that the majority of the log file is being used by
> the virtual log so there's not much free space to reclaim.
> I don't want to change the db mode to simple as the transaction log is
> too important.
> I've read a tip on the web about issuing "backup log dbname with
> no_log", and this does indeed shrink the virtual logs size, but not the
> logical size. No problem, I just issue "dbcc shrinkfile (logFileName)"
> to shrink the logical size. What the tip didn't explain was how much
> data am I loosing from the log?
> Can someone please explain what this actually does or explain a better
> method of keeping the log file size to a minimum while using "dbcc
> reindex"?
> TIA,
> Craig.
> Russell Fields wrote:
>|||Tibor - the rows do get moved individually but only while moving that single
page. When a page is moved, all the rows from that page end up on the same
newly allocated page - there is no compaction.
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:uHcPWyR$DHA.4060@.TK2MSFTNGP10.phx.gbl...
> Paul,
>
> (i.e.
> Does this apply also for heap data pages? I did some tests a while ago and
> the tests indicates that individual rows are not moved for heap pages.
> However, the MS MOC SQL Server programming course has a picture indicating
> that for heap pages, individual rows are moved. Would be nice to have a
> confirmation. :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23RAFFzM$DHA.2524@.tk2msftngp13.phx.gbl...
> (i.e.
moving
> rights.
>

dbcc reindex & dbcc shrinkdatabase?

Hello,
When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
database I've noticed that the amount of space and free space in the database
goes up. This sort makes sense as when creating the indexes originally, the
fillfactor was set to 90%
My question is, if I run "dbcc shrinkdatabase" right after "dbcc reindex" will
it take back the free space from the leaf level page where there should be 20%
free space left for new indexes, effectively screwing up the fillfactor
previously defined in the "dbcc reindex" command?
Thanks,
Craig.Craig,
I don't believe that shrink will mess with the page contents, but it will
relocatee pages, which will effectively undo much of the value of the
reindex. Whatever the technical details, please be assured that it will
undo some of the performance gain that you just created for yourself.
Advice: view the freespace in the database as the price of doing business.
Russell Fields
"Craig" <spam@.[at]thehurley.[dot]com> wrote in message
news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
> Hello,
> When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
> database I've noticed that the amount of space and free space in the
database
> goes up. This sort makes sense as when creating the indexes originally,
the
> fillfactor was set to 90%
>
> My question is, if I run "dbcc shrinkdatabase" right after "dbcc reindex"
will
> it take back the free space from the leaf level page where there should be
20%
> free space left for new indexes, effectively screwing up the fillfactor
> previously defined in the "dbcc reindex" command?
> Thanks,
> Craig.|||Shrink in SQL Server 2000 will definitely not mess with page contents (i.e.
fillfactor) but may screw up the index fragmentation as a result of moving
pages around.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OLm2#$K$DHA.2512@.TK2MSFTNGP11.phx.gbl...
> Craig,
> I don't believe that shrink will mess with the page contents, but it will
> relocatee pages, which will effectively undo much of the value of the
> reindex. Whatever the technical details, please be assured that it will
> undo some of the performance gain that you just created for yourself.
> Advice: view the freespace in the database as the price of doing business.
> Russell Fields
>
> "Craig" <spam@.[at]thehurley.[dot]com> wrote in message
> news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
> > database I've noticed that the amount of space and free space in the
> database
> > goes up. This sort makes sense as when creating the indexes originally,
> the
> > fillfactor was set to 90%
> >
> >
> > My question is, if I run "dbcc shrinkdatabase" right after "dbcc
reindex"
> will
> > it take back the free space from the leaf level page where there should
be
> 20%
> > free space left for new indexes, effectively screwing up the fillfactor
> > previously defined in the "dbcc reindex" command?
> >
> > Thanks,
> > Craig.
>|||Paul,
> Shrink in SQL Server 2000 will definitely not mess with page contents
(i.e.
> fillfactor)
Does this apply also for heap data pages? I did some tests a while ago and
the tests indicates that individual rows are not moved for heap pages.
However, the MS MOC SQL Server Programming course has a picture indicating
that for heap pages, individual rows are moved. Would be nice to have a
confirmation. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23RAFFzM$DHA.2524@.tk2msftngp13.phx.gbl...
> Shrink in SQL Server 2000 will definitely not mess with page contents
(i.e.
> fillfactor) but may screw up the index fragmentation as a result of moving
> pages around.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hello again,
An increase in the data file is acceptable but not the log file. What I've
started testing out is scheduling the "dbcc reindex", then "dbcc shrinkfile
(logFileName)" but the log file stays pretty much the same size due to the fact
that the majority of the log file is being used by the virtual log so there's
not much free space to reclaim.
I don't want to change the db mode to simple as the transaction log is too
important.
I've read a tip on the web about issuing "backup log dbname with no_log", and
this does indeed shrink the virtual logs size, but not the logical size. No
problem, I just issue "dbcc shrinkfile (logFileName)" to shrink the logical
size. What the tip didn't explain was how much data am I loosing from the log?
Can someone please explain what this actually does or explain a better method of
keeping the log file size to a minimum while using "dbcc reindex"?
TIA,
Craig.
Russell Fields wrote:
> Craig,
> I don't believe that shrink will mess with the page contents, but it will
> relocatee pages, which will effectively undo much of the value of the
> reindex. Whatever the technical details, please be assured that it will
> undo some of the performance gain that you just created for yourself.
> Advice: view the freespace in the database as the price of doing business.
> Russell Fields
>
> "Craig" <spam@.[at]thehurley.[dot]com> wrote in message
> news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
>>Hello,
>>When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
>>database I've noticed that the amount of space and free space in the
> database
>>goes up. This sort makes sense as when creating the indexes originally,
> the
>>fillfactor was set to 90%
>>
>>My question is, if I run "dbcc shrinkdatabase" right after "dbcc reindex"
> will
>>it take back the free space from the leaf level page where there should be
> 20%
>>free space left for new indexes, effectively screwing up the fillfactor
>>previously defined in the "dbcc reindex" command?
>>Thanks,
>>Craig.
>
>|||I've just tested out using bulk logging mode and the amount of log space used
when using "dbcc reindex" has dramatically reduced. Here's a script to change to
bulk logging mode, reindex then change back to full logging mode.
Regards,
Craig.
-- =============================================================================-- Date: 2004.FEB.27
-- Name: m_dbreindex
-- Description: Create maintenance procedure to reindex *dbname*. Change db
mode -- to bulk to reduce logging while creating indexes. When done,
-- change db mode back to full. Mode BULK_LOGGED reduces logging
-- for: SELECT INTO, CREATE INDEX and bulk loading.
-- Parameters: @.pFillFactor (int) - percentage fillfactor
-- Example: EXEC m_dbreindex 90
-- Note: Rename *dbname* to db name!
-- =============================================================================USE *dbname* -- rename
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'm_dbreindex' AND type = 'P')
DROP PROCEDURE m_dbreindex
GO
CREATE PROCEDURE m_dbreindex
@.pFillFactor int
AS
ALTER DATABASE *dbname* SET RECOVERY BULK_LOGGED -- rename | change db mode
DECLARE @.TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@.TableName, ' ', @.pFillFactor)
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
ALTER DATABASE *dbname* SET RECOVERY FULL -- rename | change db mode
GO
Craig wrote:
> Hello again,
> An increase in the data file is acceptable but not the log file. What
> I've started testing out is scheduling the "dbcc reindex", then "dbcc
> shrinkfile (logFileName)" but the log file stays pretty much the same
> size due to the fact that the majority of the log file is being used by
> the virtual log so there's not much free space to reclaim.
> I don't want to change the db mode to simple as the transaction log is
> too important.
> I've read a tip on the web about issuing "backup log dbname with
> no_log", and this does indeed shrink the virtual logs size, but not the
> logical size. No problem, I just issue "dbcc shrinkfile (logFileName)"
> to shrink the logical size. What the tip didn't explain was how much
> data am I loosing from the log?
> Can someone please explain what this actually does or explain a better
> method of keeping the log file size to a minimum while using "dbcc
> reindex"?
> TIA,
> Craig.
> Russell Fields wrote:
>> Craig,
>> I don't believe that shrink will mess with the page contents, but it will
>> relocatee pages, which will effectively undo much of the value of the
>> reindex. Whatever the technical details, please be assured that it will
>> undo some of the performance gain that you just created for yourself.
>> Advice: view the freespace in the database as the price of doing
>> business.
>> Russell Fields
>>
>> "Craig" <spam@.[at]thehurley.[dot]com> wrote in message
>> news:ekQCGvJ$DHA.220@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> When I run "dbcc reindex" with a fill factor of 80% for all indexes in a
>> database I've noticed that the amount of space and free space in the
>>
>> database
>> goes up. This sort makes sense as when creating the indexes originally,
>>
>> the
>> fillfactor was set to 90%
>>
>> My question is, if I run "dbcc shrinkdatabase" right after "dbcc
>> reindex"
>>
>> will
>> it take back the free space from the leaf level page where there
>> should be
>>
>> 20%
>> free space left for new indexes, effectively screwing up the fillfactor
>> previously defined in the "dbcc reindex" command?
>> Thanks,
>> Craig.
>>
>>|||Tibor - the rows do get moved individually but only while moving that single
page. When a page is moved, all the rows from that page end up on the same
newly allocated page - there is no compaction.
--
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:uHcPWyR$DHA.4060@.TK2MSFTNGP10.phx.gbl...
> Paul,
> > Shrink in SQL Server 2000 will definitely not mess with page contents
> (i.e.
> > fillfactor)
> Does this apply also for heap data pages? I did some tests a while ago and
> the tests indicates that individual rows are not moved for heap pages.
> However, the MS MOC SQL Server Programming course has a picture indicating
> that for heap pages, individual rows are moved. Would be nice to have a
> confirmation. :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23RAFFzM$DHA.2524@.tk2msftngp13.phx.gbl...
> > Shrink in SQL Server 2000 will definitely not mess with page contents
> (i.e.
> > fillfactor) but may screw up the index fragmentation as a result of
moving
> > pages around.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
>

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

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
Cheers
If an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>

DBCC DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
CheersIf an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>

DBCC DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
CheersIf an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>