Como Defragmentar Bases de Sharepoint 2007 (Server y Services)

Un tema interesante que encontre en Technet, como defragmentar las bases de Moss 2007.

Article ID :943345
Last Review :October 24, 2007
Revision :1.2

INTRODUCTION

This article describes how to defragment the following Microsoft Windows SharePoint Services 3.0 databases and Microsoft Office SharePoint Server 2007 databases:
• Search database
• Profile database
• Content database

MORE INFORMATION

The extent of the Microsoft SQL Server index fragmentation determines whether an online defragmentation or an offline defragmentation of a database occurs. Online defragmentation defragments only the SQL Server leaf pages. The SQL Server locked pages are not defragmented. Offline defragmentation defragments the locked pages and all leaf pages. The following SQL Server script measures the level of fragmentation, and then it performs either offline defragmentation or online defragmentation if it is required.In the following script, the level of fragmentation is defined as the number of blocks that are logically linear and physically nonlinear. If the level of defragmentation is less than 10 percent, defragmentation does not occur. If the level of fragmentation is from 10 percent to 90 percent, online defragmentation occurs. If the level of fragmentation is more than 90 percent, offline defragmentation occurs.Important The following SQL Server stored procedure makes changes to Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases. This script must be run exactly as specified in this article. Changes that you make to SharePoint Server 2007 databases must follow the Support guidelines that are specified in the following Microsoft Knowledge Base article: 841057 (http://support.microsoft.com/kb/841057/) Support for changes to the databases that are used by Office server products and by Windows SharePoint Services Note We recommend that you host the SharePoint Server 2007 databases on a computer that is running SQL Server 2005. We recommend that you monitor the level of fragmentation before and after you run this script. Schedule the script to run daily, weekly, or monthly, as appropriate for your situation.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragIndexes')
BEGIN
DROP Procedure dbo.proc_DefragIndexes
END
GO
-- =============================================
-- This stored procedure checks all indexes in the current
-- database and performs either offline or online defragmentation
-- according to the specified thresholds
-- The stored procedure also updates statistics for indexes in which the last update
-- time is older than the specified threshold
-- Parameters:
-- @onlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform online defragmentation (default 10%)
-- @offlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform offline defragmentation (default 90%)
-- @updateStatsThreshold specifies the number of days since the last statistics update
-- which should trigger updating statistics (default 7 days)
-- =============================================
CREATE PROCEDURE dbo.proc_DefragIndexes
(
@onlineDefragThreshold float = 10.0,
@offlineDefragThreshold float = 90.0,
@updateStatsThreshold int = 7
)

AS
BEGIN
set nocount on
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag float
DECLARE @command varchar(8000)
DECLARE @schemaname sysname
DECLARE @objectname sysname
DECLARE @indexname sysname
declare @AllIndexes table (objectid int, indexid int, fragmentation float)
declare @currentDdbId int
select @currentDdbId = DB_ID()

insert into @AllIndexes
SELECT
object_id, index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes
OPEN indexesToDefrag;
-- Loop through the partitions.
FETCH NEXT
FROM indexesToDefrag
INTO @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid
SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid
IF @frag > @onlineDefragThreshold
BEGIN
IF @frag < @offlineDefragThreshold BEGIN; SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + object_name(@objectid) + ' REORGANIZE' EXEC (@command) END; IF @frag >= @offlineDefragThreshold
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + object_name(@objectid) + ' REBUILD'
EXEC (@command)
END;
PRINT 'Executed ' + @command
END
IF STATS_DATE(@objectid, @indexid) < command =" 'UPDATE" class="KBlink" href="http://support.microsoft.com/kb/932744/">932744 (http://support.microsoft.com/kb/932744/) Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases