Determining Free Space Per SQL Server Database in SSRS


By:   |   Updated: 2007-11-21   |   Comments (4)   |   Related: > Reporting Services Custom Report


Problem

Wouldn't it be great if some of the great DBA management queries you have found on MSSQLTips were available in a Reporting Services portal so that you could run them from anywhere? Imagine how convenient that would be. You could even share them with other DBAs, System Administrators and/or Developers within an organization as deemed appropriate.  In this tip we will take a look at one of the management queries that is found on MSSQLTips.com and transformed into a pre-canned Reporting Services report.

Solution

The solution is to have many of the great scripts and queries found on MSSQLTips retrofitted as Reporting Services reports. This article hosts the first of what I hope to be a long series of companion downloadable reports to go along with the great blogs, articles and tips offered here are MSSQLTips. I will be working on converting as many as time permits.

On 10/11/2007, MSSQLTips published a tip on "Determining Free Space Per Database". The tip offers a SELECT statement that can be used to monitor database growth. I have converted this statement into a (set of) Report Services report(s). One report uses Windows Authentication. The other uses SQL Server authentication, which prompts you for those credentials. Both reports prompt for servername (in the event you want to rerun the report against another server).

(Note: These reports were created with the SQL Server 2005 client tools at SP2. If you have a pre-SP2 system and these files do not work, contact me and I'll try to help.)

Running the reports from the Desktop
If you have the SQL Server 2005 SP2 client tools loaded on your desktop you do not even have to deploy them. You can download the complete Report Designer solution and run it locally.

Download the solution here

Unzip the solution to a folder and open the FreeSpace.sln file from Visio Studio. From there just run the reports as desired.

Configuring and running the reports from a Reporting Services server
(Note: These reports do NOT use a shared data source. The data source is custom and dynamic and is embedded in the dataset.)
  • Running the reports
    • The Windows Authentication report - Unless you have Kerberos delegation configured, you will run into the the 'double hop' issue when running the reports remotely. You should be logged into the Report Server (via RDP or VNC or some other remote method) and use the Report Server's web browser. However, this situation is not appropriate for everyone, especially if you are sharing the reports with other staff that might not have access to the system.
    • The PromptSQLAccount report - Use this directly from any workstation, however you will need to know the credentials of a SQL user account with access to the Master database.
Once run, these reports can easily be exported to spreadsheets or PDFs, in the event you need to share information with others easily.

Next Steps

 



Last Updated: 2007-11-21


get scripts

next tip button



About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips
Related Resources


Comments For This Article




Wednesday, December 17, 2014 - 6:38:13 PM - Patel Back To Top (35651)

=Dateadd("D",-1,DateAdd("M",6, Parameters!FINSTARTDATE.Value))

Here my parameter is 01/04/2012 and i want date 30/09/2012 but it gives 07/03/2012

Tuesday, January 22, 2008 - 7:27:10 AM - jeaux Back To Top (234)

Thanks Rich for your response.  I actually tried the first example, but really wanted the results close to each other. I am checking growth over the last day and a few other categories as well and wanted to just isolate any problem servers.  I think I will end up going with an automated SSIS or C# program to make the connections as you mentioned.

 Thanks again for your response,

Tuesday, January 22, 2008 - 6:26:26 AM - rfisch Back To Top (233)

Hello Jeaux,

Two options come to mind. A simple solution would be to add multiple datasources, one for each server, and have a separate table for each server in a single report. To get a little more elegant, you could write an SSIS package that sequencially cycles through saving the results for each server into a single working table, where you could report on them centrally with a single datasource.

Rob Fisch

Monday, January 21, 2008 - 7:40:21 AM - jeaux Back To Top (231)

I have been working on a similar type report.  I would love to be be able to combine the data from several servers togethers.  Any ideas?


download





Recommended Reading

Execute a SQL Server Reporting Services report from Integration Services Package














get free sql tips
agree to terms