Größe einer SQL Server Datenbank ermitteln und ausgeben

In Zeiten von wachsender Interaktion zwischen Benutzer und Web-Seiten ist es wichtig, die hinter einer Site liegende Datenbank zu überwachen, sofern man dort Beitragskommentare oder ähnliches ablegt. Schnell wird der angemietete Platz auf dem Datenbank-Server zu klein und im schlimmsten Fall geht der Web-Site Funktionalität verloren, wenn kein Datensatz mehr angelegt werden kann.

Webmaster, denen ein SQL Server von Microsoft zur Verfügung steht, können hierzu auf die systemseitig implementierte Stored Procedure sp_spaceused zurückgreifen, um zu erfahren wie groß eine Tabelle oder die ganze Datenbank inzwischen ist.

Datenbankgröße

Für die Ausgabe der Informationen zur Datenbankgröße genügt im Prinzip der SQL Query Analyzer und die Verbindung zur Datenbank:

Um diese Ausgabe etwas komfortabler über die Oberfläche des Webs zur Verfügung zu stellen, braucht es neben einer kurzen Stored Procedure nur ein wenig ASP-Code:

Stored Procedure

CREATE PROCEDURE procGetDBSize
AS
   SET NOCOUNT ON
   EXEC sp_spaceused

ASP-Code

<%
 'Datenbankverbindung öffnen
 Set objConn = Server.CreateObject("ADODB.Connection")
 objConn.Open "<Verbindungzeichenfolge zum SQL-Server>"

 'Command-Objekt initialisieren
 Set objCmd = Server.CreateObject("ADODB.Command")
 objCmd.ActiveConnection = objConn
 objCmd.CommandType = adCmdStoredProc
 objCmd.CommandText = "procGetDBSize"

 'Recordset-Objekt initialisieren und öffnen
 set rs = Server.CreateObject("ADODB.Recordset")
 rs.Open objCmd,, adOpenForwardOnly, adLockReadOnly

 Set fldName = rs.Fields("database_name")
 Set fldSize = rs.Fields("database_size")
 Set fldUnalloc = rs.Fields("unallocated space")
%>

<table width="100%">
  <tr>
      <td width="200">Datenbankname:</td>
      <td><%response.write(fldName)%></td>
   </tr>
   <tr>
      <td>Aktuelle Datenbankgröße:</td>
      <td><%response.write(fldSize)%></td>
   </tr>
   <tr>
      <td>Nicht zugeordneter Speicher:</td>
      <td><%response.write(fldUnalloc)%></td>
   </tr>
...

Der aufmerksame Leser wird festgestellt haben, daß die Prozedur sp_spaceused, und somit auch unsere Prozedur, nicht nur ein, sondern zwei Resultsets zurückgibt. Um nun das zweite Resultset abzurufen, verwenden wir die Methode NextRecordset() des Recordset-Objekts.

...
<%
 Set rs = rs.NextRecordset()

 Set fldReserved = rs.Fields("reserved")
 Set fldData = rs.Fields("data")
 Set fldIndex = rs.Fields("index_size")
 Set fldUnused = rs.Fields("unused")
%>
  <tr>
     <td>Reservierter Speicher:</td>
     <td><%response.write(fldReserved)%></td>
  </tr>
  <tr>
     <td>Für Daten verwendeter Speicher:</td>
     <td><%response.write(fldData)%></td>
  </tr>
  <tr>
     <td>Für Indizes verwendeter Speicher:</td>
     <td><%response.write(fldIndex)%></td>
  </tr>
  <tr>
     <td>Nicht verwendeter Speicher:</td>
     <td><%response.write(fldUnused)%></td>
  </tr>
</table>

<%
 rs.close
 Set rs = Nothing
 Set objCmd = Nothing
%>

Tabellengrößen

Wenn man die Stored Procedure sp_spaceused mit einem Parameter bestückt, der einen gültigen Namen einer Tabelle darstellt, so erhält man die Größeninformationen über die Tabelle.

Um nun nicht die Namen der Tabellen im ASP-Code fest zu hinterlegen oder andere Konstrukte anzuwenden, um an die Informationen von allen Tabellen der Datenbank zu kommen, schreiben wir eine Stored Procedure, die alle Informationen über temporäre Tabellen zusammenträgt und uns lediglich ein Resultset mit den gewünschten Informationen zurückgibt.

CREATE PROCEDURE procGetTableSizes

AS
   SET NOCOUNT ON

   -- benötigte Variablen deklarieren
   DECLARE @sql VARCHAR(128)
   DECLARE @tname VARCHAR(128)

   -- temporäre Tabelle für Tabellennamen erzeugen...
   CREATE TABLE tmpTables(tName VARCHAR(128))

   -- ... und befüllen
   SELECT @sql = 'INSERT tmpTables '
      + 'SELECT table_name FROM INFORMATION_SCHEMA.TABLES '
      + 'WHERE table_type = ''BASE TABLE'''
   EXEC (@sql)

   -- temporäre Tabelle für Tabelleninformationen erzeugen...
   CREATE TABLE tmpSpaceUsed (
      name VARCHAR(128),
      rows VARCHAR(11),
      reserved VARCHAR(18),
      data VARCHAR(18),
      index_size VARCHAR(18),
      unused VARCHAR(18))

   SELECT @tname = ''

   -- ... über Schleife über Tabellennamen und der System-SP 'sp_spaceused' befüllen
   WHILE EXISTS (SELECT * FROM tmpTables WHERE tName > @tname)
   BEGIN
      SELECT @tname = MIN(tName) FROM tmpTables WHERE tName > @tname
      SELECT @sql = 'INSERT tmpSpaceUsed EXEC sp_spaceused ' + @tname + ''
      EXEC (@sql)
   END

   -- Informationen über Systemtabellen anfügen
   INSERT tmpSpaceUsed EXEC sp_spaceused sysobjects
   INSERT tmpSpaceUsed EXEC sp_spaceused sysindexes
   INSERT tmpSpaceUsed EXEC sp_spaceused syscolumns
   INSERT tmpSpaceUsed EXEC sp_spaceused systypes
   INSERT tmpSpaceUsed EXEC sp_spaceused syscomments
   INSERT tmpSpaceUsed EXEC sp_spaceused sysfiles1
   INSERT tmpSpaceUsed EXEC sp_spaceused syspermissions
   INSERT tmpSpaceUsed EXEC sp_spaceused sysusers
   INSERT tmpSpaceUsed EXEC sp_spaceused sysproperties
   INSERT tmpSpaceUsed EXEC sp_spaceused sysdepends
   INSERT tmpSpaceUsed EXEC sp_spaceused sysreferences
   INSERT tmpSpaceUsed EXEC sp_spaceused sysfulltextcatalogs
   INSERT tmpSpaceUsed EXEC sp_spaceused sysindexkeys
   INSERT tmpSpaceUsed EXEC sp_spaceused sysforeignkeys
   INSERT tmpSpaceUsed EXEC sp_spaceused sysmembers
   INSERT tmpSpaceUsed EXEC sp_spaceused sysprotects
   INSERT tmpSpaceUsed EXEC sp_spaceused sysfulltextnotify
   INSERT tmpSpaceUsed EXEC sp_spaceused sysfiles
   INSERT tmpSpaceUsed EXEC sp_spaceused sysfilegroups

   -- Ausgabe des Resultsets
   SELECT * FROM tmpSpaceUsed

   -- Löschen der temporären Tabellen
   DROP TABLE tmpTables
   DROP TABLE tmpSpaceUsed

Die Implementierung der Prozedur in unseren ASP-Code funktioniert exakt so wie im ersten Teil, nur daß wir hier nicht zwei Resultsets haben und durchlaufen müssen und die HTML-Tabelle etwas anders aufgebaut werden muss:

<table width="100%">
 
<tr>
   <td width="125">Name</td>
   <td align="right">Zeilen</td>
   <td align="right">Reserv. Speicher</td>
   <td align="right">Datenspeicher</td>
   <td align="right">Indexspeicher</td>
   <td align="right">Nicht verw. Speicher</td>
 </tr>

<%
 Set objCmd = Server.CreateObject("ADODB.Command")
 objCmd.ActiveConnection = objConn
 objCmd.CommandType = adCmdStoredProc
 objCmd.CommandText = "procGetTableSizes"

 set rs = Server.CreateObject("ADODB.Recordset")
 rs.Open objCmd,, adOpenForwardOnly, adLockReadOnly

 set fldName = rs.fields("name")
 set fldRows = rs.fields("rows")
 set fldReserved = rs.fields("reserved")
 set fldData = rs.fields("data")
 set fldIndex = rs.fields("index_size")
 set fldUnused = rs.fields("unused")

 do until rs.EOF

%>
  <tr>
     <td><%response.write(fldName)%></td>
     <td align="right"><%response.write(fldRows)%></td>
     <td align="right"><%response.write(fldReserved)%></td>
     <td align="right"><%response.write(fldData)%></td>
     <td align="right"><%response.write(fldIndex)%></td>
     <td align="right"><%response.write(fldUnused)%></td>
  </tr>

<%
   rs.movenext
 loop
 rs.close
 Set rs = nothing
 Set objCmd = Nothing
%>

</table>