| Author |
Topic |
|
CSK
Constraint Violating Yak Guru
489 Posts |
 Posted - 2008-04-10 : 14:50:46
|
| Gurus,Everyweek I am truncating the logfiles. I want to automate the truncate logfiles on every sunday. Anyone can help me to automate this process..Thanks-- Krishna |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 14:52:54
|
| Why are you truncating log file?Any Specific reasons. Explain clearly |
 |
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2008-04-10 : 14:58:18
|
| Sodeep are you kidding..? Dont you know the exact reason of truncating the log file..? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-10 : 15:00:53
|
| CSK,You should not be doing this! What is your database recovery model set to?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2008-04-10 : 15:01:35
|
| FULL recovery model. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 15:02:25
|
| Did I say that I didn't know the reason? I am asking becoz truncatin g log files will break your log backup LSN. Did you get it???? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-10 : 15:02:40
|
| There is no point to using FULL recovery model if you are going to truncate the log. You lost the ability to restore to a point in time by doing this as you have broken the transaction log chain. Stop doing it immediately if your data is critical! Just start backing up the transaction log more frequently. We backup ours every 15 minutes. This helps manage the LDF file size.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 15:03:35
|
| you need to understand between Truncating and shrinking Log files in BOL.Then you need to post. |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2008-04-10 : 15:07:51
|
| Tara/Sodeep,Thanks for your valuable suggessions and time.But the thing is DB size is 700 MB , But the log file size almost 3 GB. Every week this is increasing by 1 GB.I am scaring about the growing of log file. So what can i do..?Thanks-- Krishna |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 15:09:50
|
| How did you set growth rate for Log files in Database properties-files? |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2008-04-10 : 15:11:45
|
| File Growth in 10% and Maximum file size is Restricted file growth by 2,097,152 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 15:14:27
|
| Do you have high logged operation like:1)Bulk- insert2) Rebuilding index (scheduled weekly)3) Massive delete.These will increase your log files quickly. As Tara said:Take log backup and shrink it with DBCC shrinkfileFor long term control:1)Take transaction log backup every 15-30 minutes . |
|
|
|
CSK
Constraint Violating Yak Guru
489 Posts |
Posted - 2008-04-10 : 15:16:55
|
| Yeah We have the process of heavy Bulk insert and Massive delete too. Let be take a log backup and will shrink DB.Thanks for sharing the information.Thanks-- Krishna |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-10 : 15:19:08
|
| Shrink log file |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-10 : 15:23:00
|
| You should not shrink the log file either. You are just creating a performance problem as the next time your bulk insert and massive delete processes run, it will need to expand the file. You should only shrink the file if you no longer need that space in the very near future. Just leave it be since it needs it!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-10 : 22:52:52
|
| csk, how often do you backup log? Didn't see you mention that. |
|
|
|
|
Automatic logfile truncation