Defragmentation and update statistics for all online databases

  • Comments posted to this topic are about the item Defragmentation and update statistics for all online databases

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • I am sure that this script does wonderful things, i ran it on a test server and on completion i looked at a blank screen. I'am still wandering if something has happened.

    Hans Loykens

    Middelburg, The Netherlands

  • Another script that: a) doesn't filter out LOB_DATA or ROW_OVERFLOW_DATA allocation units b) runs over all indexes

    Do yourself a favor and download Ola Hallengren's database maintenance solution and just use that: http://ola.hallengren.com/

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Hi This Script will not give any output but will rebuild or reorganize indexes.

    This Script is only for production environment and it will create a job, You have to run the job. For other environments Please run below listed codes(a,b).

    It will update the statistics of these indexes. 🙂

    a.

    /* Query developed by Vimal Lohani For reorganizing and rebuilding indexes */

    /* Query work for all Online Databases in one go */

    /* Work for index name available other wise will say INDEX NAME NOT SET OR NULL */

    /* Work for user define indexes */

    /* Still work if any error occored with indexes */

    USE MASTER

    GO

    SET NOCOUNT ON

    If exists (select * from tempdb.sys.all_objects where name like ''#Databases'')

    begindrop table #Databases end

    SELECT ROW_NUMBER() OVER(ORDER BY name) Seq, name Banco

    INTO #Databases

    FROM sys.databases

    WHERE name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') --Not including system databases

    and name NOT LIKE ''ReportServer%''

    AND compatibility_level > 80 --For Database sql 2005 or higher

    AND state=0 --fOR All online databases

    ORDER BY Banco;

    DECLARE

    @Loop INT = 1,

    @QT INT = (SELECT COUNT(1) FROM #Databases), --max no of databases

    @Banco VARCHAR(50);

    WHILE @Loop <= @QT

    BEGIN

    SET @Banco = (SELECT Banco FROM #Databases WHERE Seq = @Loop);

    EXEC(

    ''USE '' + @Banco + ''; '' +

    ''PRINT ''''For Database : '''' + db_name();

    Declare @dbid int;

    set @dbid=DB_ID();

    SELECT

    ROW_NUMBER() OVER(ORDER BY p.object_id, p.index_id) Seq,

    t.name Tabela, h.name Esquema,

    i.name Indice, p.avg_fragmentation_in_percent Frag

    INTO #Consulta

    FROM

    sys.dm_db_index_physical_stats(@dbid,null,null,null,null) p

    join sys.indexes i on (p.object_id = i.object_id and p.index_id = i.index_id)

    join sys.tables t on (p.object_id = t.object_id)

    join sys.schemas h on (t.schema_id = h.schema_id)

    join sys.objects o on (o.object_id=p.object_id)

    where p.avg_fragmentation_in_percent > 5.0

    and p.index_id >= 0 --all indexes including heap

    and p.page_count >= 10

    AND o.type=''''U''''

    ORDER BY Esquema, Tabela;

    DECLARE

    @Loop INT = 1,

    @Total INT = (SELECT COUNT(1) FROM #Consulta),

    @Comando VARCHAR(500),

    @Updato VARCHAR(500)

    WHILE @Loop <= @Total

    BEGIN

    SELECT @Comando = ''''ALTER INDEX '''' + Indice +

    '''' ON '''' + Esquema + ''''.'''' + Tabela +

    ( CASE WHEN Frag > 45.0 THEN '''' REBUILD'''' ELSE '''' REORGANIZE'''' END)

    FROM #Consulta

    WHERE Seq = @Loop;

    EXEC(@Comando);

    PRINT ''''Executed: '''' + ISNULL(@Comando,''''INDEX NAME NOT SET OR NULL'''');

    SELECT @Updato=''''UPDATE STATISTICS ''''+ Esquema + ''''.'''' +Tabela + '''' ''''+Indice FROM #Consulta

    WHERE Seq = @Loop;

    EXEC(@Updato);

    PRINT ''''UPDATE STATISTICS: ''''+ ISNULL(@Updato,''''INDEX NAME NOT SET OR NULL'''');

    SET @Loop = @Loop + 1;

    END;

    PRINT DB_NAME() + '''' Total No of indexes defragmented : '''' + CONVERT(VARCHAR(5),@Total);

    PRINT ''''------------------------------------------------------------------------------'''';

    DROP TABLE #Consulta;'');

    SET @Loop = @Loop + 1;

    END;

    DROP TABLE #Databases;

    b.

    DECLARE @SQL VARCHAR(1000)

    DECLARE @DB sysname

    DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR

    SELECT [name]

    FROM master..sysdatabases

    WHERE [name] NOT IN (''model'', ''tempdb'')

    ORDER BY [name]

    OPEN curDB

    FETCH NEXT FROM curDB INTO @DB

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @SQL = ''USE ['' + @DB +'']'' + CHAR(13) + ''EXEC sp_updatestats'' + CHAR(13)

    PRINT @SQL

    exec(@sql)

    Select @SQL= ''USE ['' + @DB +'']'' + CHAR(13) + ''alter database ['' + @DB +'']'' +'' set multi_user''+ CHAR(13)

    PRINT @SQL

    exec(@SQL)

    print ''------------------------------------------------''

    FETCH NEXT FROM curDB INTO @DB

    END

    CLOSE curDB

    DEALLOCATE curDB

    CLOSE curDB

    DEALLOCATE curDB

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

  • This is an old post but must be good enough to repost. I'll pass this on to my DBA.

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

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