1 Reply Latest reply on Feb 9, 2015 1:20 PM by Slyfin

    ePO 5.1 and SQL 2012 Database Maintiance Tasks.

    bryce.cairns

      What Maintenance Task should I be running?

       

      I have tried running the rebuild Automatically based on fragmentation - Modified.sql, by right clicking on the DB and going run query.

      All I get is a completed, that's it.

       

      My Database size is not changing, which I thought would.

      Any one got any ideas, I am not a SQL guys so please bear with me.

       

      -- http://www.sqlmusings.com
      -- Ensure a USE <databasename> statement has been executed first.
      SET NOCOUNT ON
      -- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online 
      -- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)
      -- Slightly modified by George Younan for ePO usage
      -- =======================================================
      -- || Configuration variables:
      -- || - 10 is an arbitrary decision point at which to
      -- || reorganize indexes.
      -- || - 30 is an arbitrary decision point at which to
      -- || switch from reorganizing, to rebuilding.
      -- || - 0 is the default fill factor. Set this to a
      -- || a value from 1 to 99, if needed.
      -- || change the report only variable to 0 to have the script
      -- || execute its recommendations, change to 1 to report only.
      -- =======================================================
      DECLARE @reorg_frag_thresh   float  SET @reorg_frag_thresh   = 10.0
      DECLARE @rebuild_frag_thresh float  SET @rebuild_frag_thresh = 30.0
      DECLARE @fill_factor         tinyint SET @fill_factor         = 80
      DECLARE @report_only         bit   SET @report_only         = 1
      -- added (DS) : page_count_thresh is used to check how many pages the current table uses
      DECLARE @page_count_thresh  smallint SET @page_count_thresh   = 1000
       
      -- Variables required for processing.
      DECLARE @objectid       int
      DECLARE @indexid        int
      DECLARE @partitioncount bigint
      DECLARE @schemaname     nvarchar(130) 
      DECLARE @objectname     nvarchar(130) 
      DECLARE @indexname      nvarchar(130) 
      DECLARE @partitionnum   bigint
      DECLARE @partitions     bigint
      DECLARE @frag           float
      DECLARE @page_count     int
      DECLARE @command        nvarchar(4000)
      DECLARE @intentions     nvarchar(4000)
      DECLARE @table_var      TABLE(
                                objectid     int,
                                indexid      int,
                                partitionnum int,
                                frag         float,
                page_count   int
                              )
       
      -- Conditionally select tables and indexes from the
      -- sys.dm_db_index_physical_stats function and
      -- convert object and index IDs to names.
      INSERT INTO
          @table_var
      SELECT
          [object_id]                    AS objectid,
          [index_id]                     AS indexid,
          [partition_number]             AS partitionnum,
          [avg_fragmentation_in_percent] AS frag,
       [page_count]       AS page_count
      FROM
          sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
      WHERE
          [avg_fragmentation_in_percent] > @reorg_frag_thresh 
       AND
       page_count > @page_count_thresh
       AND
          index_id > 0
       
       
      -- Declare the cursor for the list of partitions to be processed.
      DECLARE partitions CURSOR FOR
          SELECT * FROM @table_var
       
      -- Open the cursor.
      OPEN partitions
       
      -- Loop through the partitions.
      WHILE (1=1) BEGIN
          FETCH NEXT
              FROM partitions
              INTO @objectid, @indexid, @partitionnum, @frag, @page_count
       
          IF @@FETCH_STATUS < 0 BREAK
       
          SELECT
              @objectname = QUOTENAME(o.[name]),
              @schemaname = QUOTENAME(s.[name])
          FROM
              sys.objects AS o WITH (NOLOCK)
              JOIN sys.schemas as s WITH (NOLOCK)
              ON s.[schema_id] = o.[schema_id]
          WHERE
              o.[object_id] = @objectid
       
          SELECT
              @indexname = QUOTENAME([name])
          FROM
              sys.indexes WITH (NOLOCK)
          WHERE
              [object_id] = @objectid AND
              [index_id] = @indexid
       
          SELECT
              @partitioncount = count (*)
          FROM
              sys.partitions WITH (NOLOCK)
          WHERE
              [object_id] = @objectid AND
              [index_id] = @indexid
       
          -- Build the required statement dynamically based on options and index stats.
          SET @intentions =
              @schemaname + N'.' +
              @objectname + N'.' +
              @indexname + N':' + CHAR(13) + CHAR(10)
          SET @intentions =
              REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
              @intentions
          SET @intentions = @intentions +
              N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
              N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
       
          IF @frag < @rebuild_frag_thresh BEGIN
              SET @intentions = @intentions +
                  N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
              SET @command =
                  N'ALTER INDEX ' + @indexname +
                  N' ON ' + @schemaname + N'.' + @objectname +
                  N' REORGANIZE; ' + 
                  N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + 
                  N' ' + @indexname + ';'
          END
          IF @frag >= @rebuild_frag_thresh BEGIN
              SET @intentions = @intentions +
                  N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
              SET @command =
                  N'ALTER INDEX ' + @indexname +
                  N' ON ' + @schemaname + N'.' +     @objectname +
                  N' REBUILD'
          END
          IF @partitioncount > 1 BEGIN
              SET @intentions = @intentions +
                  N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
              SET @command = @command +
                  N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
          END
          IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
              SET @intentions = @intentions +
                  N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
              SET @command = @command +
                  N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
          END
       
          -- Execute determined operation, or report intentions
          IF @report_only = 0 BEGIN
              SET @intentions = @intentions + N' EXECUTING: ' + @command
              PRINT @intentions     
              EXEC (@command)
          END ELSE BEGIN
              PRINT @intentions
          END
       PRINT @command
      END
      
      -- Close and deallocate the cursor.
      CLOSE partitions
      DEALLOCATE partitions
      
      GO