SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


URGENT: Unusual growth of ReportServerTempDB. -- HELP --


URGENT: Unusual growth of ReportServerTempDB. -- HELP --

Author
Message
dvprao
dvprao
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 151
Recently I had to perform a emergency Repair of ReportServerTempDB database due to SAN issues that turned this to "suspect status".
The log was rebuilt - and all looked normal....

Now I am noticing that the log file is growing continuously and , no free space is available....
At this rate my drive will fill up in few hours ....

Any help is appreciated ....
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19432 Visits: 14900
Run this:

SELECT
D.database_id,
D.name,
D.recovery_model_desc,
D.log_reuse_wait_desc
FROM
sys.databases AS D
WHERE
D.name = 'reportservertempdb';



I'm guessing you'll see FULL as the recovery model and Log Backup as the log reuse wait. This database should be in SIMPLE recovery. To get back to SIMPLE recovery you need to run:


ALTER DATABASE ReportServerTempDB
SET RECOVERY SIMPLE;



You can then shrink the log file back down to a reasonable size using DBCC SHRINKFILE. One of the few times a SHRINK is okay.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
dvprao
dvprao
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 151
Thank you so kindly for the tip.

It seems this has happened to some others as well. It is a mystery ...
The work around suggested is here.

But I ended up dropping the database set (ReportServer , ReportServeTempDB) and re-create them with the RS config wizard and restoring back ( only ReportServer ) and all was back to normal. I had to bring down the SSRS service and take the rap for outage though.

The DB was indeed in simple recovery mode . when and how the REPLICATION thingy git in is a mystery. I am just guessing without any basis that the DBCC CHECKDB ( allow_Repair_with_data_loss) has done something.

Also :
I could not find any guidance on the output message of the DBCC command below:
"...
Warning: The log for database 'ReportServerTempDB' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files. .."
--------------------
What does it mean delete any extra log files ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search