DBCC DBREINDEX mytable , 90)
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
Thanks
Sql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>
|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex?
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>
|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>
|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>
Showing posts with label factor. Show all posts
Showing posts with label factor. Show all posts
Thursday, March 22, 2012
DBCC Reindex factor
DBCC DBREINDEX mytable , 90)
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
ThanksSql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex'
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex'
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
ThanksSql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex'
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex'
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>
DBCC Reindex factor
DBCC DBREINDEX mytable , 90)
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
ThanksSql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex'
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex'
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means I
>> have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
--
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it is
>> time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means I
>> have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>> The problem is that data size is 5gb, that db doesn't have that much
>> transactions.
>> I do weekly reindex. after I finish db reindex the log size (allocation )
>> is like 21 GB. I have to do shrink log file.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it is
>> time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means
>> I have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>>
>|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
--
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> What recovery mode are you running? If you are in FULL and don't do any
>> log backups during the reindex process your log must be able to hold all
>> the log entries from the reindex process. If you are using DBCC
>> INDEXDEFRAG this can result in a lot of log entries. Try setting the
>> recovery mode to simple just before the reindex and then back to full
>> when done. Just remember to do a FULL backup to reinitialize the log
>> chain.
>> --
>> Andrew J. Kelly SQL MVP
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>> The problem is that data size is 5gb, that db doesn't have that much
>> transactions.
>> I do weekly reindex. after I finish db reindex the log size
>> (allocation ) is like 21 GB. I have to do shrink log file.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it
>> is time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means
>> I have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>>
>>
>
fACTOR IS 90% MEANS 90% full... is that correct?
I feel that my table is 90% free space for that dbcc statement i ran.
Because after reindex the db size is 20gb instead of 5 gb. I have to shrink
it after reindex. any ideas?
ThanksSql2005 no sp applied yet.
after doing dbcc dbreindex with 90% factor. All dbs data and log files
allocation space is very big (70-80% of acture data space). That means I
have to do shrink db(both data and log) after reindex'
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
> DBCC DBREINDEX mytable , 90)
> fACTOR IS 90% MEANS 90% full... is that correct?
> I feel that my table is 90% free space for that dbcc statement i ran.
> Because after reindex the db size is 20gb instead of 5 gb. I have to
> shrink it after reindex. any ideas?
> Thanks
>|||mecn,
How much of it is data space and how much is log space? If the log became
very large, that is easy to shrink, but shrinking the data is usually not a
wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
If you are actually working that close to full on your disks, then it is
time to considering buying more space.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
> Sql2005 no sp applied yet.
> after doing dbcc dbreindex with 90% factor. All dbs data and log files
> allocation space is very big (70-80% of acture data space). That means I
> have to do shrink db(both data and log) after reindex'
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>|||The problem is that data size is 5gb, that db doesn't have that much
transactions.
I do weekly reindex. after I finish db reindex the log size (allocation ) is
like 21 GB. I have to do shrink log file.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
> mecn,
> How much of it is data space and how much is log space? If the log became
> very large, that is easy to shrink, but shrinking the data is usually not
> a wise thing to do. www.karaszi.com/SQLServer/info_dont_shrink.asp
> If you are actually working that close to full on your disks, then it is
> time to considering buying more space.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means I
>> have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>|||What recovery mode are you running? If you are in FULL and don't do any log
backups during the reindex process your log must be able to hold all the log
entries from the reindex process. If you are using DBCC INDEXDEFRAG this can
result in a lot of log entries. Try setting the recovery mode to simple just
before the reindex and then back to full when done. Just remember to do a
FULL backup to reinitialize the log chain.
--
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
> The problem is that data size is 5gb, that db doesn't have that much
> transactions.
> I do weekly reindex. after I finish db reindex the log size (allocation )
> is like 21 GB. I have to do shrink log file.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it is
>> time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means I
>> have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>|||Full
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
> What recovery mode are you running? If you are in FULL and don't do any
> log backups during the reindex process your log must be able to hold all
> the log entries from the reindex process. If you are using DBCC
> INDEXDEFRAG this can result in a lot of log entries. Try setting the
> recovery mode to simple just before the reindex and then back to full when
> done. Just remember to do a FULL backup to reinitialize the log chain.
> --
> Andrew J. Kelly SQL MVP
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>> The problem is that data size is 5gb, that db doesn't have that much
>> transactions.
>> I do weekly reindex. after I finish db reindex the log size (allocation )
>> is like 21 GB. I have to do shrink log file.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it is
>> time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means
>> I have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>>
>|||Well that is the answer then. If you are in FULL recovery mode every part of
the reindex will be fully logged and the log will grow when it runs out of
room.
--
Andrew J. Kelly SQL MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%232kuyUgzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> Full
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%238xyvfXzHHA.5980@.TK2MSFTNGP04.phx.gbl...
>> What recovery mode are you running? If you are in FULL and don't do any
>> log backups during the reindex process your log must be able to hold all
>> the log entries from the reindex process. If you are using DBCC
>> INDEXDEFRAG this can result in a lot of log entries. Try setting the
>> recovery mode to simple just before the reindex and then back to full
>> when done. Just remember to do a FULL backup to reinitialize the log
>> chain.
>> --
>> Andrew J. Kelly SQL MVP
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:%23%23SZtgTzHHA.748@.TK2MSFTNGP04.phx.gbl...
>> The problem is that data size is 5gb, that db doesn't have that much
>> transactions.
>> I do weekly reindex. after I finish db reindex the log size
>> (allocation ) is like 21 GB. I have to do shrink log file.
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:eeRBGaTzHHA.5408@.TK2MSFTNGP02.phx.gbl...
>> mecn,
>> How much of it is data space and how much is log space? If the log
>> became very large, that is easy to shrink, but shrinking the data is
>> usually not a wise thing to do.
>> www.karaszi.com/SQLServer/info_dont_shrink.asp
>> If you are actually working that close to full on your disks, then it
>> is time to considering buying more space.
>> RLF
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:uL61FJTzHHA.4476@.TK2MSFTNGP06.phx.gbl...
>> Sql2005 no sp applied yet.
>> after doing dbcc dbreindex with 90% factor. All dbs data and log files
>> allocation space is very big (70-80% of acture data space). That means
>> I have to do shrink db(both data and log) after reindex'
>> Thanks
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:Oz3LYFTzHHA.464@.TK2MSFTNGP02.phx.gbl...
>> DBCC DBREINDEX mytable , 90)
>> fACTOR IS 90% MEANS 90% full... is that correct?
>> I feel that my table is 90% free space for that dbcc statement i ran.
>> Because after reindex the db size is 20gb instead of 5 gb. I have to
>> shrink it after reindex. any ideas?
>> Thanks
>>
>>
>>
>>
>>
>
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.
>
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.
> >
>
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.
> >
>
Subscribe to:
Posts (Atom)