| Author |
Topic |
|
leoiser
Starting Member
20 Posts |
 Posted - 2007-07-17 : 23:01:29
|
| 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 Studio 9.00.1399.00Microsoft Analysis Services Client Tools 2005.090.1399.00Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 7.0.5730.11Microsoft .NET Framework 2.0.50727.42Operating System 5.2.3790What are the precautions I need to take.Sorry I am not a DBA.Please advice.Thanks in advance |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-17 : 23:05:50
|
| If the db is in full recovery model, you need backup log periodically. You can shrink the file after log backup. |
 |
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-07-17 : 23:09:33
|
quote:
Originally posted by rmiao
If the db is in full recovery model, you need backup log periodically. You can shrink the file after log backup.
I took the backup of ReportServerTempDB,2MB only.Please advice how to shrink log file |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-17 : 23:26:12
|
| Right click the db in ssms and choose tasks -> shrink -> files. |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-07-18 : 13:22:59
|
quote:
Originally posted by rmiao
Right click the db in ssms and choose tasks -> shrink -> files.
I done all the steps still the log file size dont change. step 1 : ALTER DATABASE ReportServerTempDB SET RECOVERY FULLstep2 : DBCC SHRINKFILE (ReportServerTempDB_log,0)DbID Filed currentSize MinimumSize UsedPages EstimatedPages--------------------------------------------------------------------8 2 645992 63 645992 56 I also done like belowRight click ReportServerTempDB --> all Tasks --> shrink -->Files --> log fileStill the size of the file samePlease advice |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-07-18 : 14:08:23
|
| Eliminate the log file completelySometimes 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 isa. Detach the databaseb. Rename the log filec. Attach the database without the log filed. Delete the log file Let’s say, the database name is testDev. In the SQL Server Management Studio, 1. Highlight the database-> Tasks->Detach..-> Click OK 2. Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf, 3. 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 4. After this is done, you can verify the contents of the attached database and then delete the log file.Can I do like above method? Is it have any problem? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-18 : 17:21:24
|
| You should:1. backup log with truncate_only2. set db to simple recovery mode3. shrink log file4. set recovery mode back |
|
|
|
leoiser
Starting Member
20 Posts |
Posted - 2007-07-18 : 22:16:14
|
quote:
Originally posted by rmiao
You should:1. backup log with truncate_only2. set db to simple recovery mode3. shrink log file4. set recovery mode back
hi rmiao, Thanks a lot.. It worked,reduced to 34MB log.I am having a question..If I delete the log file & attach the databse any problem..Thanks for the solution  |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-19 : 10:34:18
|
| I don't do that since:1. deatch db means down time.2. not guaranteed that you can attach db back without log file. |
|
|
|
|
ReportserverTempDB log size 5GB