June 19, 2018 at 1:31 pm
Is this good solution for index maintenance on 'always-on' where not all databases are primary , or do we have something better
use master
declare @cmdtbl table (cmd varchar(max))
insert into @cmdtbl
exec sp_msforeachdb 'use [?];if db_id()>4
select ''use [?];if sys.fn_hadr_is_primary_replica(''''?'''')=1 begin begin try alter index ['' + i.name + ''] on ['' + s.name + ''].['' + o.name + ''] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); end try begin catch print ''''opps'''' end catch end'' as cmd
from sys.objects o left outer join sys.schemas s on
o.schema_id= s.schema_id left outer join sys.indexes i on
o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS IPS
on i.object_id=IPS.object_id and i.index_id=ips.index_id where i.name is not null and s.name != ''sys'''
declare @cmd varchar(max)
declare index_cursor cursor for select * from @cmdtbl
open index_cursor
fetch next from index_cursor into @cmd
while @@FETCH_STATUS=0
begin
print @cmd
exec(@cmd)
fetch next from index_cursor into @cmd
end
close index_cursor
deallocate index_cursor
June 19, 2018 at 1:47 pm
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 19, 2018 at 2:35 pm
thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply