| Author |
Topic |
|
blorg
Starting Member
4 Posts |
 Posted - 2004-08-05 : 07:35:27
|
| Perhaps someone could explain something here as I am somewhat new to SQL Server administration and backups in particular. We are using SQL Server 2000 on Windows 2000 Server.I'm using the Full recovery model, optimizing Sunday 1am, doing a complete backup Sunday 2am, and backing up the transaction logs every other night at 12am. (E.g. following the defaults in the Maintenance Plan wizard.) Monday's transaction log backup (the first after the complete backup on Sunday) is always much bigger than the transaction log backups for all the other days' transaction log backups. Why is this – what exactly is it that is taking up all the room?Also, if anyone could suggest any reading to help fill me in on this I'd appreciate it (I have read Chapter 5 of Inside SQL Server 2000, on backups, but it doesn't really help me to understand what is happening here.) |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-08-05 : 14:14:21
|
| Does your optimizing include reindexing? If so, I suspect that is your answer. Reindexing takes up a lot of log space.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 14:18:41
|
| It's bigger because the optimizations job uses a ton of logging. The optimizations job runs DBCC DBREINDEX which uses the transaction log a ton. That's why MS suggests on small scale systems that you can run DBCC INDEXDEFRAG instead as they have the same effect on small scale systems.You should be backing up your transaction log more often than every other night. At least do it once an hour. We do ours every 15 minutes due to the criticality of the data.Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-08-05 : 14:28:33
|
quote:
Originally posted by tduggan
It's bigger because the optimizations job uses a ton of logging. The optimizations job runs DBCC DBREINDEX which uses the transaction log a ton. That's why MS suggests on small scale systems that you can run DBCC INDEXDEFRAG instead as they have the same effect on small scale systems.You should be backing up your transaction log more often than every other night. At least do it once an hour. We do ours every 15 minutes due to the criticality of the data.Tara
Tara,I'm not sure you can make that general statement without knowing more about the environment. There are low transaction systems where every other day is fine. The only reason to do it more often is to keep the log from growing to large. The Criticality of data has no bearing. I can restore to a point in time with TLogs every other day, just as well as I can if I have them every 5 mins. If they have a low number of transactions, and the TLog size is not causing a problem, then adding more tlog backups is just unnecessary overhead.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 14:34:38
|
| The criticality of the data has tons of bearing on the decision of how often to back it up. I realize you can restore to a point in time if you are able to get that transaction log. But that's if. You might not be able to and can only restore up to the last transaction log. Every other day seems like you'd lose a bunch of data unless you didn't have a lot of transactions as you mentioned. But then why not just perform a full backup each day with SIMPLE recovery model for this type of system?Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-08-05 : 15:04:31
|
| A Full Backup everyday with simple recovery would probably work just as well for a system like that, except if they ever needed to restore to a point in time. For example, I just ran a n UPDATE without a WHERE clause. Even though I haven't had a TLog backup in 24 hours, I can take one right now, and restore to 5 minutes ago (Before the errant UPDATE). Where if I just do FULLs every day, I lose that ability.I see what you mean if your server blows up, and you can't do a TLog backup, then you have a problem. But again, you have to take it case by case. I probably jumped the gun by saying the criticality of the data has NO bearing on the decision. I think the number of transactions is big factor in the desicion as well though. But, I still stand by my statement that you can't give a blanket statement like "You need to do more frequent log backups" withought knowing more details than we have.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 15:06:53
|
| Agreed on all points.I assume that if you don't need to do transaction log backups all that often like this every other day approach, then you might as well use SIMPLE recovery model with full backups.Tara |
|
|
|
blorg
Starting Member
4 Posts |
Posted - 2004-08-06 : 06:44:29
|
| Thanks for all of your replies. I have 'reorganize data and index pages' ticked in 'optimizations' with 'change free space per page percentage to 10%', along with 'remove unused space from database files'. I'll try testing whether this is it by running an extra optimization this evening, and seeing if this causes a growth in the transaction log. However - and this is a point I perhaps don't understand fully - I have the optimization running an hour *before* the complete backup - I would have thought that even if this caused a large growth in the log, that it would be truncated with a complete backup (as one only needs the log *after* a complete backup to recover, surely?). Perhaps someone could fill me in here.With regards to the transaction log backups, we are running these every night except Sunday when we do a complete backup, rather than 'every second night'. It wouldn't be catastrophic for us if we had to roll back to a previous night, just a bit of a nuisance - we can actually recover all the data lost in the meantime as we are aggregating this from external sources. Having said this, our transaction log backups only tend to take a few minutes, so we could do them more regularly.Another issue with the backups is that we are backing up to a file on the same disk (there is only one in the machine). In the morning we then manually add these to a RAR file and FTP them down to our offices (our server is in NJ, we are in Ireland.) I'd like to automate moving these backups off the server if possible and would appreciate any pointers on how to best go about this. |
|
|
|
chadmat
The Chadinator
1974 Posts |
|
|
blorg
Starting Member
4 Posts |
Posted - 2004-08-06 : 13:25:16
|
| Thanks Chad, that explains how they are seperate very well. Am I correct in supposing that the following sequence would work for Sunday night, avoiding having to hang on to the big transaction log backup for a week:1. Optimize/reindex2. Transaction Log Backup3. Complete Backup4. Delete the transaction log backup from (2) - as I now have a complete backup |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-08-06 : 13:49:03
|
| Or You could do a TRUNCATE_ONLY backup to eliminate step 4.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
Large trans log backup after complete backup?