Determining Free Space Per SQL Server Database in SSRS

By: Rob Fisch   |   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.

This tip Determining Free Space Per Database 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.

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

• Download these two reports and follow the specific instructions for your server type.
  • FreeSpace-WindowsAuthentication.zip
  • FreeSpace-PromptSQLAccount.zip
• Unzip the files to a temporary location.
• Configuring the Reporting Server using either native or integration mode as shown below

Native Mode - follow these steps

1. Open the SQL Server Management Studio (not Visual Studio or BIDS)
2. In the Object Explorer, click Connect and choose Reporting Services.
3. Choose or enter your Reporting Services server name and click the Connect button with the proper credentials.
4. Create a new folder under the Home tree. (Right-click and select New Folder.)
5. Enter a name of your choosing in the Name field and click OK.
   
   
6. Right-click on the new folder and select Import File.
7. Browse for one of the files downloaded by clicking on the button with the elipsis.
8. Select it and click Open.
9. Click OK.
10. Report steps 6-9 above for the other file.
11. Expand the report objects by clicking the '+' sign.
12. Expand the Data Sources object.
    
    
13. Right-click on the data source object under Data Sources and select Properties.
14. Scroll down to the bottom of the screen and select the radio button next to Credentials are not required.
15. Click OK.
16. Repeat steps 11-15 for any other reports.

Sharepoint Integration Mode - follow these steps

1. Create a new folder in a Document Library on your Sharepoint Intergrated Mode Reporting Services server.
2. Upload the unzipped RDL files to the new folder.
3. Click the document dropdown of one of the files and select Manage Data Sources.
   
   
4. Under the Name column, click the link to the data source.
   
   
5. Custom data source should be already selected in the Connection Type.
6. Scroll down to the Credentials section at the bottom of the page. Select the radio button next to Unattended report processing account and click OK. Then click Close.
   
   
7. Repeat steps 3-6 above for any other files.

(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 '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

• Take a look at these other Reporting Services tips

About the author

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