repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
Thanks
Part of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:

> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks
|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talkin
g
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:

> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink i
t
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room f
or
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160
GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - do
es
> anyone have an idea how long it will take on a 160GB database? Are we talk
ing
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...[vbcol=seagreen]
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
>
weekend[vbcol=seagreen]
it[vbcol=seagreen]
I[vbcol=seagreen]
for[vbcol=seagreen]
around[vbcol=seagreen]
160GB[vbcol=seagreen]
does[vbcol=seagreen]
talking[vbcol=seagreen]

repair_rebuild and the transaction log

We have a large-ish database (160GB) which grew to over 300GB last weekend
when the maintenance job failed. Half of it is unused but I can't shrink it
due to index errors and when I run checkdb it also reports index errors. I
would like to run checkdb with repair_rebuild but there is not much room for
the log file to grow now the DB has gotten so big - it will max out around
8-10GB.
My questions are:
Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
database? If so can I just move the log file to a drive with more room?
I can't copy the DB to another server and test out the repair_rebuild - does
anyone have an idea how long it will take on a 160GB database? Are we talking
several hours or days?
Is DBCC reindex a better option in my case?
ThanksPart of DBCC CHECK With Repair_rebuild option is rebuilding corrupt
indexes. How about adding a new transaction log to a different drive
instead of moving existing log file?
Yih-Yoon Lee
J Jetson wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||t8-10GB is too small for 160GB db to run checkdb.
You can move log file to a big disk with detach and atach syntax and then
run check db. Alternatively, you can turn Recovery Model from "Full" to
"Simple" in DB Options.
Given that, SQL server will not grow log file. It's same as trunc. log on
chkpt in SQL 7.
Zrich
"J Jetson" wrote:
> We have a large-ish database (160GB) which grew to over 300GB last weekend
> when the maintenance job failed. Half of it is unused but I can't shrink it
> due to index errors and when I run checkdb it also reports index errors. I
> would like to run checkdb with repair_rebuild but there is not much room for
> the log file to grow now the DB has gotten so big - it will max out around
> 8-10GB.
> My questions are:
> Will repair_rebuild need the log file to grow any larger than 8GB on a 160GB
> database? If so can I just move the log file to a drive with more room?
> I can't copy the DB to another server and test out the repair_rebuild - does
> anyone have an idea how long it will take on a 160GB database? Are we talking
> several hours or days?
> Is DBCC reindex a better option in my case?
> Thanks|||> t8-10GB is too small for 160GB db to run checkdb.
I don't know how you can possibly say that when you have no idea what
corruptions exist in the database or the size of the indexes that the
repair_rebuild option may cause to be rebuilt.
Can you post the output from DBCC CHECKDB? Also, have you done root-cause
analysis to work out why the corruption happened? (e.g. look through the SQL
errorlog and Windows event logs for hardware errors).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:92D7B4F1-0637-4A56-BB98-4EE0C002239C@.microsoft.com...
> t8-10GB is too small for 160GB db to run checkdb.
> You can move log file to a big disk with detach and atach syntax and then
> run check db. Alternatively, you can turn Recovery Model from "Full" to
> "Simple" in DB Options.
> Given that, SQL server will not grow log file. It's same as trunc. log on
> chkpt in SQL 7.
> Zrich
> "J Jetson" wrote:
> > We have a large-ish database (160GB) which grew to over 300GB last
weekend
> > when the maintenance job failed. Half of it is unused but I can't shrink
it
> > due to index errors and when I run checkdb it also reports index errors.
I
> > would like to run checkdb with repair_rebuild but there is not much room
for
> > the log file to grow now the DB has gotten so big - it will max out
around
> > 8-10GB.
> >
> > My questions are:
> >
> > Will repair_rebuild need the log file to grow any larger than 8GB on a
160GB
> > database? If so can I just move the log file to a drive with more room?
> >
> > I can't copy the DB to another server and test out the repair_rebuild -
does
> > anyone have an idea how long it will take on a 160GB database? Are we
talking
> > several hours or days?
> >
> > Is DBCC reindex a better option in my case?
> >
> > Thanks

Reorganize data and index pages

Hello all,

SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
gives me an option to reorganize data and index pages. When I check on
it, it populates the line "change free space per page percentage to" and
puts in 10 in there. Is this the default for free space? Is it the
data pages that will have 10% free space or just the index pages? Are
data and index on the same pages?

Thanks,
Raziq.

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

If you profile the maintainance plan is seems that choosing causes a call to
DBCC DBREINDEX which is documented in books online. The 10% free space value
is reversed to be a 90% fillfactor as described by:

fillfactor
Is the percentage of space on each index page to be used for storing data
when the index is created. fillfactor replaces the original fillfactor as
the new default for the index and for any other nonclustered indexes rebuilt
because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
uses the original fillfactor specified when the index was created.

If you look at the Table and Index Architecture section of books online you
will see how clustered and non-clustered indexes are composed.
or at:
http://msdn.microsoft.com/library/d...ar_da2_8sit.asp
http://msdn.microsoft.com/library/d...ar_da2_1tbn.asp
http://msdn.microsoft.com/library/d...ar_da2_75mb.asp

You may want to get "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 which talks about this sort of thing in detail.

John

"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:rF97e.5$Nk2.347@.news.uswest.net...
> Hello all,
> SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
> gives me an option to reorganize data and index pages. When I check on
> it, it populates the line "change free space per page percentage to" and
> puts in 10 in there. Is this the default for free space? Is it the
> data pages that will have 10% free space or just the index pages? Are
> data and index on the same pages?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reorganize data and index pages

I have a couple of questions here so I will break them out.
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>

Reorganization and logs

I have a db maintenance plan set up that reorganizes my data once a week. I
noticed that the SQL Server Log does not indicate that this has run.
However, if I use the option to 'Write report to a text file in directory:',
a .txt file is created that indicates the reorganization has run. Why does
this not show up in the SQL Server Log as my reindexing and backups do? I'm
running SQL 2000.
The backups really shouldn't be in there either in my opinion. The log is
mainly for error info and a successful backup can be found in the job
history. If you want to know if and when it ran you can check the Job
History and the MP history.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:B604E4EE-8C77-4B5D-8B05-EBFC42BE0023@.microsoft.com...
> I have a db maintenance plan set up that reorganizes my data once a week.
I
> noticed that the SQL Server Log does not indicate that this has run.
> However, if I use the option to 'Write report to a text file in
directory:',
> a .txt file is created that indicates the reorganization has run. Why
does
> this not show up in the SQL Server Log as my reindexing and backups do?
I'm
> running SQL 2000.

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.com|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>