| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2005-09-19 : 11:16:18
|
| During the day, from 6am to 8pm I have t-logs every hour. After business hours, I get a Full backupThen run the Optimization and Integrity maintenance JobsAnd at last, I get another full backupBut the first T-Log at 6am next day is too big. Why? I thought a full backup truncates the T-Log. Canada DBA |
|
|
chen149
Starting Member
19 Posts |
Posted - 2005-09-19 : 11:36:15
|
| You have to backup the log file itself |
 |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-09-19 : 12:08:13
|
| Hi Farhadr,You absolutely right. I doubt there may be some jobs which are used to defrag the database and causes increase in T-Log?RegardsSachin |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-09-19 : 12:45:00
|
Would you please explain more. I backup the log file every hour up to 8pm and next backup is 6am. Do you mean that we have a full backup for logs as well?quote:
Originally posted by chen149
You have to backup the log file itself
Canada DBA |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-19 : 12:52:46
|
| full backup doesn't truncate the tlogs.the reason your first tlog backup of the morning is large is because of your optimization job. The optimization job is rebuilding all indexes (including cluster indexes) and all of these operations are logged.-ec |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-09-19 : 12:53:05
|
Hi Sachin,Yes, there are optimization jobs on my DB. I think the fact is that the Full Backup doesn't truncate the TLog and I need to shrink the TLog or DB, AFTER I got the full backup.Thanks, quote:
Originally posted by sachinsamuel
Hi Farhadr,You absolutely right. I doubt there may be some jobs which are used to defrag the database and causes increase in T-Log?RegardsSachin
Canada DBA |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-19 : 12:57:53
|
quote:
Originally posted by farhadr
Hi Sachin,Yes, there are optimization jobs on my DB. I think the fact is that the Full Backup doesn't truncate the TLog and I need to shrink the TLog or DB, AFTER I got the full backup.Thanks, quote:
Originally posted by sachinsamuel
Hi Farhadr,You absolutely right. I doubt there may be some jobs which are used to defrag the database and causes increase in T-Log?RegardsSachin
Canada DBA
I think it is because of your nightly optimization job. -ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-19 : 13:36:50
|
| "I need to shrink the TLog or DB, AFTER I got the full backup"No, do not shrink them!!!!. They will just grow back again the following night when the optimisation runs. And the new LDF file will be more fragmented, and will take "effort" to create.If the size of the LDF is bothering you ether disable TLogging during Optimisation or make TLog backups MORE frequently during optimisation.If the size of the LDF is <= 120% of the MDF, and you are not desperate for disk space, then just leave it alone!Kristen |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-19 : 14:13:17
|
| if it is the optimization jobs, you could always change the recovery mode before and after the job. and somewhere in the back of my feeble mind I remember something about moving the job of index creation to the tempdb.====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-19 : 14:16:59
|
If your database is in full recovery mode, just change it to do transaction log backups every 15 minutes, 24 x 7.quote:
Originally posted by farhadr
During the day, from 6am to 8pm I have t-logs every hour. After business hours, I get a Full backupThen run the Optimization and Integrity maintenance JobsAnd at last, I get another full backupBut the first T-Log at 6am next day is too big. Why? I thought a full backup truncates the T-Log. Canada DBA
CODO ERGO SUM |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-09-19 : 14:27:20
|
| Hi Fargadr,If you are executing some jobs to defrag the indexes then the T-log will visibly grow. To avoid this there are 2 ways1) Change the recovery modely from full to simple.This will avoid the sql server from logging the defragmenation in log and the growth in log will reduce.2) While executing the defragmenation of indexes (I hope there must be some cursor based script), execute "Dump Tran <database name> with NO_LOG" in every 50-60 iterations. This will keep on removing the transaction log. I hope you will be performing this activity at off peak hour.Hope it helpsRegardsSachin |
|
|
|
|
T-Log size after Full backup