SQL

Report Server log file growing nonstop

Last friday I was alerted to the log drive filling up on a SQL 2005 server. I looked into it and found that my SQL Report Services log file (ReportServer_Log.LDF) was increasing in size and filling up the drive.
My first thought was to simply shrink the file via SQL Server Management Studio – select the DB, select Tasks, Shrink, Files, and select the appropriate log file. This however failed to do anything and the drive was continuing to fill. So before it got any worse, I capped the log file size to keep the drive from filling up and created another temporary log file on a drive that had more room.
I tried shrinking the file various ways in the UI and via dbcc shrinkfile, all without success. Some googling reminded me that the log files wont truncate until they are backed up, so I try backing up the log file:
backup log reportserver to DISK=’f:test.bak’

That also failed, but it finally gave me an interesting error message:
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

Some more googling and I found http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2899817&SiteID=1 which discussed this error also. For some reason my database thought it was configured for replication even though I am not using replication. In a replication scenario the logs are only truncated after the transactions have been replicated to the second server. So to resolve this we need to make it think replication is working and then turn it off.

1. Following the article I begin by setting the replication to TRUE
sp_replicationdboption ‘ReportServer’,’publish’,true
This however fails because there is no distributor setup
The Distributor has not been installed correctly. Could not enable database for publishing.
2. Configure Distribution on the server
using the wizard I configured distribution, pointing the files to paths on drives with plenty of space
3. Now that replication is setup, set replication to TRUE
sp_replicationdboption ‘ReportServer’,’publish’,true
4. Using sp_repldone, clear the logs
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
5. Now, unpublish the DB
sp_replicationdboption ‘ReportServer’,’publish’,’false’
6. Now that the DB no longer thinks its published, we can backup the log file
backup log reportserver to DISK=’f:test.bak’
7. Finally we can shrink the file
dbcc shrinkfile(‘reportserver_log’,10)
8. Lastly, cleanup the mess you just made by disabling replication and deleting your backup file

Now all thats left is to delete the temporary log file that was created to keep the initial drive from filling.
The normal shrink process doesnt seem to be working and the log files are emptying to allow for their deletion. I ran into this once before and remember that it had to do with how SQL places data in the logical and virtual logs. More googling to find the answer…

3 thoughts on “Report Server log file growing nonstop

Leave a Reply