Olaf Helper

Die Größe seiner MS SQL Server Datenbanken hat man im Allgemeinen immer im Blick. Nur wie teilt sich das auf meine Objekte und dort auf die Daten, Indizes etc. auf?

Mittels
 

EXEC sp_spaceused @objname = 'Schema.Objectname'

kann man es sich je Objekt ausgeben lassen, nur ist das etwas müßig, wenn man es für mehrere haben möchte.

Also habe ich ein kleines T-SQL Script geschrieben, das per Cursor für alle Objekte die Größe ermitteln lässt, das Ergebnis in eine temporäre Tabelle schreibt und das kann man dann schön auswerten; ist das T-SQL Script dazu; funktioniert ab MS SQL Server 2000:

-- Größe aller Objekte ermitteln

SET NOCOUNT ON

DECLARE @Update varchar(5),

        @SchemaObject varchar(257),

        @Type varchar(2);

-- Soll die Usage aktualisiert werden ? true/false

SET @Update = 'false' 

-- Temporäre Tabelle anlegen

CREATE TABLE ##ObjectSize

    (name nvarchar(128) NOT NULL,

     rows char(11) NULL,

     reserved varchar(18) NULL,

     data varchar(18) NULL,

     index_size varchar(18) NULL,

     unused varchar(18) NULL);

-- Cursor über alle Objekte

DECLARE AllObjs CURSOR LOCAL FOR

    SELECT S.name + '.' + O.Name AS SchemaObject, O.type

    FROM sys.objects AS O

         INNER JOIN

         sys.schemas AS S

             ON O.schema_id = S.schema_id 

    WHERE O.type IN ('U ', 'S ', 'V ', 'SQ', 'IT')

-- Mittels Cursor zu allen Objecten die Größe ermitteln

OPEN AllObjs

FETCH NEXT FROM AllObjs INTO @SchemaObject, @Type

WHILE @@FETCH_STATUS = 0

BEGIN

    IF @Type IN ('V ', 'SQ')

        INSERT INTO ##ObjectSize

        EXEC sp_spaceused @objname = @SchemaObject -- Kein Update

    ELSE

        INSERT INTO ##ObjectSize

        EXEC sp_spaceused @objname = @SchemaObject, @updateusage = @Update

    FETCH NEXT FROM AllObjs INTO @SchemaObject, @Type

END

CLOSE AllObjs

DEALLOCATE AllObjs

-- Die Daten haben wir, aber in unbrauchbaren Textformat

-- => In Nums umwandelt

ALTER TABLE ##ObjectSize

    ADD ireserved bigint NULL,

        idata bigint NULL,

        iindex_size bigint NULL,

        iunused bigint NULL

-- KB bleibt, MB in KB umsetzen, nach bigint wandeln

-- NULL bleibt wie es ist

UPDATE ##ObjectSize

SET ireserved =   CONVERT(bigint, REPLACE(REPLACE(reserved, ' KB', ''), ' MB', '')) 

                  * CASE WHEN RIGHT(reserved, 2) = 'MB' THEN 1024 ELSE 1 END,

    idata =       CONVERT(bigint, REPLACE(REPLACE(data, ' KB', ''), ' MB', ''))

                  * CASE WHEN RIGHT(data, 2) = 'MB' THEN 1024 ELSE 1 END,

    iindex_size = CONVERT(bigint, REPLACE(REPLACE(index_size, ' KB', ''), ' MB', ''))

                  * CASE WHEN RIGHT(index_size, 2) = 'MB' THEN 1024 ELSE 1 END,

    iunused =     CONVERT(bigint, REPLACE(REPLACE(unused, ' KB', ''), ' MB', ''))

                  * CASE WHEN RIGHT(unused, 2) = 'MB' THEN 1024 ELSE 1 END

-- Details auslesen, abst. sortiert nach Gesamtgröße

SELECT *

FROM ##ObjectSize

ORDER BY ISNULL(ireserved, 0) DESC, ISNULL(idata, 0) DESC,

         ISNULL(iindex_size, 0) DESC, ISNULL(iunused, 0) DESC,

         ISNULL(rows, 0) DESC

-- und Gesamtsummen

SELECT SUM(ISNULL(ireserved, 0)) AS ReservedSum,

       SUM(ISNULL(idata, 0)) AS DataSum,

       SUM(ISNULL(iindex_size, 0)) AS IndexSizeSum,

       SUM(ISNULL(iunused, 0)) AS UnusedSum

FROM ##ObjectSize

-- DB Größe

EXEC sp_spaceused

-- Temp abräumen

DROP TABLE ##ObjectSize