Re: DBReindex

Hi
Taking look at your script , you do not have to run UDDATE STATISTICS as
dbcc dbreindex does it
I'd suggest you to read up BOL on the subject as it has even sample script
to rebuild indexes

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b796c829-ef3a-405c-a784-48286d4fb2b9.htm


"thejamie" <thejamie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3284791E-FC3B-4EF7-AF26-4D0835EFE47E@xxxxxxxxxxxxxxxx
There is a post on the Small Business Server site regarding the use of
dbcc
dbreindex titled "Can anyone 'crash course' me in SQL Maintenance?" and
the
user asks why hte logs are growing so large. Having a similar problem
with
my SQL 2005 databases, I was surprised to discover that dbcc dbreindex may
cause the log to swell. http://support.microsoft.com/kb/873235

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

The job below runs weekly as part of a maintenance task and is without
question the result of the logs getting so large.

declare @TableName sysname
declare c_Tables cursor for
select Table_Name
from information_schema.tables
where Table_Type = 'Base Table'

open c_Tables
fetch next from c_Tables into @TableName
while @@fetch_status = 0 begin
print @TableName
dbcc dbreindex( @TableName, '', 90 )
execute( N' Update Statistics ' + @TableName )
fetch next from c_Tables into @TableName
end
close c_Tables
deallocate c_Tables

--
Regards,
Jamie


.

Relevant Pages

  • Re: DBReindex
    ... The job I posted was for SQL 2000 - the script was something my predecessor ... I was surprised to discover that dbcc dbreindex may ... fetch next from c_Tables into @TableName ...
    (microsoft.public.sqlserver.security)
  • Re: DBCC DBREINDEX and Transactional Replication
    ... > this way I won't offend Paul:)) ... In SQL 2000 I've seen the log impact of DBCC DBREINDEX as high, ...
    (microsoft.public.sqlserver.replication)