SQL Server Express Edition- Datenbankwartung
Die von Microsoft kostenlos angebotene SQL Server Express Edition bietet zwar ausreichend Leistung wenn es um die Entwicklung und Bereitstellung kleiner Datenbanken geht- leider fehlen hier aber Funktionen, die der Wartung der Datenbanken dienen.Nach langer Suche bin ich auf die Seite http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressMaintenance gestoßen. Die dort veröffentlichten Stored Procedures finden sich weiter unten.Als Wrapper für den zeitgesteuerten Aufruf der einzelnen Prozeduren sind 4 Batch- Dateien entstanden, die über einen geplanten Task ausgeführt werden können.
StoredProcedure CheckDatabaseIntegrity
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
Stored Procedure PerformIndexMaintenance
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
Stored Procedure ShrinkDatabase
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
Stored Procedure sp_DefragIndexes
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
Batchdateien für den Aufruf
Die folgenden Batchdateien können für den turnusmässigen Aufruf durch einen geplanten Task verwendet werden.Das erste Script dient hierbei als Wrapper für den Aufruf der drei weiteren Dateien. Alle Dateien erzeugen Logfiles. Der Pfad muss gegebenenfalls noch angepasst werden. Eine Aufräumroutine für alte Logs ist im Warpper-Script ebenfalls enthalten.performdbmaintenance.bat
@echo off
CALL C:\Admin\checkdbintegrity.bat
CALL C:\Admin\shrinkdatabase.bat
CALL C:\Admin\indexmaintenance.bat
forfiles -p "C:\Admin\logs" -s -m *.log /D -14 /C "cmd /c del @path"
checkdbintegrity.bat
@echo off
set CURDAT=%date:~6,4%%date:~3,2%%date:~0,2%
set LOGFILE=C:\Admin\logs\%CURDAT%-dbintegrity.log
echo %CURDAT% >> %LOGFILE%
sqlcmd -S .\SQLEXPRESS -Q "EXEC CheckDatabaseIntegrity" >> %LOGFILE% 2>&1
indexmaintenance.bat
@echo off
set CURDAT=%date:~6,4%%date:~3,2%%date:~0,2%
set LOGFILE=C:\Admin\logs\%CURDAT%-indexmaintenance.log
echo %CURDAT% >> %LOGFILE%
echo Performing IndexMaintenance for SMCDB >> %LOGFILE%
sqlcmd -S .\SQLEXPRESS -Q "EXEC PerformIndexMaintenance @DatabaseName='SMCDB'" >> %LOGFILE% 2>&1
shrinkdatabase.bat
@echo off
set CURDAT=%date:~6,4%%date:~3,2%%date:~0,2%
set LOGFILE=C:\Admin\logs\%CURDAT%-shrinkdb.log
echo %CURDAT% >> %LOGFILE%
sqlcmd -S .\SQLEXPRESS -Q "EXEC ShrinkDatabase" >> %LOGFILE% 2>&1