September 18, 2009 at 8:04 am
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
September 18, 2009 at 8:18 am
I am sure that someone will be kind enough to spend the time to do this for you. however based on your comments, I am making the assumption, you didnt write this script on your own, if you are running log backups on any of your databases, you will invalidate those backups if you switch recovery model between simple and full so bear in mind that you will need to do a full backup again after you switch the recovery model back to full. and it is not best practise to have any user created stored procedures in the master database.
To add value to my comment, there are two ways you could do this, either use a outer cursor to cycle through the exising databases or call this script/procedure using sp_msforeachdb.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply