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 |
|
dewacorp.alliances
452 Posts |
 Posted - 2006-11-13 : 04:21:52
|
| Hi allAt the moment, I am still using the optimisation from Maintenance Plan and found something that I didn't come accros till yesterday.Basically, I have the jobs:1. TLog backup, daily, 8AM till 1AM, every hour2. Optimisation (DBCC Reindex), sunday, 9.30PM.I have 2 databases that close to 40GB and while the optimisation kick in don't realise that the TLOG backups wasn't running at all and I end up at 2AM with huge TLOG backup which is I believe come from 1AM job. BTW, the job for optimisation is close to 3 hours. So ... is this right then? If this is the case then I should make the TLog backup running every 15 minutes then for that particular time (12AM - 2AM) ... correct?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 04:31:51
|
| On a 24/7 database I reckon TLog backups should be 10 - 15 minute interval anyway. How do you afford to lose 59 minutes work when you could only lose 9 or 14 minutes max instead?The total space required by TLog backups every 10 minutes is the same (give or take some overhead) as 24 x hourly TLog backups ... the only real downside is when you restore there are LOTS of the blighters!The upside is the more frequent Tlog backups kick in before the LDF file gets huge - that reduces overall disk space usage and potential file extension stuff. Also, if you are able to retain a smaller LDF file because of this action your RESTORE will be quicker (RESTORE has to first make a database of equivalent size to the original, even if there is no data in it!, and preinitializing that lot takes a while [SQL2000 speak]) (Note that you will need to do a one-time shrink of the DB to reclaim that space, if you want to)So I wouldn't bother with cleverness in more frequent TLog backups for certain times of the day, I'd just let them rip at that frequency all the time.As discussed in other threads INDEXDEFRAG will use much less space than REINDEX.Note also that "optimisation from Maintenance Plan" will default (unless you explicitly choose the alternative option!) to CHANGING all your indexes to FILLFACTOR 90% - might be worth checking if that has happened to the indexes you thought were 100% fill factor.Kristen |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-11-13 : 05:16:27
|
| >> How do you afford to lose 59 minutes work Depends on the system - a lot can cope with just daily full backups or maybe none at all.I wouldn't use maintenance plans - it's simpler to just code the backups yourself - then you will at least know what they are doing.==========================================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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 05:39:07
|
| "a lot can cope with just daily full backups or maybe none at all"Indeed, but I think the case for a hourly TLog backup (making the assumption that TLog backups are needed periodically) is hard to justify compared to every 10 minutes or so.Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-13 : 05:58:42
|
| hi guysI don't mind to reduce it to 15 or 30 minutes but my question why there is no TLOG backup kick in at 10PM, 11PM on Sunday while Optimisation kicks in (DBCC REINDEX) and having huge TLOG backup @ 2AM instead. ?!?! So it seems that while DBCC REINDEX running, it locks the backup? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-11-13 : 06:36:06
|
| Have a look at what is going on at the time. Could be that the checkpoint is failing to complete, could be that a transaction is being held so that there is nothing to backup (unlikely). More likely is that they need the same resource.==========================================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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 07:05:01
|
| Let me just check this:"Optimisation (DBCC Reindex), starts sunday, 9.30PM.""the job for optimisation is close to 3 hours"Suggests that Optimisation finishes at 0:30AM ?"there is no TLOG backup kick in at 10PM, 11PM ""huge TLOG backup @ 2AM instead"Is there a TLog backup at 12PM / 1AM, or NOTHING until 2AM?"TLog backup, daily, 8AM till 1AM, every hour"If there is NO Tlog backup until 2AM then I would expect that is the 10PM backup which, for some reason, is blocked. The Optimisation finishes at 0:30AM, the blocked TLog backup resumes, and takes until 02:00AM to complete.The is probably verifiable from the BackupHistory table in the MSDB database (I'm pretty sure it has both StartTime and EndTime columns)Moving to INDEXDEFRAG instead of INDEXREBUILD will block the TLog backups less (and reduce the "working space" requirements enormously)Kristen |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2006-11-13 : 08:20:12
|
| Hi KristenActually, I am revising what I said for scheduled job:1. TSQL Backup, 8.00 AM till 12.05 AM, every hour, daily2. Full Backup to file server, 12.30 AM, daily3. Inetgrity Check (checkdb), 9.00 PM, daily4. Optimisation (reindex), 9.30 PM, Sunday onlyOn Sunday night/Monday morning, this what happens when I looked the backuphistory sys table:...8:00:04 PM - Tlog backup - 0 second9:13:07 PM - TLog backup - 1 second11:18:09 PM - TLog backup - 6232 seconds (huge TLOG - almost 33GB)8:00:04 AM - Tlog backup - 5 seconds...Looking at this it seems that:1. Integrity check blocks the TLog Backup cause as you can see the startime @ 9:13PM instead of 9.00PM2. Also, optimisation (reindex) blocks TLog Backups and in fact 10PM one running at huge TLog @ 11:18PM instead. And due to it's big .. it blocks the full backup @ 12.30AM. So ... this explains that there is no way you can control the TLog to mininum size during this time - 0930PM till 12:00AM. Cause the process of reindexing is building up huge TLOG and it blocks any activities (backup). Also, even if the Tlog backup sets to 15m or so ... is not going to help, isn't it? Am I correct here?Someone suggest to look at the INDEXDEFRAG instead. Is it going to help to control this TLog? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 09:09:57
|
| I'm surprised the Full Backup didn't run?It should have launched at 12:30AM but it would have then sat and waited, blocked, until any currently running backup finished."Someone suggest to look at the INDEXDEFRAG instead. Is it going to help to control this TLog?"I think so. It should not block anything, unlike REINDEX (which the Maintenance Plan uses). The TLog activity will be less too. But the Maintenance Plan won't do this for you, you will have to make your own (see Tara's Blog for some good examples)Kristen |
|
|
|
|
|
|
|
|
SQL2K: TLog Backup and Optimisation