Plan de Mantenimiento de las bases de datos para Granja de SharePoint

Aunque solo hay referencia de un Plan de mantenimiento para SharePoint 2010, SharePoint 2016 aún se corre sobre SQL Server y por tanto validar las bases de datos, reindexarlas y hacer un shrink de vez e cuando es algo que puede ayudar a mejorar el rendimiento y la salud de las bases de datos de SharePoint.

Básicamente el mantenimiento de SQL Server para SharePoint cubre los siguientes aspectos:

1. Comprobación de la integridad de la base de datos
2. Shrink de base de datos (ocasional cuando sea muy necesario)
3. Desfragmentación de los índices mediante su reorganización o regeneración

Pero en vez de utilizar el asistente de SQL Server Management Studio les detallare los scripts 

Listado de bases de datos

select * from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); 

Con este query se puede generar un listado de todas las bases de datos que con excel nos puede servir para generar los comandos  t-sql más facilmente.


Comprobación de la integridad de la base de datos


DBCC CHECKDB ([SharePoint_Config]) WITH PHYSICAL_ONLY 

* Limita la comprobación a la integridad de la estructura física de los encabezados de página y registro y la coherencia de la asignación de la base de datos

https://msdn.microsoft.com/en-us/library/ms176064.aspx

Shrink de base de datos (ocasional cuando sea muy necesario)

https://technet.microsoft.com/es-es/library/cc262731(v=office.14).aspx

Esto aunque no es muy recomendado se hace necesario cuando necesitamos disponer de espacio libre y hay mucho espacio sin ocupar especialmente cuando se ha depurado contenido en el portal de gran volumen.  El Comando es el siguiente:


DBCC SHRINKDATABASE  ([SharePoint_Config],TRUNCATEONLY)

https://msdn.microsoft.com/en-us/library/ms190488.aspx

Desfragmentación de los índices mediante su reorganización o regeneración

Este script fue tomado del sitio abajo indicado funciona muy bien y toma como necesario la reindexación si el porcentaje de desfragmentación es mayor al 30% y si tiene más de 1000 páginas la tabla analizada de la base de datos.

-- http://www.sqlmusings.com

-- Ensure a USE statement has been executed first.

use [WSS_Content_GUID]

go

SET NOCOUNT ON

-- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online

-- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)

-- =======================================================

-- || 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.

-- =======================================================

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

--===========================================

--[dbo].[AllDocStreams].[AllDocStreams_CI]:

-- FRAGMENTATION: 19.7798%

-- PAGE COUNT: 557169

-- OPERATION: REORGANIZE

--ALTER INDEX [AllDocStreams_CI] ON [dbo].[AllDocStreams] REORGANIZE;  UPDATE STATISTICS [dbo].[AllDocStreams] [AllDocStreams_CI];

--=============================================

--[dbo].[AllDocVersions].[AllDocVersions_PK]:

-- FRAGMENTATION: 12.833%

-- PAGE COUNT: 21959

-- OPERATION: REORGANIZE

--ALTER INDEX [AllDocVersions_PK] ON [dbo].[AllDocVersions] REORGANIZE;  UPDATE STATISTICS [dbo].[AllDocVersions] [AllDocVersions_PK];

--===================================

--[dbo].[AllLinks].[Links_Forward]:

-- FRAGMENTATION: 26.2066%

-- PAGE COUNT: 48793

-- OPERATION: REORGANIZE

--ALTER INDEX [Links_Forward] ON [dbo].[AllLinks] REORGANIZE;  UPDATE STATISTICS [dbo].[AllLinks] [Links_Forward];

--====================================

--[dbo].[AllLinks].[Links_Backward]:

-- FRAGMENTATION: 25.3887%

-- PAGE COUNT: 44059

-- OPERATION: REORGANIZE

--ALTER INDEX [Links_Backward] ON [dbo].[AllLinks] REORGANIZE;  UPDATE STATISTICS [dbo].[AllLinks] [Links_Backward];

--=======================================

--[dbo].[EventCache].[EventCache_Time]:

-- FRAGMENTATION: 25.687%

-- PAGE COUNT: 1674

-- OPERATION: REORGANIZE

--ALTER INDEX [EventCache_Time] ON [dbo].[EventCache] REORGANIZE;  UPDATE STATISTICS [dbo].[EventCache] [EventCache_Time];

--=========================================

--[dbo].[EventCache].[EventCache_ListId]:

-- FRAGMENTATION: 22.176%

-- PAGE COUNT: 3603

-- OPERATION: REORGANIZE

--ALTER INDEX [EventCache_ListId] ON [dbo].[EventCache] REORGANIZE;  UPDATE STATISTICS [dbo].[EventCache] [EventCache_ListId];

--=================================

--[dbo].[EventLog].[EventLog_Id]:

-- FRAGMENTATION: 32.5723%

-- PAGE COUNT: 16207

-- OPERATION: REBUILD

-- FILL FACTOR: 80

--ALTER INDEX [EventLog_Id] ON [dbo].[EventLog] REBUILD WITH (FILLFACTOR = 80)

--=============================================

--[dbo].[AllUserData].[AllUserData_ParentId]:

-- FRAGMENTATION: 13.7654%

-- PAGE COUNT: 1006816

-- OPERATION: REORGANIZE

--ALTER INDEX [AllUserData_ParentId] ON [dbo].[AllUserData] REORGANIZE;  UPDATE STATISTICS [dbo].[AllUserData] [AllUserData_ParentId];

--=================================

--[dbo].[Workflow].[Workflow_CI]:

-- FRAGMENTATION: 15.8641%

-- PAGE COUNT: 3385

-- OPERATION: REORGANIZE

--ALTER INDEX [Workflow_CI] ON [dbo].[Workflow] REORGANIZE;  UPDATE STATISTICS [dbo].[Workflow] [Workflow_CI];

--=======================================

--[dbo].[AuditData].[AuditData_OnItem]:

-- FRAGMENTATION: 12.085%

-- PAGE COUNT: 243318

-- OPERATION: REORGANIZE

--ALTER INDEX [AuditData_OnItem] ON [dbo].[AuditData] REORGANIZE;  UPDATE STATISTICS [dbo].[AuditData] [AuditData_OnItem];

Bueno eso es todo por ahora, con esto terminamos la serie que tiene que ver con bases de datos y la granja de SharePoint en sus versiones 2010,2013 y 2016.

SharePoint4fun!,

Juan Manuel Herrera Ocheita