| Author |
Topic |
|
CSK
Constraint Violating Yak Guru
489 Posts |
 Posted - 2007-10-21 : 13:59:58
|
| Dear Gurus,I have been encountering a problem for some time, that the size of transaction log (.LDF) has grown to 50GB and the data file size is (.mdf) about 71GB. My concern is about the size of transaction log. Even if I shrink it, the size is reduced to 25GB which is still large enough. Kindly let me know your view about this situation.ThanksKrishna |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-21 : 14:05:12
|
| Sounds like you might need a 50G tr log - not unusual in some systems.No point in shrinkiing if it's just going to grow again.Try finding out why it's growing rather than shrinking it. Once you've cured that you can decide how big it should be.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2007-10-21 : 14:32:33
|
| Thanks Nr, Let Me find out the reasonThanksKrishna |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-21 : 15:11:30
|
| I assume that LDF will be 120% of MDF. I don't worry about it, and above all I do not shrink it until it is above 120%.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-21 : 17:02:14
|
| What's db recovery model? Did you backup log in schedule? |
|
|
|
ratheeshknair
Posting Yak Master
129 Posts |
Posted - 2007-10-22 : 13:53:33
|
| change ur database recovery mode to SIMPLE run the DBCC SHRINKFILE(LOG_FILE_NAME,SIZE).RKNAIR |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-22 : 13:56:18
|
quote:
Originally posted by ratheeshknair
change ur database recovery mode to SIMPLE run the DBCC SHRINKFILE(LOG_FILE_NAME,SIZE).
That is not a solution for this problem. CSK probably is running the defragmentation stuff which is causing the LDF file to bloat. Don't worry about LDF files that are smaller than the MDFs. This is normal if you do any kind of index maintenance. CSK, do not shrink the LDF as this will just happen again and you will have created a performance problem since the file would then need to grow back to the larger size. Add more disk space if space is an issue, otherwise don't touch anything!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2007-10-22 : 15:04:37
|
| Thanks for your valuble suggession tara.Last month only this project has gone to live. I have asked my sysadmin to alot some SAN space.ThanksKrishna |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-22 : 22:08:02
|
| Still need to figure out root cause, it maybe 200gb someday. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 02:43:57
|
I think Tara meant:"Don't worry about LDF files that are smaller larger than the MDFs."We "boost" Tlog backups to every 2 minutes during defrag/update statistics, which helps keep the size of the LDF files under control.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-23 : 11:57:19
|
| No I meant what I said. I'd only investigate this if the LDF file was larger than the MDF. Right now, CSK's is smaller so I wouldn't bother finding the root cause as that would be a waste of time.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 12:26:50
|
| "I'd only investigate this if the LDF file was larger than the MDF"Sorry, I read what you had written the wrong way round. I agree!Had I have written what I was thinking it would have said "Don't worry about LDF files until they are larger than the MDFs."Oh well, old age has set in early this week ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-23 : 12:32:20
|
| That would be what I would do on my systems. It is well known that maintenance-type activities cause the LDF file to swell, so I don't see any need to investigate something that is well known. SQL Server 2005 handles things better, but I still wouldn't worry about it until the LDF file got bigger.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 12:50:04
|
| I'm pleasantly surprised how much difference (i.e. reducing the amount of LDF extension) that TLog backups every 2 minutes made.Not that I'm overly bothered about the size of my LDF files, but if they are 99% used only for maintenance then its bloat that I would prefer trying to control as best as I can.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-23 : 12:57:56
|
| I don't bother with increasing the tlog backup frequency during maintenance routines. We've got a ton of disk space available to us, so it isn't an issue. Having a large LDF file doesn't affect anything except disk space.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 13:40:43
|
"Having a large LDF file doesn't affect anything except disk space."Indeed. Back in 2000-world  it adversely effects us for Restore |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-23 : 22:29:32
|
| Still affect restore in sql2k5. |
|
|
|
mlevier
Starting Member
33 Posts |
Posted - 2007-11-01 : 08:24:51
|
| So what should you do if your recovery mode is simple and when you run DBCC SHRINKFILE(LOG_FILE_NAME,SIZE). I get the following results back:Dbld Field CurrentSize Min.Size UsedPages Est.Pages7 2 13820864 128 13820864 128I have tried everything that I have fould out there to get the transaction log files size down.Any Ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-01 : 22:53:26
|
| Backup log first. |
|
|
|
|
Transaction Log