SQL Server index help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Server index help

Author

Topic

espanolanthony
Starting Member

9 Posts

Posted - 2009-09-18 : 10:04:30

I have SQL Server 2005. i have a script to rebuild reorganise index. now that script runs only on a particular database only, and the other is i also need to bring down the database in simple recovery mode before running the script. Now can someone modift the given script to
run it for all the database (make a store procedure and runs from master dbase), and the other is need to add somewhere in the script that will take the database in simple recovery and after completion of the rebuild/reorganise again change it to full recovery mode.
the script is given below:

----------------------------------------------------
-- Automatic index rebuild and reorganizing
-- Reorganizes indexes with fragmentation between 10 and 30%
-- Rebuilds indexes with more than 30% fragmentation
----------------------------------------------------

declare @sql nvarchar(1000)
declare @indexname nvarchar(255)
declare @tablename nvarchar(255)
declare @fragmentation float
declare @msg nvarchar(512)
declare @dbid int
declare @indexCount int

set nocount on

set @dbid=db_id()
set @indexCount = 0

declare c CURSOR FOR
Select 'ALTER INDEX ' + i.name + ' ON ' + object_name(d.object_id) + CASE WHEN avg_fragmentation_in_percent>30 THEN ' REBUILD' ELSE ' REORGANIZE' END as [sql],
convert(decimal(5,2), avg_fragmentation_in_percent) fragmentation,object_name(d.object_id),i.name
from sys.dm_db_index_physical_stats( @dbid,null, -1, null, 'SAMPLED') d -- or 'DETAILED'
inner join sys.indexes i on i.object_id=d.object_id and i.index_id=d.index_id
where d.avg_fragmentation_in_percent > 10
order by avg_fragmentation_in_percent desc

select N'See "Messages" tab for progress!' as Info
raiserror (N'Reading index fragmentation..',0,1) WITH NOWAIT
raiserror (N' ',0,1) WITH NOWAIT

open c

fetch next from c INTO @sql,@fragmentation,@tablename,@indexname

while @@FETCH_STATUS = 0
begin
SET @msg = N'Found fragmented index..'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Name: ' + @indexname
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Table: ' + @tablename
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' Fragmentation: ' + cast(@fragmentation as nvarchar) + '%s'
raiserror (@msg,0,1,'%') WITH NOWAIT

exec sp_executesql @sql
set @indexCount = @indexCount + 1

SET @msg = N' Defrag done!'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N' '
raiserror (@msg,0,1) WITH NOWAIT

fetch next from c INTO @sql,@fragmentation,@tablename,@indexname
end

close c
deallocate c

SET @msg = N'--------------------------------'
raiserror (@msg,0,1) WITH NOWAIT
SET @msg = N'Found and defragged ' + cast(@indexCount as nvarchar) + N' index(es)'
raiserror (@msg,0,1) WITH NOWAIT

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-18 : 11:37:26

Why do you want to switch to simple recovery? What's that going to do to your backups and your recovery strategy?

The modifications you're asking for are not trivial. It's probably easier to duplicate the code in a SQL job with one step per database, or to add it as a stored proc in each database and have a master proc somewhere (maybe master, not usually recommended) that calls all the procs

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources