Realise its a while after the question was asked but in case anyone else can use it - the code here runs the defrag for all active non-system databases. We use it with a SQL Agent job
USE MSDB
declare @isql nvarchar(MAX)
declare @dbname nvarchar(64)
declare c1 cursor for
SELECT d.name as dbName
FROM sys.databases d
where d.name not in ('master','model','msdb','tempdb')
and d.state_desc = 'ONLINE'
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
SET @isql = 'EXEC dbo.usp_AdaptiveIndexDefrag ' +
' @Exec_Print = 1 ' +
' , @printCmds = 1 ' +
' , @updateStats = 1 ' +
' , @updateStatsWhere = 1 ' +
' , @debugMode = 1 ' +
' , @outputResults = 1 ' +
' , @dbScope = ''' + @dbname + '''' +
' , @forceRescan = 1 ' +
' , @maxDopRestriction = 2 ' +
' , @minPageCount = 4 ' +
' , @maxPageCount = NULL ' +
' , @minFragmentation = 1 ' +
' , @rebuildThreshold = 1 ' +
' , @rebuildThreshold_cs = 1 ' +
' , @defragDelay = ''00:00:05'' ' +
' , @defragOrderColumn = ''range_scan_count'' ' +
' , @dealMaxPartition = NULL ' +
' , @disableNCIX = 1 ' +
' , @offlinelocktimeout = 180;'
EXECUTE sp_executesql @isql
fetch next from c1 into @dbname
end
close c1
deallocate c1