DBReindex

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: High Extent Scan Fragmentation
    ... understands the dbreindex every night is probably overkill -- but he's ... of single insert statements (to try and create fragmentation). ... we check the ext. scan frag before and after the dbcc dbreindex. ... CREATE CLUSTERED INDEX ON ...
    (microsoft.public.sqlserver.server)
  • RE: Chain Linkage Problem
    ... We are using Enterprise version of SQL 2000. ... resolving the original issue ... > Have you run DBCC CheckDB and what version of SQL are you on? ... >> And when I run DBCC CHECKTABLE it shows consistency errors. ...
    (microsoft.public.sqlserver.server)
  • Re: MDF explore
    ... I use DBCC PAGE a LOT just to see what exactly ... SQL Server is doing. ... This is an SQL command, ... > When I first learned about it I thought it would be immensely useful for ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpoint SPID Blocked
    ... CheckDB and DBCC Checkalloc didn't turn up any allocation errors. ... really need to run DBCC CHECKDB on this database. ... We have a situation where the checkpoint process is ... > SQL just kept going about its business up until the point where another ...
    (microsoft.public.sqlserver.server)
  • RE: Errors while running DBCC CheckDb
    ... If its ver.7 then ensure to apply latest Service pack 4 to the SQL. ... Also run DBCC CHECK with REPAIR clause in order to resolve the issue, if not check event viewer for any information on Hardware issues on the SQL server. ... > Could not read and latch page with latch type SH. ...
    (microsoft.public.sqlserver.server)