How to shrink the SQL Server Reporting Services log database

Today I did support on a ConfigMgr 2012 environment with local SQL installation, which I prefer! This because the SQL Server Reporting Services (SSRS) log database ReportServer_log.ldf file was claiming almost complete diskspace (30GB). This is because of the Maximum File Size setting which is set to 2TB by default and no Maintenance Plan is active to shrink the file (by design). I will explain how to shrink the file in order to prevent that the logfile stay small.

Before shrinking the file

Start SQL Management Studio for that and expand Databases. Select the ReportServer database and rightclick on it. Choose Properties and Files and change Full recovery to Simple recovery first. Do a rightclick on the ReportServer database again and choose Tasks, Shrink and Files. Under "File type" select Log and click OK.

Have a look at "Available free space"

The ReportServer_log.ldf file should now decreased in size to 1-5MB instead of multiple GB's. In my situation the logfile was shrinken from 30GB to 5MB (after a few minutes). Much better that way, and no need to increase diskspace every time.

Hope you have the same experience as described here!

Reporting Services - No Reports shows up in Console!

When installing Reporting Services for ConfigMgr integration, it's possible that no reports shows up in the ConfigMgr console. This because a domain user (as recommended) is used, and no additional permissions are set on that account. In this blogpost I explain how to install Reporting Services, which account permissions are needed, and how ConfigMgr integration is done.

First Reporting Services on a SQL Server (instance) is needed. This can be installed once per instance. In SCCM 2007 it could be used only in the default instance (which is named MSSQLSERVER). In ConfigMgr 2012 it can be used both on a default or named instance. During installation use a domain user (service) account. Special permissions are set after installation.

Important: Before doing ConfigMgr integration make sure reporting is working okay already. Just open "Reporting Services Configuration Manager" and click on browse at Web Service URL and Report Manager URL. The following results must be seen then:

Result on Web Service URL (1)

Result on Report Manager URL (1)

After that install the Reporting services role in ConfigMgr on the server where the Reporting Services database is installed. On other servers it's not possible to configure the role for reporting. Just use the same (service) account which was used before. Choose verify and make sure all fields are filled-in correctly. Now let's have a look if reports are showing up. Click again on both URL's:

Result on Web Service URL (2)

Result on Report Manager URL (2)

If all is okay, reports are showing up in ConfigMgr console also. When this is not the case additional permissions are needed. Most of time this is needed when Reporting Services is installed on a different server. Just click on the arrow next to ConfigMgr in the Report Manager URL and choose Security. Click "New Role Assignment". Fill in the (service) account used for Reporting Services before. Select all available roles (or less when needed). Just do the same for the domain administrator account also. Select OK and restart the "SQL Server Reporting Services" to enable it.

It will looks like this:

That's it for now. Reporting Services reports should be available in the ConfigMgr console now. They can be used from the URL also; results are the same. Subscriptions can be set on both places.

No reports available in ConfigMgr 2012 console

ConfigMgr 2012 has reporting functionality build-in, based on SQL Reporting Services (SRS). Today I had a issue that reports aren't available in the ConfigMgr console. What's going wrong here?

When looking at SRS on the SQL Server everything was configured fine. In Reporting Services Configuration Manager a Service Account was set (a domain user, which is a best practice), and the Report Manager URL was displayed fine also. All ConfigMgr reports were displayed here!

First a few captures to display the issue:

A Windows domain user account is configured in RSCM

Starting the Report Manager URL shows no errors..

But no reports are displayed in the ConfigMgr console

Searching on the web I found an recommendation to use the Local system account as SRS Service Account. I changed above configuration from Windows domain user account to a Local system account and it's working again. Strange thing that Microsoft recommends to use a Windows domain user account, but it's not working that way.

The following captures displays the solution:

Configure the Service Account to use Local system in RSCM

After change have a look in the ConfigMgr console again

Nice to have 423 items in Reports again. Just use Local system in SRS Service Account from now on, to pass this issue.

Error in ConfigMgr after Reporting Services Point installation

In ConfigMgr the default Reporting Point role is installed normally. Then reports will be generated and processed on the ConfigMgr server. There is however the choice to install a Reporting Services Point role on the SQL Server. Then reports will be generated and processed on the SQL Server. Much faster that way, industry standard formatting and the possibility to export reports to many formats!

Just add the Reporting Services Point role to the SQL Server and follow the steps in SQL "Reporting Services Configuration". Create a ReportServer database and virtual directory there. Then go back to the ConfigMgr server and choose "Copy Reports to Reporting Services". Just follow the wizard and specify the SQL Server with Reporting Services installed (and configured!).

After copying reports is done it's possible that an error message is displayed in the ConfigMgr system status. The Message ID displayed is 7403 : SMS SRS web service is not running on SRS Reporting Point server "<SQL server>". Although Reporting Services is running fine and reports can be started it's not quite nicely.

The solution for this is not that hard, but maybe not easy to find. An SQL Server update is needed to resolve this issue. Now the Cumulative Update (CU) package 4 for SQL Server 2008 R2 fix the error 7403. It can be downloaded here: http://support.microsoft.com/kb/2345451/en-us

It's even better to install Service Pack (SP) 1 to include all Cumulative Updates released before. It can be download here: http://www.microsoft.com/download/en/details.aspx?id=26727 

Mandatory need for Reporting Services?
An Reporting Services Point is needed when Power Management (R3) and/or Forefront Endpoint Protection (FEP) integration is used in ConfigMgr. If that's not the case, you can choose between both Reporting solutions!

ConfigMgr and OpsMgr on the same SQL Server?
When multiple Reporting Services instances are needed (for example: ConfigMgr and OpsMgr on the same server), remember to place ConfigMgr in the "default instance". you need to install ConfigMgr first because it wants the default instance where as OpsMgr can bet set to a different one.  So you should be able to have them both use the same server, but not the same instance.