DBREINDEX

Question

Post reply

DBREINDEX

Colin Betteley

Hall of Fame

Points: 3095
More actions
April 26, 2007 at 6:16 am

#69737

Hi,
I have a number of Databases on the same server, each with 20/30 Indexes, different in each case. Currently I run DBREINDEX agaianst each Dbase and each table on which an index exists.
Is there a way to simplify this process by running a single command againt an individual Database? BOL does not provide a solution (that I can find). I suppose that there must be some sort of iteration through a system table?
Thanks in advance for any suggestions.
Colin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

this is what we use to roll through all our databases and tables to reindex

declare @db varchar(50)

declare getdbs cursor for select name

from master..sysdatabases

where name not in ('master','msdb','model','tempdb','pubs','Northwind')

for read only

open getdbs

fetch next from getdbs into @db

while @@fetch_status -1

begin

print '*********************************************************************'

select 'Processing ' + @db

declare @tblid int, @tblname varchar(50)

declare @innercursor varchar (255)

declare @dbccstmt varchar (255)

select @innercursor = 'declare tbls cursor for select name,id

from ' + @db + '..sysobjects

where type = ''U''

and name not like ''dt%''

and name not like ''sys%''

order by name for read only'

exec (@innercursor)

open tbls

fetch next from tbls into @tblname, @tblid

While (@@fetch_status -1)

begin

select @dbccstmt = 'dbcc showcontig (' +

rtrim(convert(char(50),@tblid)) + ')'

exec ('USE ' + @db + ' ' + @dbccstmt)

select 'Doing DBREINDEX on ' + @tblname

exec ('USE ' + @db + ' ' + 'DBCC DBREINDEX ("[' + @tblname + ']",'''',80)')

select 'Updating statistics for ' + @tblname

exec ('USE ' + @db + ' UPDATE STATISTICS ' + @tblName)

fetch next from tbls into @tblname, @tblid

end

close tbls

deallocate tbls

print ' '

fetch next from getdbs into @db

end

close getdbs

deallocate getdbs

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 2

Adam,

Many thanks for that. I see that you are setting the fillfactor at 80; as I do not know the existing fillfator I think that I should replace the 80 with a 0 in order to retain the original value.

Can you expand on what the other exec commands are doing in your innercursor?

Will ceratinly test this on our Dev server, when it becomes availble next week.

Thanks

Colin

Bulent Gucuk SSCertifiable Points: 5498 More actions · Answer 3

if you run EXEC sp_configure you will see the configured value for the fill factor. But that does not mean that the index was created with default fill factor. Look into table and index and figure out the insert/update rate and you can come out with a fill factor you need.

first execute statement rund dbcc showcontig

second runs the dbcc dbreindex

third runs the dbcc updateusage

check out the bol for all the above commands

Good day,

Bulent

Wayne West SSC-Insane Points: 22586 More actions · Answer 4

This is my nightly DBCC run, I run it through an osql job so that the full output can be captured:

use master
go

exec sp_msforeachdb @command1="print getdate() print '? checkdb' DBCC CHECKDB(?)"
go

exec sp_msforeachdb @command1="print getdate() print '? checkalloc' DBCC CHECKALLOC(?)"
go

exec sp_msforeachdb @command1="print getdate() print '? checkcatalog' DBCC CHECKCATALOG(?) print ''"
go

exec sp_msforeachtable @command1="print getdate() print '? Update Statistics' UPDATE STATISTICS ? WITH FULLSCAN"
go

exec sp_msforeachtable @command1="print getdate() print '? DBReindex' dbcc dbreindex('?', '', 90)"
go

sp_msforeachtable/db are undocumented system stored procedures that walk through each database/table on the system. I hope they're present in 2005...

This is the osql job that calls it, everything is wrapped up in a scheduled job:

osql -S"sqlservername" -E -id:\dbccs\nightlydbcc.sql -od:\dbccs\dbccresult.txt

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

JeremyE SSCoach Points: 16227 More actions · Answer 5

I use this to reindex everything in a single db:

Declare reindex CURSOR

FOR

Select 'DBCC DBREINDEX (N''[' + u.name + '].[' + o.name + ']'', [' + i.name + '], 90)'

from sysindexes i

join sysobjects o

on i.id = o.id

join sysusers u

on o.uid = u.uid

where indid >= 1

and i.name not like '_WA_Sys%'

and i.name not like 'nc%'

and i.name not like 'sys%'

and i.name not like 'tsys%'

and i.name not like 'uc%'

order by o.name

OPEN reindex

DECLARE @index sysname

FETCH NEXT FROM reindex INTO @index

WHILE (@@FETCH_STATUS = 0)

BEGIN

EXEC (@index)

FETCH NEXT FROM reindex INTO @index

END

DEALLOCATE reindex