Search This Blog

Friday, August 28, 2015

SQL Server Reporting Services log database (ReportServer_log.ldf)

I know this is old news, but I have seen the problem on various sites so now it’s time to write a short post about the issue.

You may find that the SQL Server Reporting Services (SSRS) log database ReportServer_log.ldf file is using almost all diskspace. The reason for this is that the Maximum File Size setting
is set to 2TB by default.

By design no Maintenance Plan will shrink the file.

So do you self a favor when installing SCCM and Reporting:

Start SQL Server Management Studio

image

Connect to you SQL Server:

image

Expand Databases. Select the ReportServer database and right click on it. Select Tasks  - ShrinkFiles.

image

Change File type to Log and press OK:

image

Before shrinking here is the size of my ReportServer_Log.ldf

image

And after shrinking:

image

Now Select Properties on the same Database:

image

Go to Options and change Recovery model from Full to Simple:

image

Go to Files and click this icon image  in the Autogrowth / Maxsize column:

image

Now change the Maximum File Size from the default 2.097.152 (2 TB) to something more suitable for your disk size. Here I will change it to  50000 (50 GB)

image

If you change the size settings before shrinking you might see this error

MODIFY FILE failed. Size is greater than MAXSIZE.

So do the shrink first:

image

If you work with custom Reports it would also be a very good idea to create a maintenance plan where you backup and shrink the database regularly.

No comments:

Post a Comment