- Teil 1
- Teil 2
==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.
Die Spalten beinhalten: Servername, DatabaseName, der logische Dateiname, der physikalische Dateiname, Totale Datenbankgröße, Status, Wiederherstellungsmodus, Datum.
Wir wollen den Wachstum der Daten täglich, wöchentlich oder monatlich nachverfolgen und das Datetime in das Datum umwandeln.
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)
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.
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.
Diese Daten werden dann in die "DBINFORMATION" Tabelle geschrieben. Als letztes führen Sie die Stored Procedure "SP_MSForEachDB" gegen Die TEMP Variable aus.
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
Zum Schluß führen wir eine Select Anweisung aus um die Daten im gewünschten Ausgabeformat anzuzeigen.
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
Nun sind wir mit dem SQL Bereich des Servers fertig. Nun erstellen wir eine Microsoft Excel Abfrage um die Ergebnisse anzuzeigen.
Jedes Mal wenn Sie das Excel öffnen, sollte es automatisch die Daten der "DBINFORMATION" Tabelle abfragen und anzeigen.
Nach oben
==Teil 2==
Erstellen Sie eine neue Excelmappe und benennen Sie die Spaltennamen in der ersten Zeile des Arbeitsblattes wie auf dem Bild gezeigt.
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.
Public Sub Dataextract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the MSDB database on the SQL instance by editing your servername
strConn = strConn & "DATA SOURCE=(Servername);INITIAL CATALOG=msdb;"
'Use an integrated login.
strConn = strConn & " Integrated Security=SSPI;"
'Now open the connection.
cnPubs.Open strConn
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
' The Select Query to display the data
.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"
' Copy the records into cell A2 on Sheet1.
Sheet1.Range("A2").CopyFromRecordset rsPubs
' Tidy up
.Close
End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing
End Sub
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.
Private Sub Workbook_Open()
Run "sheet1.dataextract"
End Sub
Nun haben Sie immer die aktuellen Daten wenn Sie die Excel Datei aufrufen.
Nach oben