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.
>