locked
SQL Maintenance for Sharepoint 2003 RRS feed

  • Question

  • Hello,

    I have a Sharepoint 2003 farm with SQL 2000, this farm has more than 1.5 million documents, now I'm planning to run a maintenance plan over SQL:

    =====================================================

    •use sps2003p1_site -- your site database
    DBCC SHOWCONTIG (Personalization)
    DBCC SHOWCONTIG (UserInfo)
    DBCC SHOWCONTIG (WebMembers)
    DBCC SHOWCONTIG (Sites)
    DBCC SHOWCONTIG (Webs)
    DBCC SHOWCONTIG (Lists)
    DBCC SHOWCONTIG (WebParts)
    DBCC SHOWCONTIG (Docs)

    ◦UPDATE STATISTICS Personalization
    ◦UPDATE STATISTICS UserInfo
    ◦UPDATE STATISTICS WebMembers
    ◦UPDATE STATISTICS Sites
    ◦UPDATE STATISTICS Webs
    ◦UPDATE STATISTICS Lists
    ◦UPDATE STATISTICS WebParts
    ◦UPDATE STATISTICS Docs

    ◦DBCC DBREINDEX (Personalization,'',0)
    ◦DBCC DBREINDEX (UserInfo,'',0)
    ◦DBCC DBREINDEX (WebMembers,'',0)
    ◦DBCC DBREINDEX (Sites,'',0)
    ◦DBCC DBREINDEX (Webs,'',0)
    ◦DBCC DBREINDEX (Lists,'',0)
    ◦DBCC DBREINDEX (WebParts,'',0)
    ◦DBCC DBREINDEX (Docs,'',0)
     
    •use sps2003p1_serv -- your serv database
    ◦UPDATE STATISTICS srch_gathererlog_1
    ◦UPDATE STATISTICS srch_gathererlog_2
    ◦UPDATE STATISTICS srch_gathererlog_3
    ◦DBCC DBREINDEX (srch_gathererlog_1,'',0)
    ◦DBCC DBREINDEX (srch_gathererlog_2,'',0)
    ◦DBCC DBREINDEX (srch_gathererlog_3,'',0)
    =====================================================
     
    anyone knows if theres some cons in running this??
     
    thanks a lot,
    Mauricio.

    mpv
    Wednesday, December 14, 2011 1:50 PM

All replies

  • Hi Mauricio,

     

    In SharePoint 2007, if we have installed SP2 there should be a timer job to updates SQL Server query optimization statistics for content databases and rebuild database indexes.

     

    Here is the script to do it on SQL 2005, you can compare it with your script:

     

    DECLARE @AVG_FRAG_LMT float
    DECLARE @NUM_PAGE_LMT int
    DECLARE @REBUILD bit
    DECLARE @ONLINE bit
    DECLARE @MAX_MINS int

    SET @AVG_FRAG_LMT = 50
    SET @NUM_PAGE_LMT = 100
    SET @REBUILD = 0
    SET @ONLINE = 1
    SET @MAX_MINS = 240

    DECLARE ix_cur CURSOR READ_ONLY FOR
       SELECT quotename(schema_name(t.schema_id)) + N'.' + quotename(t.name),
              quotename(i.name)
         FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) ips
         JOIN sys.tables t ON t.object_id = ips.object_id
         JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = ips.index_id
        WHERE ips.alloc_unit_type_desc = N'IN_ROW_DATA' AND
              i.type in (1, 2) AND i.is_disabled = 0 AND
              (@REBUILD <> 0 OR i.allow_page_locks <> 0) AND
              (@REBUILD = 0 OR @ONLINE = 0 OR NOT EXISTS (
              SELECT * FROM sys.partition_schemes s
               WHERE s.data_space_id = i.data_space_id) AND
              (i.type = 1 AND NOT EXISTS (
              SELECT * FROM sys.columns c
               WHERE c.object_id = t.object_id AND
                     (c.user_type_id IN (34, 35, 99) OR c.max_length = -1)) OR
              i.type = 2 AND NOT EXISTS (
              SELECT * FROM sys.index_columns ic JOIN sys.columns c
                  ON c.object_id = ic.object_id and c.column_id = ic.column_id
               WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id AND
                     (c.user_type_id IN (34, 35, 99) OR c.max_length = -1))))
        GROUP BY t.schema_id, t.name, i.name
       HAVING AVG(ips.avg_fragmentation_in_percent) > @AVG_FRAG_LMT AND
              SUM(ips.page_count) >= @NUM_PAGE_LMT

    DECLARE @tabname nvarchar(256), @indname sysname, @endtime datetime
    SET @endtime = DATEADD(mi, @MAX_MINS, GETDATE())
    OPEN ix_cur
    FETCH NEXT FROM ix_cur INTO @tabname, @indname

    WHILE @@FETCH_STATUS = 0 AND GETDATE() < @endtime
    BEGIN
       DECLARE @sql nvarchar(max)
       SET @sql = N'ALTER INDEX ' + @indname + N' ON ' + @tabname +
                  CASE WHEN @REBUILD = 0
                       THEN N' REORGANIZE'
                       ELSE N' REBUILD WITH (ONLINE = ' +
                            CASE WHEN @ONLINE = 0 THEN N'OFF)' ELSE N'ON)' END
                  END
       EXEC sp_executesql @sql
      
       SET @sql = N'UPDATE STATISTICS ' + @tabname + N' ' + @indname
       EXEC sp_executesql @sql

       FETCH NEXT FROM ix_cur INTO @tabname, @indname
    END

    CLOSE ix_cur
    DEALLOCATE ix_cur

    For more information, please refer to this:

     

    http://blogs.technet.com/b/patrick_heyde/archive/2010/05/27/advanced-maintenance-for-sharepoint-databases-defrag-update-index.aspx

     

    And this article related to maintain database of SQL 2000:

     

    http://msdn.microsoft.com/en-us/library/aa297936(v=SQL.80).aspx

     

    Hope this helps.

     

    Thanks.


    Pengyu Zhao

    TechNet Community Support

    • Marked as answer by Pengyu Zhao Wednesday, December 21, 2011 6:45 AM
    • Unmarked as answer by MauricioUY Wednesday, December 21, 2011 11:48 AM
    Monday, December 19, 2011 4:43 AM
  • Hello Pengyu,

    I already knows this script, it seems to do the same thing but using ALTER INDEX instead of DBREINDEX.

    I supposed that DBREINDEX shouldn't affect Sharepoint 2003, but I really would like to know what has to say a Sharepoint expert.....

    thanks!

    Mauricio.


    mpv
    Monday, December 19, 2011 6:56 PM