none
Reporting Services ProductionRSTempDB_1.ldf is 75 gigs and growing, recovery is 'simple', how to fix

    Question

  • SSRS, database is Enterprise, version 10.50.2425.

    ProductionRSTempDB log file is currently 75+ gigs and growing, ProductionRSTempDB.mdf is 3+ gigs, database is set to 'simple'.

    I dont have a lot of experience with SSRS. Need a resolution and an explanation of what is going on and why the log file for this db is so large and cannot be reduced.

    thanks for any help


    • Edited by tolnep Wednesday, July 17, 2013 4:36 PM
    Wednesday, July 17, 2013 4:31 PM

Answers

All replies

  • I'd check for Open transactions.

    There are a number of reasons why the Tlog could grow like this, long running queries, open transactions, unreplicated transactions, isolation level, etc

    Look for open trans actions: dbcc opentran or use Paul Randals script: http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

    Wednesday, July 17, 2013 7:20 PM
  • I'd check for Open transactions.

    There are a number of reasons why the Tlog could grow like this, long running queries, open transactions, unreplicated transactions, isolation level, etc

    Look for open trans actions: dbcc opentran or use Paul Randals script: http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

    well, yes ive done something like that. realize that what it shows me is info about the meta data dbs for SSRS, which i dont know much about. Its not like i can find a user's query or session and go from there. What i'm seeing are parts of the SSRS system holding locks and perhaps (probably?) causing the issue but why and how to resolve and what the problem(s) might be with the SQL Server Reporting Services is the information i am looking for.

    if this was a application in our environment i would have already dealt with it.

    Wednesday, July 17, 2013 8:43 PM
  • Hi,

    By default, the maximum size of the log file of ReportServerTempDB is set to 2TB. If the size of the ReportserverTempDB_Log.LDF has not been increasing rapidly recently, its size may increase normally. So, I suggest that you set the maximum size of the ReportserverTempDB Log file from the SSMS. Besides, since the database is already set to Simple recovery mode, you can also shrink the database log file using the DBCC command:

    Use ReportServerTempDB;
    Go
    -- get the logical name of the file
    Select *
      From sys.sysfiles;
    DBCC SHRINKFILE(ProductionRSTempDB_Log, size);

    References:

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Thursday, July 18, 2013 9:28 AM
    Moderator