My firm just hired me as their new DBA to manage all the MS SQL Server installations. They are quite new to venture into the world of SQL Server and there is no proper protocol in place that had been setup to manage them previously.

So to cut the story short, I have been asked by my manager to create a custom report to dynamically query the database sizes on each individual SQL instances that are running across the sites. He would use this report and create custom charts on database growth for each server on a monthly basis. The problem is we haven't purchased any monitoring tool yet for SQL Server which can be used to achieve this task. So after trying out a few different ways, I came up with the following TSQL script (I did take help from the posts provided by my fellow SQLServerCentral buddies and also on other SQL discussion Forums). This script along with some VBA coding on Excel can completely automate the task for querying and displaying the information..

PART 1

First, Create a Table on the instance where you want to dump the queried data. I have created a table called DBINFORMATION under the TRACKDBGROWTH database.

The columns include Servername, DatabaseName, The Logical file name, Physical Filename, Total Database size, its Status and Recovery mode and lastly the Date field. We want to track the growth on a daily, weekly or monthly basis and hence am converting the datetime value into just the date.

Create database TRACKDBGROWTH
Use TRACKDBGROWTH
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 DBINFORMATION ADD CONSTRAINT Comb_SNDNDT2 UNIQUE(ServerName, DatabaseName, Dateandtime,LogicalFileName)
Alter table DBINFORMATION ADD CONSTRAINT Pk_SNDNDT2 PRIMARY KEY (ServerName, DatabaseName, Dateandtime,LogicalFileName)

Primary and Unique key constraints for the combination of columns (ServerName, DatabaseName, Dateandtime,LogicalFileName) are added. This is to ensure that duplicate entries are not created in the table if the query runs more than once on the same day. You will understand this better as we look at the next part of the script.

Next we query the SYSFILES table to gather all the required data. In the script below, we declare the Temporary Variable that would store the results of this Select query.

This data is then inserted into the DBINFORMATION Table. Lastly, execute the system Stored Proc SP_MSForEachDBagainst the TEMP Variable.

/* I found the code snippet below on the web from another DB Forum and tweaked it as per my need. So Lets take a moment to appreciate the person who has made this available for us*/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

Now if you recall, I had placed Primary and Unique key constraints on certain combination of columns (ServerName, DatabaseName, Dateandtime,LogicalFileName). This is to ensure that the same column values are not inserted more than once inside the table. If you try to run the INSERT query the second time on the same day, it will fail with a constraint error. Only when the date changes, the data can be inserted into the table which will help us track the DB changes on a daily interval.

Finally we run a SELECT statement to display the data in the required format inside the Query Analyzer output.

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

Ok. We are now done with the SQL Server side of the story. Now lets make Microsoft Excel query and display the results. Every time you open the excel file, it should automatically query the data from the DBINFORMATION table and display it. This data can be used for any comparison or analysis purpose.

PART 2

• Create a new Excel worksheet or multiple worksheets if you have more than one server to grab the data from. Insert a new table and type the column names on the first row of the sheet. A sample display of the worksheet is given below.

• Now, open the Visual Basic editor. Under Project Explorer Window ----> VBA project---->Microsoft Excel Objects, Double click on the sheet you are working with. and copy paste the code below. The code below contains a Subroutine procedure which has a connection string to SQL Server to query and display the data from row 2 of the worksheet.

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 TRACKDBGROWTH database on the SQL instance by editing your servername
  strConn = strConn & "DATA SOURCE=(Servername);INITIAL CATALOG=TRACKDBGROWTH;"
  '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 dbinformation 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

Now to automatically run this task every time the Excel file is opened,Under Project Explorer Window ----> VBA project---->Microsoft Excel Objects, double click THISWORKBOOK and paste the code below.

Private Sub Workbook_Open()
  Run "sheet1.dataextract"
End Sub

That's it, you are good to go. You can create a SQL job to automate this task, where the Insert statement dumps the data into the DBINFORMATION table periodically. Repeat this process on as many servers as you want by creating a new worksheet within the same Excel file for every instance of SQL Server you may be working with. Thanks for reading and Good Luck.