Re: DBReindex

So your design goals are about like this.

The database should be available 24/7. The users should also be able to work
while the indexes are being rebuilt. The transaction log size should be kept
small.

Then I think that we can summarize the solution something like this.

1. Only rebuild / reorganize indexes with fragmentation.
2. Use the BULK_LOGGED recovery model if possible.
3. Rebuild indexes with ONLINE = ON if possible.

Ola Hallengren
http://ola.hallengren.com



"thejamie" wrote:

My immediate solution involved writing some of the history files that went
back to the year 2003 to tape and putting it in a storage area. This freed
up enough space to allow the log to have expansion room but did not solve the
issue of the logs swelling. Using the code from the BOL fixed that issue but
not completely due to the doubling up of shifts in the warehouse and the need
for 24/7 access.

The job I posted was for SQL 2000 - the script was something my predecessor
had left behind. The log issue was on the SQL 2005 indexes - 2000 does not
appear to be effected but the 2000 job was not working in the 2005
environment.

I like using a combination of BULK-LOGGING (Thanks Erland) and Ola's script
may be the better solution - I am testing that at my home network on my city
database.

Although the BOL solution works fine, [thanks Uri], to give Erland credit, I
have been able to setup the bulk-logging in the past while indexing runs and
it was very efficient. With the warehouse operating nearly 24/7 and I am
looking for the better solution given that users may be working when the
indexes are rebuilt. My supervisor wants me to switch to SIMPLE mode. I am
experimenting on my home office environment with OLA's scripts (I do not like
the SIMPLE solution) and I am hoping these may be better suited to the
situation.

I bought myself time by writing data off the system that was not required
for day to day business.

Thanks for all the help. It is appreciated.
--
Regards,
Jamie


"Uri Dimant" wrote:

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: DBReindex
    ... Only rebuild / reorganize indexes with fragmentation. ... The job I posted was for SQL 2000 - the script was something my predecessor ... I was surprised to discover that dbcc dbreindex may ...
    (microsoft.public.sqlserver.security)
  • Re: DB Versioning options
    ... We would like to be able to script out the entire DB, ... data and be able to rebuild the DB directly from those scripts. ... Is there a tool that can do this and still uphold constraints and key ... It handles SQL 2000 databases that have been moved to 2005 just fine. ...
    (borland.public.delphi.non-technical)
  • Re: When should one rebuild an index?
    ... from internal Oracle resources is the same. ... I see no part of this doc suggesting ANALYZE being useless in determining index rebuild. ... Knock yourself out explaining whether this index should or should not be rebuilt based on the information generated by ANALYZE. ... SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE; ...
    (comp.databases.oracle.server)
  • Re: are you indirect, I mean, feeding because of encouraging citizens
    ... Mike to revive it. ... These days, go rebuild a script! ... What doesn't Milton commit ...
    (sci.crypt)