Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-10-07 : 01:53:23
|
| Dear All,I have a production database with 0.5 GB with less transaction/hour.we are taking the transaction log backup for every 1 Hour.The database is in Full recover model with restricted growth to 2,097,152 MB But my transaction log is 9.5 GB how we can reduce the transaction log in production server.I heard shrinking and truncating log is not good option.Please suggest.Thanks,Gangadhar |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-07 : 02:19:36
|
| Shrinking ONCE is fine, if the reason the file is big is because of a one-off event (backups didn't run for a while, someone deleted a huge number of records, that type of thing).If you shrink it and then it grows back again within the next week or two then that's what is bad - the repeated Shrink / Grow will fragment the system.How big is your largest Log Backup in the last week or two? If that is under 50% of the LDF filesize then my guess would be that you could safely shrink itIf the LDF file is not growing, and you have plenty of disk space, you could just leave it alone - its not hurting anything. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-07 : 04:20:06
|
| Something must have caused that to grow. Before trying to shrink, see if you can ID why it grew, otherwise you'll be back there later.Check the sys.databases, the log_reuse_wait_desc for this databases--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Tran log is full