Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tracking and Reporting Database Growth

By Lokesh Gunjugnur,

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.

Total article views: 12626 | Views in the last 30 days: 22
 
Related Articles
FORUM

Dateandtime search

my dateandtime are in one column

FORUM

SQLCLUSTER @@servername = NULL

SQLCLUSTER @@servername = NULL

FORUM

Linked ServerName as variable

Linked ServerName as variable

FORUM

@@servername and serverproperty('servername') gives different values

select serverproperty('servername') and select @@servername shows different names

FORUM

@@servername

Hello, my SQL Server was installed with name 'FIRSTNAME'. After that I change its name into 'SECONDN...

Tags
administration    
excel    
monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones