Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Importing and reporting physical database file size for a SQL Server database using SSIS and SSRS


By:   |   Last Updated: 2012-08-21   |   Comments (6)   |   Related Tips: More > Database Administration

Problem

One of my SQL servers has a database that grows very rapidly (1 GB per week). I needed an easy way to monitor the growth of this database. I also needed an easy way to monitor all my servers for growth trending. In a previous tip I covered the process I used to gather the data, in this tip I show how to import the data into a database using SSIS and report on it using SSRS.

Solution

In my tip Gather database physical file size using PowerShell I showed how to gather the data using PowerShell. In this tip I will show how I use SQL Server Integration Services (SSIS) to import the data from an Excel spreadsheet into a SQL server database. Then use SQL Server Reporting Services (SSRS) to report on the data. Using SSRS to report on the data I can very quickly see the trends for the physical data files of all the databases on all my SQL servers.

Importing the data from Excel with SSIS

I collect database file size using a PowerShell script as described in a previous tip. As I mentioned in the other tip I chose to put the data in an Excel spreadsheet, this was more for presentation purposes so the format of the spreadsheet is not exactly fit for importing. I manually change the spreadsheet to prepare it for importing. To prepare the spreadsheet, I change the heading for column A and fill in server name in that column then remove the Server row, Database heading row and blank row between each server. This is shown in the images below.

Original Spreadsheet with database file sizes

Modified Spreadsheet to import the data with SSIS

This process takes me about 5 minutes for 20 plus SQL Servers. I know it is possible to have the PowerShell script do this for me and leave that as an exercise for the reader. Once I have the format of the spreadsheet correct I save it as an Excel 2003 spreadsheet (.xls), I did this because some versions of SSIS do not support .xlxs. Now that my spreadsheet is in the proper format I am ready to import the data using SSIS.

My SSIS project is fairly simple with just 4 components to the data flow. The Excel Source, a Data Conversion, Derived Column and an OLEDB Destination as shown in the image below.

SSIS Data Flow Tasks to Import the Data

The Excel Source is the spreadsheet modified in the previous step. I include the Data Conversion component to ensure the data fields are set to the proper data type and length to match the database fields.

SSIS Data Conversion Transformation Editor

I add a single Derived Column for the Create date.

SSIS Derived Column for the CreateDate Column

Then I have the OLEDB Destination. The connection is an OLE DB Connection to the server and database where the data is stored. The database has a single table with the structure shown below.

OLEDB Destination

In the OLEDB Destination component I map columns from the source to the database table as shown.

OLE DB Destination Editor

Now that I have the data in a database I can use SQL Server Reporting Services to generate a report and very quickly see the growth trends for all my SQL Servers. The report I created is fairly simple with columns for database, data file, data file size and used space. I also include a chart for each server and at the end of the report I have a summary chart showing all servers. There are two datasets in the report, one for the weekly data and one for totals.

SSRS Report Datasets

SSRS Report Design for the Database File Sizes

The final report shows the date the data was collected and totals for each server. The data can be expanded to show the values for each database as show in the image below. At the end of each server section is a chart showing all the databases and file size trends.


Final Report in SSRS

It may sound like a long involved process, but from beginning to end it takes less than 10 minutes and I very quickly see how my data files are growing. I also realize there are other ways to accomplish the data import, but this is the method I chose.

Next Steps


Last Updated: 2012-08-21


get scripts

next tip button



About the author
MSSQLTips author Dale Kelly Dale Kelly has been in the computer industry since the late 80s and is currently a Sr. DBA responsible for 26 SQL Servers.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 12, 2012 - 1:19:53 PM - Kent McConnell Back To Top

You can use the SQL 2008 Powershell module to import the data directly into SQL server when your original script gathers it.


Thursday, August 23, 2012 - 8:29:36 AM - Dale Back To Top

Sandeep;

You can find a download link for the SSIS and SSRS in the Next Steps section.


Wednesday, August 22, 2012 - 5:48:47 PM - Sandeep Kataria Back To Top

Dale --- Can you share the code please? it will save developer time at my end. Thanks

 


Wednesday, August 22, 2012 - 8:23:46 AM - Dale Back To Top

SK

I do not currently have anything in place to track disk space. I do have a job that sends me a comprehensive report for each of my servers every morning and disk space is included in that report.


Tuesday, August 21, 2012 - 9:33:54 AM - SK Back To Top

very helpful...do you have something that can track disk space too?


Tuesday, August 21, 2012 - 9:20:29 AM - Jesse Monk Back To Top

We use SSRS to report database file size growth as well.  Our method differes in that it involves a small Admin database created on each monitored instance.  Agent jobs run stored procedures to collect the filesize information in the Admin database and then the Master Admin DB on our SSRS box runs a procedure to collect the data from each of the instances.  This method avoids the intrinsic complications and extra steps required by the ETL that create failure points along the way.  At least we hope it does!  Regardless, as long as the solution is working for your environment it is valid.  Kudos on thinking ahead and proactively using SSRS to montior and manage the databases!

We also set warning thresholds and use SSRS to highlight if database sizes are close to or have reached those thresholds.  This makes it very easy to identify trouble before it starts.  Currently we are working on an addendum to the system to monitor autogrowth activities and provide a similar report to monitor them as well.


Learn more about SQL Server tools