ReportServerTempdb log file is full

Question

ReportServerTempdb log file is full

Robin35

SSCertifiable

Points: 6471
More actions
December 12, 2016 at 8:39 am

#315804

Hello,
Our ReportServerTmpdb log file is full and our reports are receiving this error. i'm trying to understand, what causes this log file to grow and how can we prevent this ? Database is in simple recovery mode so we cannot take log backup.
Can anyone please help me on this ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

Robin35 (12/12/2016)
Hello,
Our ReportServerTmpdb log file is full and our reports are receiving this error. i'm trying to understand, what causes this log file to grow and how can we prevent this ? Database is in simple recovery mode so we cannot take log backup.
Can anyone please help me on this ?

You can try running DBCC OPENTRAN to see any long running transactions. This can cause the log to grow.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Robin35 SSCertifiable Points: 6471 More actions · Answer 2

joeroshan (12/12/2016)
Robin35 (12/12/2016)
Hello,
Our ReportServerTmpdb log file is full and our reports are receiving this error. i'm trying to understand, what causes this log file to grow and how can we prevent this ? Database is in simple recovery mode so we cannot take log backup.
Can anyone please help me on this ?
You can try running DBCC OPENTRAN to see any long running transactions. This can cause the log to grow.

I did run that, there were no open transactions...i did shrunk the log file. My question is why would log file fill up when a large report is running ? usually this database stores current session data right ?

joeroshan SSChampion Points: 10377 More actions · Answer 3

I am not aware of any known issues with Reportservertempdb. I do not think any changes to it is supported.

However you can use general monitoring counters for memory CPU and disks for any bottlenecks.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

i believe it's suffering from the classic error: recovery mode is FULL recovery, someone took at least one full backup, and no one has ever run a log backup, nor plans on running a log backup.

change the recovery model to simple recovery, and shrink the database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Robin35 (12/12/2016)
My question is why would log file fill up when a large report is running ?

Probably because of an all-in-one query with many joins and a DISTINCT or GROUP by to cover up the fact than accidental many-to-many joins are present. Another name for many-to-many is CROSS JOIN and it can blow a log file out of the water in a couple of heartbeats. Remember that a lot of code write to the temp db for things like hash joins, spools, etc, etc, and, even though it's in the SIMPLE recovery model, it still has to be logged until the implicit transaction completes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)