Re: DBReindex
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sun, 17 Feb 2008 15:29:21 -0800
thejamie (thejamie@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I'm using a standard SQL Server edition and when I went to update my old
dbcc dbreindex routine, I tried to update the dbcc dbreindex command with
ALTER INDEX '+@index+'
ON '+@TableName+'
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = ON, ONLINE = ON )'
When I replace the older statement with this one, I see
"Online index operations can only be performed in Enterprise edition of
SQL Server."
I assume there is a way to rebuild indexes in SQL 2005 but for me it isn't
what is above.
Change ONLINE = ON to ONLINE = OFF, and you essentially have DBCC
DBREINDEX.
When the jobs began to fail that come with the wizard maintenance, I
replaced it with the dbcc dbreindex command. As I see this is causing
my log problem, I am not sure where to go next. For example, not sure
if maybe I should switch to something like "REBUILD PARTITION".
First of all, I hope you are backing up your transaction log regularly?
What you can do to reduce the strain on the transaction of rebuilding
indexes is to set the recovery model to BULK_LOGGED while your maintenance
job is running.
BULK_LOGGED is to a large extent the same as full recovery, but some
actions are minimally logged in bulk-logged recovery, for instance the
creation of indexes. Bulk-logged recovery still gives the possibiliy
to restore the database up to the state of the latest transaction log.
However, you cannot restore to a point in time if there has been any
minimally logged operation since the last log backup.
An action you also can consider is to add some intelligence to your
job, so that you only rebuild indexes on tables that have more than,
say, 30% in fragmentation.
The fact that an index rebuild eats log is not strange: the entire table
is essentially moved to a new location.
You can also consider ALTER INDEX REORGANIZE this command can be
kinder to the log - but in extreme situations, the tool may be higher.
And bulk-logged recovery does not help in this case.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: DBReindex
- From: Ola Hallengren
- Re: DBReindex
- References:
- DBReindex
- From: thejamie
- DBReindex
- Prev by Date: DBReindex
- Next by Date: Re: DBReindex
- Previous by thread: DBReindex
- Next by thread: Re: DBReindex
- Index(es):
Relevant Pages
|
