SQL Server Express Edition- Datenbankwartung

USE [master]
GO
 
/****** Object:  StoredProcedure [dbo].[CheckDatabaseIntegrity]    Script Date: 04/08/2013 15:41:28 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[CheckDatabaseIntegrity]
(
@DatabaseName sysname = NULL
)
AS
 
IF @DatabaseName IS NULL
BEGIN
EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'
END
ELSE BEGIN
EXEC ('DBCC CHECKDB('''+@DatabaseName+''')')
END
GO

USE [master]
GO
 
/****** Object:  StoredProcedure [dbo].[PerformIndexMaintenance]    Script Date: 04/08/2013 15:50:22 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[PerformIndexMaintenance]
(
  @DatabaseName sysname = null
)
AS
 
IF @DatabaseName IS NULL
BEGIN
  EXEC sp_msforeachdb N'EXEC sp_DefragIndexes ''[?]'''
END
ELSE BEGIN
  SET @DatabaseName = '['+REPLACE(REPLACE(@DatabaseName,'[', ''),']','')+']'
  EXEC sp_DefragIndexes @DatabaseName
END
 
GO

USE [master]
GO
 
/****** Object:  StoredProcedure [dbo].[ShrinkDatabase]    Script Date: 04/08/2013 15:50:37 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[ShrinkDatabase]
(
  @DatabaseName sysname = null,
  @FreeSpace INT = 0
)
AS
 
IF @DatabaseName IS NULL
BEGIN
  EXEC sp_msforeachdb 'DBCC SHRINKDATABASE(''?'', 0)'
END
ELSE BEGIN
  EXEC ('DBCC SHRINKDATABASE('''+@DatabaseName+''', 0)')
END
 
GO

USE [master]
GO
 
/****** Object:  StoredProcedure [dbo].[sp_DefragIndexes]    Script Date: 04/08/2013 15:50:55 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
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 30%).
    -- @updateStatsThreshold specifies the number of days since the last statistics update
    -- which should trigger updating statistics (default 7 days).
    -- =============================================
    CREATE PROCEDURE [dbo].[sp_DefragIndexes]
    (
    @databaseName sysname = NULL,
    @onlineDefragThreshold FLOAT = 10.0,
    @offlineDefragThreshold FLOAT = 30.0,
    @updateStatsThreshold INT = 7
    )
 
    AS
    BEGIN
 
    IF @databasename IS NULL
    BEGIN
    RETURN;
    END
 
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'USE '+ @databasename +'
 
    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) < DATEADD(dd, -@updateStatsThreshold, getdate())
    BEGIN
    SELECT @command = ''UPDATE STATISTICS '' + @schemaname + ''.'' + object_name(@objectid) +
    '' '' + @indexname +'' WITH RESAMPLE''
    EXEC (@command)
 
    PRINT ''Executed '' + @command
    END
 
    FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
 
    END
 
    CLOSE indexesToDefrag;
    DEALLOCATE indexesToDefrag;'
 
    DECLARE @Params NVARCHAR(MAX)
    SET @Params = N'
    @onlineDefragThreshold float,
    @offlineDefragThreshold float,
    @updateStatsThreshold int'
 
    EXECUTE SP_EXECUTESQL @SQL,
    @Params,
    @onlineDefragThreshold=@onlineDefragThreshold,
    @offlineDefragThreshold=@offlineDefragThreshold,
    @updateStatsThreshold=@updateStatsThreshold;
    END
 
 
GO