DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-Smith
My guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegro ups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>
|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegro ups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>
|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:[vbcol=seagreen]
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reindex
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegro ups.com...

DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-SmithMy guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
--
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reindex
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
> >I have a 400 gb database that I reindex once monthly through a
> > maintenance plan. The reindexing has consistently taken 13hrs for the
> > last year or so, and this last month it took almost 66hrs.
> > I haven't had any drastic changes in my DB structure, any massive data
> > loads or deletions, there were no scheduled jobs at the time of the
> > reindex (at least not during the time it usually takes), no one is
> > fessing up to any adhoc queries or data modifications during the normal
> > reindexing time, and there are no errors reported in either the job
> > log, or the SLQ error logs.
> > I have worked a bit with Microsoft PSS on this, but they really can't
> > do any post mortem on this and essentially wished me luck in a
> > reduction in my reindexing time next month.
> >
> > Where else would you look for clues if this were your DB?
> > I am working on redesigning my reindexing plans and potentially moving
> > to a dbcc indexdefrag method for the future, but just leaving it at 'I
> > don't know" isn't sitting well with me or my CIO.
> >
> >
> > Any ideas?
> >
> > Meredith Ryan-Smith
> >

DBCC Reindex suddenly takes 5 times longer than it has

I have a 400 gb database that I reindex once monthly through a
maintenance plan. The reindexing has consistently taken 13hrs for the
last year or so, and this last month it took almost 66hrs.
I haven't had any drastic changes in my DB structure, any massive data
loads or deletions, there were no scheduled jobs at the time of the
reindex (at least not during the time it usually takes), no one is
fessing up to any adhoc queries or data modifications during the normal
reindexing time, and there are no errors reported in either the job
log, or the SLQ error logs.
I have worked a bit with Microsoft PSS on this, but they really can't
do any post mortem on this and essentially wished me luck in a
reduction in my reindexing time next month.
Where else would you look for clues if this were your DB?
I am working on redesigning my reindexing plans and potentially moving
to a dbcc indexdefrag method for the future, but just leaving it at 'I
don't know" isn't sitting well with me or my CIO.
Any ideas?
Meredith Ryan-SmithMy guess is that it was waiting for a lock and there was a query that hadn't
been committed that was holding it. No way to find that out for the last run
but if it happens again, look at sp_lock to see if its blocked.
I'd be happy to help you work out a better fragmentation removal plan (I
wrote DBCC INDEXDEFRAG) - take out the 'nospam' and drop me a line if you're
interested.
Paul Randal
Principal Lead Program Manager
Core Storage Engine, Microsoft SQL Server Team
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||Meredith,
Even a 13 hour window is probably too long for a maintenance job anyway.
More than likely, some of your tables do not need reindexing. One approach
is to schedule reindex jobs more often, maybe on weekends, and only reindex
a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are in
the worst shape, and also which tables might not need re-indexing.
Very definitely, take advantage of Paul's offer to explore defrag. It far
less resuource intensive and in many cases is all you need. And you sure
won't find a better qualified mentor.
-- Bill
"mryan" <coffegrl@.gmail.com> wrote in message
news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...
>I have a 400 gb database that I reindex once monthly through a
> maintenance plan. The reindexing has consistently taken 13hrs for the
> last year or so, and this last month it took almost 66hrs.
> I haven't had any drastic changes in my DB structure, any massive data
> loads or deletions, there were no scheduled jobs at the time of the
> reindex (at least not during the time it usually takes), no one is
> fessing up to any adhoc queries or data modifications during the normal
> reindexing time, and there are no errors reported in either the job
> log, or the SLQ error logs.
> I have worked a bit with Microsoft PSS on this, but they really can't
> do any post mortem on this and essentially wished me luck in a
> reduction in my reindexing time next month.
> Where else would you look for clues if this were your DB?
> I am working on redesigning my reindexing plans and potentially moving
> to a dbcc indexdefrag method for the future, but just leaving it at 'I
> don't know" isn't sitting well with me or my CIO.
>
> Any ideas?
> Meredith Ryan-Smith
>|||I am very much looking forward to taking advantage of Paul's offer for
help, and I would agree that 13hrs is too long for a single maintenance
job. I've only recently inherited this system from a DBA that recently
left the company, and I am in the process of slowly discovering all the
little quirks and making changes as needed.
Meredith
AlterEgo wrote:[vbcol=seagreen]
> Meredith,
> Even a 13 hour window is probably too long for a maintenance job anyway.
> More than likely, some of your tables do not need reindexing. One approach
> is to schedule reindex jobs more often, maybe on weekends, and only reinde
x
> a couple of tables at a time. Use DBCC SHOWCONTIG to see which tables are
in
> the worst shape, and also which tables might not need re-indexing.
> Very definitely, take advantage of Paul's offer to explore defrag. It far
> less resuource intensive and in many cases is all you need. And you sure
> won't find a better qualified mentor.
> -- Bill
>
> "mryan" <coffegrl@.gmail.com> wrote in message
> news:1169149741.244719.118910@.51g2000cwl.googlegroups.com...sql

dbcc reindex issue - - I dont understand!

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

for all tables in the database. Then I compared the dbcc showcontig
with all_index output from before and after the reindex and on the
largest table in the database I found this. First output is prior to
reindex:

Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
7
TABLE level scan performed.
- Pages Scanned........................: 184867
- Extents Scanned.......................: 23203
- Extent Switches.......................: 23324
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
- Logical Scan Fragmentation ..............: 11.13%
- Extent Scan Fragmentation ...............: 35.46%
- Avg. Bytes Free per Page................: 60.0
- Avg. Page Density (full)................: 99.26%

Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned........................: 303177
- Extents Scanned.......................: 37964
- Extent Switches.......................: 42579
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..............: 43.19%
- Extent Scan Fragmentation ...............: 24.78%
- Avg. Bytes Free per Page................: 75.1
- Avg. Page Density (full)................: 99.07%

Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Did you not already post this message before, or am i experiencing deja
vu?

Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?

Raziq Shekha wrote:
> Hi Folks,
> SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
> the command :
> dbcc dbreindex ('tablename')
> go
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%
>
> Second output is from after the reindex:
>
> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177
> - Extents Scanned.......................: 37964
> - Extent Switches.......................: 42579
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 89.00% [37898:42580]
> - Logical Scan Fragmentation ..............: 43.19%
> - Extent Scan Fragmentation ...............: 24.78%
> - Avg. Bytes Free per Page................: 75.1
> - Avg. Page Density (full)................: 99.07%
>
> Following are my concerns:
> The following numbers are all higher after reindex than before reindex:
> pages scanned, extent switches, logical scan fragmentation, avg bytes
> free per page, avg page density.
> scan density is lower after reindex than before reindex
> Seems to me that the numbers that are higher after reindex should be
> lower and numbers that are lower after reindex should be higher? I
> didn't specify the fill factor in the dbcc reindex command so it should
> have used the default fill factor. The fill factor has never been
> changed on this machine.
> Am I missing something?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Raziq Shekha (raziq_shekha@.anadarko.com) writes:
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned........................: 184867
> - Extents Scanned.......................: 23203
> - Extent Switches.......................: 23324
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..............: 11.13%
> - Extent Scan Fragmentation ...............: 35.46%
> - Avg. Bytes Free per Page................: 60.0
> - Avg. Page Density (full)................: 99.26%

With this scan density, defragmentation may be no be very useful.

> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned........................: 303177

I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.

*** Sent via Developersdex http://www.developersdex.com ***

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

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

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

dbcc reindex ?

Is it required to do a dbcc reindex after a restore of a
database ? Has any one heard or read about this anyplace ?
TIA,
JackJack
There is nothing about the RESTORE itself that necessitates a REINDEX.
Restore will put all the data back on the same page numbers as in the
original database. If there was fragmentation before the backup, there will
be fragmentation after the restore. If you just did a REINDEX and removed
all fragmentation before backing up, there is no need to do it again after
restore.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jack A" <Jacka8@.excite.com> wrote in message
news:019b01c365a8$fd0d8a60$a301280a@.phx.gbl...
> Is it required to do a dbcc reindex after a restore of a
> database ? Has any one heard or read about this anyplace ?
> TIA,
> Jack|||Just want to add to Kalen's fine response. You should update the statistics
after a restore to ensure you are starting with an up to date set.
--
Andrew J. Kelly
SQL Server MVP
"Jack A" <Jacka8@.excite.com> wrote in message
news:019b01c365a8$fd0d8a60$a301280a@.phx.gbl...
> Is it required to do a dbcc reindex after a restore of a
> database ? Has any one heard or read about this anyplace ?
> TIA,
> Jacksql

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:
On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR to
zero by using DBCC REINDEX option, but it is not allowing it to reset it to
default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT zero.
Any suggestions ''?
thanks in advance,
Kamran.Kamran,
0 equates to 100 which is the default and you can't use the value 0 (must be
between 1-99). I doubt you want the fillfactor to be anywhere near zero.
HTH
Jerry
"Kamran" <Kamran@.discussions.microsoft.com> wrote in message
news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> Hi:
> On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> to
> zero by using DBCC REINDEX option, but it is not allowing it to reset it
> to
> default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> zero.
> Any suggestions ''?
> thanks in advance,
> Kamran.|||thanks Jerry..
"Jerry Spivey" wrote:
> Kamran,
> 0 equates to 100 which is the default and you can't use the value 0 (must be
> between 1-99). I doubt you want the fillfactor to be anywhere near zero.
> HTH
> Jerry
> "Kamran" <Kamran@.discussions.microsoft.com> wrote in message
> news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> > Hi:
> >
> > On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> > to
> > zero by using DBCC REINDEX option, but it is not allowing it to reset it
> > to
> > default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> > zero.
> >
> > Any suggestions ''?
> >
> > thanks in advance,
> >
> > Kamran.
>
>

dbcc reindex

If dbcc reindex attempts to lock a table for reindexing,
but can't get exclusive access, does it just sit there
interminably? If so, can I use "set lock timeout" to make
it skip tables on which it cannot get exclusive locks?Yes, it will just wait to get the locks it needs for the reindex and yes,
you can use the lock_timeout to make it skip the locked table.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:016501c3da14$cf13d390$a601280a@.phx.gbl...
> If dbcc reindex attempts to lock a table for reindexing,
> but can't get exclusive access, does it just sit there
> interminably? If so, can I use "set lock timeout" to make
> it skip tables on which it cannot get exclusive locks?

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:
On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR to
zero by using DBCC REINDEX option, but it is not allowing it to reset it to
default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT zero.
Any suggestions ''?
thanks in advance,
Kamran.Kamran,
0 equates to 100 which is the default and you can't use the value 0 (must be
between 1-99). I doubt you want the fillfactor to be anywhere near zero.
HTH
Jerry
"Kamran" <Kamran@.discussions.microsoft.com> wrote in message
news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> Hi:
> On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> to
> zero by using DBCC REINDEX option, but it is not allowing it to reset it
> to
> default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> zero.
> Any suggestions ''?
> thanks in advance,
> Kamran.|||thanks Jerry..
"Jerry Spivey" wrote:

> Kamran,
> 0 equates to 100 which is the default and you can't use the value 0 (must
be
> between 1-99). I doubt you want the fillfactor to be anywhere near zero.
> HTH
> Jerry
> "Kamran" <Kamran@.discussions.microsoft.com> wrote in message
> news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
>
>

dbcc reindex

If dbcc reindex attempts to lock a table for reindexing,
but can't get exclusive access, does it just sit there
interminably? If so, can I use "set lock timeout" to make
it skip tables on which it cannot get exclusive locks?Yes, it will just wait to get the locks it needs for the reindex and yes,
you can use the lock_timeout to make it skip the locked table.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"superboy" <anonymous@.discussions.microsoft.com> wrote in message
news:016501c3da14$cf13d390$a601280a@.phx.gbl...

quote:

---

> If dbcc reindex attempts to lock a table for reindexing,
> but can't get exclusive access, does it just sit there
> interminably? If so, can I use "set lock timeout" to make
> it skip tables on which it cannot get exclusive locks?

---

DBCC REINDEX

Hi:
On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR to
zero by using DBCC REINDEX option, but it is not allowing it to reset it to
default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT zero.
Any suggestions ???
thanks in advance,
Kamran.
Kamran,
0 equates to 100 which is the default and you can't use the value 0 (must be
between 1-99). I doubt you want the fillfactor to be anywhere near zero.
HTH
Jerry
"Kamran" <Kamran@.discussions.microsoft.com> wrote in message
news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> Hi:
> On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> to
> zero by using DBCC REINDEX option, but it is not allowing it to reset it
> to
> default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> zero.
> Any suggestions ???
> thanks in advance,
> Kamran.
|||thanks Jerry..
"Jerry Spivey" wrote:

> Kamran,
> 0 equates to 100 which is the default and you can't use the value 0 (must be
> between 1-99). I doubt you want the fillfactor to be anywhere near zero.
> HTH
> Jerry
> "Kamran" <Kamran@.discussions.microsoft.com> wrote in message
> news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.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 memusage problem

When I use ShrinkDB or reindex and some other DB performance utilities, then when I use DBCC memusage it throws some error however it get corrected when I restart SQL Server
Is there any simplest way to correct this problem without restarting the SQL server
Regards
SunilCan you share the error? We might be able to debug this more easily.
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming
--
Server: Msg 8966, Level 16, State 4, Line
Could not read and latch page (5:562) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1621) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1622) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1623) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1809) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1810) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1811) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1812) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1813) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1814) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1815) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1816) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1840) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1841) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1842) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1843) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1844) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1845) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1846) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1847) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1848) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1863) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1864) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1865) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1866) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1867) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1868) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1869) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1870) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1871) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1872) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1873) with latch type SH. VerifyPageId failed
Server: Msg 8966, Level 16, State 1, Line
Could not read and latch page (8:1874) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId failed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId failed.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC memusage problem

When I use ShrinkDB or reindex and some other DB performance utilities, then
when I use DBCC memusage it throws some error however it get corrected when
I restart SQL Server.
Is there any simplest way to correct this problem without restarting the SQL
server.
Regards,
SunilCan you share the error? We might be able to debug this more easily.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Sunil" <anonymous@.discussions.microsoft.com> wrote in message
news:41FCD39E-3F6F-44B7-88FE-31ADC023397B@.microsoft.com...
> When I use ShrinkDB or reindex and some other DB performance utilities,
then when I use DBCC memusage it throws some error however it get corrected
when I restart SQL Server.
>
> Is there any simplest way to correct this problem without restarting the
SQL server.
>
> Regards,
> Sunil|||This error is coming :
--
Server: Msg 8966, Level 16, State 4, Line 1
Could not read and latch page (5:562) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1621) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1622) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1623) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1809) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1810) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1811) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1812) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1813) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1814) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1815) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1816) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1840) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1841) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1842) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1843) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1844) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1845) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1846) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1847) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1848) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1863) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1864) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1865) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1866) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1867) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1868) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1869) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1870) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1871) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1872) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1873) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1874) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1849) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1850) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1851) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1852) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1875) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1876) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1877) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1878) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1488) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1489) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1490) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1560) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1561) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1562) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1563) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1564) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1565) with latch type SH. VerifyPageId fail
ed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (8:1567) with latch type SH. VerifyPageId fail
ed.
Could not read and latch page (1:811) with latch type SH. VerifyPageId faile
d.
Server: Msg 8966, Level 16, State 1, Line 1
BCC execution completed. If DBCC printed error messages, contact your system
administrator.