Datenbank Übersicht

Das Skript ist ein nützliches Werkzeug zur Überwachung des Speicherplatzes von Datenbanken.:

DECLARE @ServerVersion varchar(100)
SET @ServerVersion = CONVERT(varchar, SERVERPROPERTY('productversion'))
SET @ServerVersion = LEFT(@ServerVersion, CHARINDEX('.', @ServerVersion, 4) - 1)

DECLARE @command nvarchar(2000)  
DECLARE @dbname sysname
DECLARE @sql nvarchar(4000)

-- Temporäre Tabelle für die Ergebnisse
IF OBJECT_ID('tempdb..#FileData', 'U') IS NOT NULL
    DROP TABLE tempdb..#FileData

CREATE TABLE tempdb..#FileData
(
    [CurrentHost]                   varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [ClusterNodes]                  varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [DB]                            varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [FileType]                      varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [Name]                          varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [VolumeOrDrive]                 varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [FileName]                      varchar(250) COLLATE Latin1_General_CI_AS NULL,
    [File Size (MB)]                decimal(15,2) NULL,
    [Space Used In File (MB)]       decimal(15,2) NULL,
    [Available Space In File (MB)]  decimal(15,2) NULL,
    [Drive Free Space (MB)]         decimal(15,2) NULL
)    

-- Vorbereitung des Befehlsgerüsts (ohne USE [?]) – je nach Version
IF CONVERT(float, @ServerVersion) < 10.5  -- SQL 2000, 2005, 2008
BEGIN
    -- Temporäre Tabelle für xp_fixeddrives
    IF OBJECT_ID('tempdb..#xp_fixeddrives', 'U') IS NOT NULL
        DROP TABLE #xp_fixeddrives

    CREATE TABLE #xp_fixeddrives (Drive varchar(250), MBFree int)
    INSERT INTO #xp_fixeddrives EXEC master..xp_fixeddrives

    SET @command = N'
        INSERT INTO #FileData
        (
            [CurrentHost], [ClusterNodes], [DB], [FileType], [Name],
            [VolumeOrDrive], [FileName], [File Size (MB)],
            [Space Used In File (MB)], [Available Space In File (MB)],
            [Drive Free Space (MB)]
        )
        SELECT 
            CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, ''''), '''')) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), DB_NAME()) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), f.Name) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), LEFT(f.FileName, 3)) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), f.FileName) COLLATE Latin1_General_CI_AS,
            CONVERT(decimal(15,2), ROUND(f.Size / 128.000, 2)),
            CONVERT(decimal(15,2), ROUND(FILEPROPERTY(f.Name, ''SpaceUsed'') / 128.000, 2)),
            CONVERT(decimal(15,2), ROUND((f.Size - FILEPROPERTY(f.Name, ''SpaceUsed'')) / 128.000, 2)),
            CONVERT(decimal(15,2), d.MBFree)
        FROM dbo.sysfiles f WITH (NOLOCK)
        INNER JOIN sys.database_files df ON df.file_id = f.fileid 
        LEFT JOIN #xp_fixeddrives d ON LEFT(f.FileName, 1) COLLATE Latin1_General_CI_AS = d.Drive COLLATE Latin1_General_CI_AS
    '
END
ELSE  -- SQL 2008R2+ (sys.dm_os_volume_stats verfügbar)
BEGIN
    SET @command = N'
        INSERT INTO #FileData
        (
            [CurrentHost], [ClusterNodes], [DB], [FileType], [Name],
            [VolumeOrDrive], [FileName], [File Size (MB)],
            [Space Used In File (MB)], [Available Space In File (MB)],
            [Drive Free Space (MB)]
        )
        SELECT 
            CONVERT(varchar(250), SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), ISNULL(STUFF((SELECT '', '' + NodeName FROM fn_virtualservernodes() FOR XML PATH('''')), 1, 1, ''''), '''')) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), DB_NAME(v.database_id)) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), df.type_desc) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), f.name) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), v.volume_mount_point) COLLATE Latin1_General_CI_AS,
            CONVERT(varchar(250), f.[Filename]) COLLATE Latin1_General_CI_AS,
            CONVERT(decimal(15,2), ROUND(f.Size / 128.000, 2)),
            CONVERT(decimal(15,2), ROUND(FILEPROPERTY(f.Name, ''SpaceUsed'') / 128.000, 2)),
            CONVERT(decimal(15,2), ROUND((f.Size - FILEPROPERTY(f.Name, ''SpaceUsed'')) / 128.000, 2)),
            CONVERT(decimal(15,2), v.available_bytes / 1048576.0)
        FROM sys.sysfiles f
        INNER JOIN sys.database_files df ON df.file_id = f.fileid 
        CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.fileid) v
    '
END

-- Produktionssichere Schleife über alle Benutzerdatenbanken (sys.databases)
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT name
    FROM sys.databases
    WHERE database_id > 4   -- Nur Benutzerdatenbanken, ggf. anpassen (z.B. auch > 0 für alle)
      AND state = 0         -- Nur Online-Datenbanken
    ORDER BY name

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- Dynamisches SQL im Kontext der aktuellen Datenbank ausführen
        SET @sql = N'USE ' + QUOTENAME(@dbname) + N'; ' + @command
        EXEC sp_executesql @sql
    END TRY
    BEGIN CATCH
        -- Fehler protokollieren (z.B. PRINT) und mit nächster DB fortfahren
        PRINT N'Fehler in Datenbank ' + QUOTENAME(@dbname) + N': ' + ERROR_MESSAGE()
    END CATCH

    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- Ergebnis anzeigen
SELECT * FROM #FileData

-- Aufräumen
DROP TABLE tempdb..#FileData
IF OBJECT_ID('tempdb..#xp_fixeddrives', 'U') IS NOT NULL
    DROP TABLE #xp_fixeddrives