Monday, May 05, 2008

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 comments:

Anonymous said...

Did you solved your problem? would be nice to know - we have the same problem. and couldnt find an answer now...

Toby Valentine said...

y have terrible spam on your pages, read at my blog, how to avoid it.
security online

Sun Ivey said...

It is hard to find a golden mean. Search engines, reviews and sales representatives will help you out with that. Just be careful and do not forget to double-check the information. So check out dataroom.