Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

URGENT: Unusual growth of ReportServerTempDB. -- HELP -- Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 3:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 9, 2013 5:59 PM
Points: 83, Visits: 96
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 ....
Post #1486021
Posted Tuesday, August 20, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 11,157, Visits: 12,897
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

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
Post #1486332
Posted Tuesday, August 20, 2013 10:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 9, 2013 5:59 PM
Points: 83, Visits: 96
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 ?


Post #1486362
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse