Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Größe zu allen Objekten ermitteln

16. Januar 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

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 

Diesen Post teilen

Repost 0
Um über die neuesten Artikel informiert zu werden, abonnieren:

Kommentiere diesen Post