March 25, 2009 at 1:20 pm
I am noticing that my ReportServerTempDB_log.LDF file is over 25GB in size -- way more than any other database on the server - I mean WAY more.
Is this normal? And is there any way I can shrink the size of it?
I should mention that I'm using SQL Server/Reporting Service 2005, Standard Edition, version #9.00.3077.00.
Thanks,
Cynthia
March 25, 2009 at 1:50 pm
You could back it up and truncate the log and it may be related to developers writing queries that can create performance issues.
So profile the code to see what is making ReportServerTempdb log to grow.
Kind regards,
Gift Peddie
March 25, 2009 at 2:25 pm
When reporting services is installed, the databases created use the default recovery model for that instance. Which is of course the recovery model defined on the model database - and that is generally set to the full recovery model.
So, most likely your database is setup and using the full recovery model. Databases using full recovery model need to have the transaction logs backed up on a regular basis (e.g. every hour).
Since I don't care about restoring this database to a point in time, I reset the database to simple recovery model, which is what I recommend you doing. Once the database recovery model has been modified, you should then be able to run a shrinkfile.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2009 at 3:19 pm
As suggested you can either change the recovery model to Simple and shrink or you can backup the logs and shrink it.
March 25, 2009 at 3:24 pm
Thanks Jeffrey. I have changed it to simple recovery.
It's interesting, because I have never backed up the ReportServerTempDB database. Since it had "Temp" in the name, I assumed it was some sort of temporary thing. Should I be backing it up?
I do back up the ReportServer database -- but not the log files. Since it did not change very often, I decided to forgo the backup of the log files. Since the ReportServer is in full recovery mode, I guess that was not a good idea.
How exactly would I go about shrinking the TempDB log file? (I should add I'm primarily a programmer, not a DBA. However our company is very small and I am assuming both roles ... thus my lack of expertise here.)
Cynthia
March 25, 2009 at 3:37 pm
It's interesting, because I have never backed up the ReportServerTempDB database. Since it had "Temp" in the name, I assumed it was some sort of temporary thing. Should I be backing it up?
You cannot restart SSRS from hardware failure, move from one server to another or upgrade without ReportserverDB and ReportServerTempDB backup. The relational engine of the new instance will not start SSRS without both databases. Check BOL for details.
Kind regards,
Gift Peddie
March 25, 2009 at 3:50 pm
Lookup SHRINKFILE in Books Online, but here is how you would do it:
Use ReportServerTempDB;
Go
-- get the logical name of the file
Select *
From sys.sysfiles;
DBCC SHRINKFILE(logicalfilename, size);
Replace logicalfilename with the logical file name of the file you want to shrink and give it an appropriate size. You can shrink it down to 100MB and let it grow back to whatever size it actually needs to be in your environment.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2009 at 4:43 pm
OK, I think I am set. I changed both Report Server DBs (regular and Temp) to simple recovery, and added a job to do a full backup on the ReportServerTempDB database on a weekly basis. And I also shrank the ReportServerTempDB log file to a reasonable size.
Thanks very much for your help!
Cynthia
March 25, 2009 at 5:39 pm
Cynthia DuBose (3/25/2009)
OK, I think I am set. I changed both Report Server DBs (regular and Temp) to simple recovery, and added a job to do a full backup on the ReportServerTempDB database on a weekly basis. And I also shrank the ReportServerTempDB log file to a reasonable size.Thanks very much for your help!
Cynthia
That's the best way to go
March 25, 2009 at 7:34 pm
Okay - that is good. Glad we could help.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 1, 2010 at 9:52 am
I had same issue with reportservertempdb, but DBCC SHrinkfile did not shrink for me. here is what I ran:
dbcc shrinkfile(ReportServer2TempDB_log,2048),
currently size is 6GB, I tried to shrank it to 2GB. after ran the command, the size is still 6GB. please advise!
many thanks,
maggie
December 1, 2010 at 10:17 am
Maggie,
Is the tempDB log database set to a simple recovery?
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
December 1, 2010 at 11:13 am
yes, the recovery mode is simple. I did full back up, and tried to shrink, did not work. I wonder if it has to do with restart SQL server? thanks
maggie
December 1, 2010 at 11:15 am
Another question. Is the recovery interval set to zero? If it not, then SQL will only shrink down transactions within the available time window. (even when set to simple recovery).
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
December 1, 2010 at 11:26 am
yes, I just check recovery interval and it is 0. one thing i don't understand that only log size is grown crazy, but data size is no chnage basically. I tried to backup log with truncate_only, still no difference. is reportservertempDB is difference than others?
thx, maggie
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply