Determining Free Space Per SQL Server Database in SSRS

By:   |   Comments (4)   |   Related: > Reporting Services Development


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

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.

    sharepoint Native Mode
  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.

    sharepoint Native Mode
  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.

    sharepoint integration Mode
  4. Under the Name column, click the link to the data source.

    sharepoint integration Mode
  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.

    sharepoint integration Mode
  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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



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?















get free sql tips
agree to terms