Nachverfolgen und berichten des Datenbankwachstums

Modified on 22/06/2009 11:32 by Manuel Siebert Categorized as Nicht kategorisiert
((([image| |http://www.cp-austria.at/website/DesktopModules/Wiki/images/Home.gif|MainPage] » [MainPage|Startseite] » [netFramePlattform|netFrame als Plattform Menü] » [TippsundTricks|Tipps und Tricks] » Nachverfolgen und berichten des Datenbankwachstums | {searchbox} [Search.aspx?FullText=1|Erweiterte Suche])))
{BR}
*[#1|Teil 1]
*[#2|Teil 2]
{br}
{br}
[anchor|#1]
==Teil 1==
Als Erstes erstellen Sie eine Tabelle in der Sie die Inhalte der gewählten Daten abspeichern. Wir haben eine Tabelle mit dem Namen "'''DBINFORMATION'''" erstellt.{br}
{br}
Die Spalten beinhalten: Servername, DatabaseName, der logische Dateiname, der physikalische Dateiname, Totale Datenbankgröße, Status, Wiederherstellungsmodus, Datum.{br}
Wir wollen den Wachstum der Daten täglich, wöchentlich oder monatlich nachverfolgen und das Datetime in das Datum umwandeln.{br}{br}
<code sql>Use MSDB
CREATE TABLE DBINFORMATION
( ServerName VARCHAR(100)Not Null,
DatabaseName VARCHAR(100)Not Null,
LogicalFileName sysname Not Null,
PhysicalFileName NVARCHAR(520),
FileSizeMB INT,
Status sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct INT,
Dateandtime varchar(10) not null
)
Alter table dbinfo ADD CONSTRAINT Comb_SNDNDT2 UNIQUE(ServerName, DatabaseName, Dateandtime,LogicalFileName)
Alter table dbinfo ADD CONSTRAINT Pk_SNDNDT2 PRIMARY KEY (ServerName, DatabaseName, Dateandtime,LogicalFileName)</code>{br}{br}
Primary und Unique Key Constraints für die Kombination er Spalten (ServerName, DatabaseName, Dateandtime, LogicalFileName) wurden hinzugefügt. Dies stellt sicher das keine doppelten Einträge in der Tabelle erstellt werden wenn die Query mehrmals am Tag ausgeführt wird.{br}{br}
Als nächstes fragen wir die "'''SYSFILES'''" Tabelle ab um die gewünschten Daten zu bekommen. Im folgenden Skript erstellen wir eine temporäre Variable welche die Ergebnisse der Select Query beinhaltet.{br}{br}
Diese Daten werden dann in die "'''DBINFORMATION'''" Tabelle geschrieben. Als letztes führen Sie die Stored Procedure "'''SP_MSForEachDB'''" gegen Die TEMP Variable aus.{br}{br}
<code sql>DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
Cast (sysfiles.size/128.0 AS int) AS FileSizeMB,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) as Int) AS FreeSpacePct, CONVERT(VARCHAR(10),GETDATE(),111) as dateandtime
FROM dbo.sysfiles'
INSERT INTO DBINFORMATION
 (ServerName,
 DatabaseName,
 FileSizeMB,
 LogicalFileName,
 PhysicalFileName,
 Status,
 RecoveryMode,
 FreeSpaceMB,
 FreeSpacePct,
 dateandtime
 )
EXEC sp_MSForEachDB @command</code>{br}{br}
Zum Schluß führen wir eine Select Anweisung aus um die Daten im gewünschten Ausgabeformat anzuzeigen.{br}{br}
<code sql>select servername,
       databasename,
       sum(filesizemb) as FilesizeMB,
       Status,
       RecoveryMode,
       sum(FreeSpaceMB)as FreeSpaceMB,
       sum(freespacemb)*100/sum(filesizemb) as FreeSpacePct,
       Dateandtime from dbinformation
where filesizemb > 0
group by servername,databasename, Status, RecoveryMode, dateandtime</code>{br}{br}
Nun sind wir mit dem SQL Bereich des Servers fertig. Nun erstellen wir eine Microsoft Excel Abfrage um die Ergebnisse anzuzeigen.{br}
Jedes Mal wenn Sie das Excel öffnen, sollte es automatisch die Daten der "'''DBINFORMATION'''" Tabelle abfragen und anzeigen.{br}{br}
{br}
{TOP}
{br}
{br}
[anchor|#2]
==Teil 2==
Erstellen Sie eine neue Excelmappe und benennen Sie die Spaltennamen in der ersten Zeile des Arbeitsblattes wie auf dem [http://img3.imagebanana.com/img/nosnrwal/3171.jpg|Bild] gezeigt.{br}
Nun öffnen Sie den Visual Basic Editor. Unter Extras -> Makros -> Visual Basic Editor (Alt+F11). Klicken Sie auf das Arbeitsblatt mit dem Sie arbeiten und kopieren Sie den folgenden Text hinein.{br}{br}
(((Public Sub Dataextract(){br}
  ' Create a connection object.{br}
  Dim cnPubs As ADODB.Connection{br}
  Set cnPubs = New ADODB.Connection{br}
  ' Provide the connection string.{br}
  Dim strConn As String{br}
  'Use the SQL Server OLE DB Provider.{br}
  strConn = "PROVIDER=SQLOLEDB;"{br}
  'Connect to the MSDB database on the SQL instance by editing your servername{br}
  strConn = strConn & "DATA SOURCE=(Servername);INITIAL CATALOG=msdb;"{br}
  'Use an integrated login.{br}
  strConn = strConn & " Integrated Security=SSPI;"{br}
  'Now open the connection.{br}
  cnPubs.Open strConn{br}
  ' Create a recordset object.{br}
  Dim rsPubs As ADODB.Recordset{br}
  Set rsPubs = New ADODB.Recordset{br}
  With rsPubs{br}
    ' Assign the Connection object.{br}
    .ActiveConnection = cnPubs{br}
    ' Extract the required records.{br}
   ' The Select Query to display the data{br}
   .Open "select servername,databasename,sum(filesizemb) as FilesizeMB, Status, RecoveryMode, sum(FreeSpaceMB)as FreeSpaceMB, sum(freespacemb)*100/sum(filesizemb) as FreeSpacePct, Dateandtime from dbinfo where filesizemb > 0 group by servername,databasename, Status, RecoveryMode, dateandtime"{br}
   ' Copy the records into cell A2 on Sheet1.{br}
    Sheet1.Range("A2").CopyFromRecordset rsPubs{br}
  {br}
    ' Tidy up{br}
    .Close{br}
  End With{br}
  cnPubs.Close{br}
  Set rsPubs = Nothing{br}
  Set cnPubs = Nothing{br}
End Sub))){br}{br}
Um diese Prozedur jedes Mal auszuführen, wenn Sie die Exceldatei öffnen klicken Sie doppelt auf "'''DieseArbeitsmappe'''" und fügen Sie den folgenden Code ein.{br}{br}
(((Private Sub Workbook_Open(){br}
  Run "sheet1.dataextract"{br}
End Sub))){br}{br}
Nun haben Sie immer die aktuellen Daten wenn Sie die Excel Datei aufrufen.{br}
{br}
{top}
CP Wiki

Navigation¶

Nachverfolgen und berichten des Datenbankwachstums
