SQL 2005 ReportserverTempDB size 5GB

Question

Post reply

SQL 2005 ReportserverTempDB size 5GB

ebinroy

Mr or Mrs. 500

Points: 548
More actions
July 17, 2007 at 9:28 pm

#117944

Hi all,
SQL 2005 Report server TempDB log file size is 5GB.How can I clear/Delete the log file.I am using win2003 with service pack 1. Here little info about my SQL server.
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework2.0.50727.42
Operating System5.2.3790
What are the precautions I need to take.How can I shrink(pls give the steps) Please advice.Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

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

michaela SSCertifiable Points: 5649 More actions · Answer 1

DO NOT TRY to delete any of your log files. You would need to shrink it/or shrink the entire database.

1. Make sure you have the ReportDBTemp recovery mode switched to full;

2. Take a log backup (probably you would need to take a full backup before the log backup)

3. run DBCC SHRINKFILE (your log file,0)

Wait a bit until your log file here got smaller.

If you need to shrink the entire db the run DBCC SHRINKDATABASE - check BOL

Finally, if the recovery mode for your db was initially simple, then switch it back to simple.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

Before that run dbcc sqlperf(logspace) to check the space used by the log files of each database so that you can shrink accordingly.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ebinroy Mr or Mrs. 500 Points: 548 More actions · Answer 3

I done all the steps still the log file size dont change.

step 1 : ALTER DATABASE ReportServerTempDB SET RECOVERY FULL

step2 : DBCC SHRINKFILE (ReportServerTempDB_log

,0)

DbID Filed currentSize MinimumSize UsedPages EstimatedPages

--------------------------------------------------------------------

8 2 645992 63 645992 56

I also done like below

Right click ReportServerTempDB --> all Tasks --> shrink -->Files --> log file

Still the size of the file same

Please advice

ebinroy Mr or Mrs. 500 Points: 548 More actions · Answer 4

Eliminate the log file completely

Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

Let’s say, the database name is testDev. In the SQL Server Management Studio,

Highlight the database-> Tasks->Detach..-> Click OK
Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
After this is done, you can verify the contents of the attached database and then delete the log file.

Can I use above method? Is it work ? It works for me other databse (not reporting server).Please advice...

Thanks

michaela SSCertifiable Points: 5649 More actions · Answer 5

I think step 2 was to take a log backup and next to shrink it. It works for sure.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

backup log dbname with truncate_only

go

dbcc shrinkfile(logfilename,100)

check if this helps.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

ebinroy Mr or Mrs. 500 Points: 548 More actions · Answer 7

backup log dbname with truncate_only works for me.

Thanks

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

Always you can use this we you want to remove the unused space from the log file and reserve that to OS.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com