| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2007-11-05 : 06:43:46
|
| Im changing from simple to full modeSet database to be in full mode and backup for midnight.The log for every 15 minutes.What best approach at midnight not do logs until say 2 am or just leave every 15 minutes.Thanks |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-05 : 07:05:43
|
| Why do you need it? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-05 : 07:39:25
|
| I recommend leaving the interval the same all the time - otherwise:Folk may be confused when they come to restore and files are missing - are they really missing, or just not needed?You may introduce a procedure during that period that increases the Log usage, and thus a backup would prevent the logs being extendedActually we take a different approach which is to increase log backup frequency to every 2 minutes during database maintenance - to keep the Log filesize under better control.Kristen |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-11-05 : 09:04:04
|
| Kirsten so you have full mode and run every 15 minutes then during the backup job you run another one every 2 minutes.I got the full backup running now and the log trn hasn't kicked off it may know its is doing a full backup and not to do log transaction backups.Basically i changed to full mode because last week some ran an process that did not commit and files grew to 99 gig left me with no space so if i did not have full i could only go back a day so i decide to change to full and log every 15 minutes.Over weekend log went up to 15 gig was 3 on friday.If im correct on understanding i cannot do shrink (hopefully do not have to do this), but if i do i switch back to simple and then do the shrink commands.Thanks for your advice really appreciate it. |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-11-05 : 09:16:03
|
| Can checkpoint be issued if need by in full mode.Running log transaction now full backup finsihed.File is still 15 gigWhen i do this dbcc loginfo('deltekcp')Got two create lsn empty and 252 transactions.I was hoping the transaction log would get truncated after backing up log using full mode (did i mis understand) |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-11-05 : 09:21:30
|
| Sorry me again.The Log is now 14272.49 73.39 (used) 14199.1 FreeWas 14272.49 13892.99 used 379.5 free So thats better.But do not want 15 gig backup every 15 minutes So is it ok to shrink size of it now or do i have to change back to simple mode to do this.Shrink it then do full backup.Thank you |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-05 : 09:52:41
|
"so you have full mode and run every 15 minutes then during the backup job you run another one every 2 minutes."Actually we run every 10 minutes, but I think 15 is good too.Its during DB maintenance [Defrag indexes and update statistics, plus also we bulk-delete stale data as part of that operation] that we increase this to every 2 minutes"the log trn hasn't kicked off it may know its is doing a full backup and not to do log transaction backups."If you start LOG Backup during Full backup then the Log backup will sit and wait for the Full to finish and then resume."I was hoping the transaction log would get truncated after backing up log using full mode"It should be truncated, but the file won't get any smaller; the file will be reused, so once it gets to "equilibrium" it will stay at that size.I would not shrink it unless you have some exceptional delete etc. that has been done. it will just grow back again (which causes fragmentation of the file, and slows the system down whilst the log is being extended)If you have a specific task that causes excessive log file growth then you may need to rethink how that works (do in smaller batches, with Log Backup in between, or somesuch). That's why we do Log Backup every 2 minutes during DB maintenance, because that is our biggest log file user  Kristen |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-11-05 : 10:25:59
|
| Thanks for answers.Im going to keep an eye on the size of the log file for a few days and see what it actually grews to Then if it is 500 mg - that be 48 gig space required before next full backup.I will get the transaction log to a reasonable size and then put back on full and log backups. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-05 : 22:30:56
|
| You can set log backup schedule to skip time period that performs full backup, say set 'starting at' time to 2am instead of 12am. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-06 : 00:21:58
|
quote:
Originally posted by TRACEYSQL
But do not want 15 gig backup every 15 minutes
I'm confused. Is your tlog (LDF) file 15GB or are the tlog backups (typically TRN) 15GB? If it's the backup files, then that is quite excessive if you are backing up every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-06 : 10:17:26
|
| Also possible that's the size of the BAK from the first time of doing a TLog backup |
|
|
|
|
Full backups