Updated: 5 December 2005
January 11, 2006 at 4:08 am
Greetings to all.
Please bear in mind that I am not a highly experienced DBA, but I am responsible for some governmental databases, hence my questions and concern. These questions have been festering in my mind for a while.
When I perform a search on a subject, I always stumble across DBAs mentioning how often they back up their transactional log. They talk of backing it up every couple of hours, or twice or three times a day etc.
Here's my first question.
Is there something wrong with backing up the tran log every 10 minutes? It seems common practise NOT to backup the tran log as frequently as this. But why not. If the database fails, the first thing one does (so I've read) is back up the existing tran log, then do your restores. This why you restore to the point in failure. However, if you're database has just failed, how do you know you'll be able to back up the existing tran log. I mean, at that stage it's an unknown whether you will be able to or not. That's why, if you were backing up the tran log every 10 minutes, you'll have only lost up to ten minutes data. But if most back it up once or twice a day, well tell me what company can afford to lose more than ten minutes data. I thought being able to restore as much of a companys mission critical data is one of the most important of DBA tasks. I mean we're talking losing our jobs. So why is it not common practise to back up the tran log as frequently as every 10 minutes.
Question 2
As a follow up to the above question, how often do major banks, such as Natwest Westminster, Lloyds, Barclays back up their transactional logs (I'm thinking cash withdrawals etc).
Question3
Another thing I keep stumbling across, is that it is bad practise to have no limit on transactional growth size. This, I don't really understand. If this were the case, why would Microsoft have implemented this feature into 2000, when it must have been a problem for DBAs in version 7. If the log is backed up every ten minutes as discussed above, then it's size would be kept at a minimum and the chances of it filling up disk space would be minimal itself.
Thanks. I eagerly await your responses!
Drew
January 11, 2006 at 6:34 am
1. I back up my TLOG every four hours (work on government contract). Backing up anything takes up resources. You have to balance backing up with transactions. I back up to disk - therefore, when a backup is being done, there's lots of disk activity (read from the database files/write to the backup files). Then I copy my backup file to tape.
2. no answer from me
3. limiting the size the tlog can grow is a good idea. You don't want it to outgrow your disk space and bring your database to a halt. That being said, either way you need to set up an alert to let you know when the space is becoming full, regardless of whether it is set for limited or unlimited growth. Plus most systems seem to have more applications than just SQL Server on their server. Don't those other applications need space also? So, you limit the space SQL Server is allowed so that other applications don't run out of space.
-SQLBill
January 11, 2006 at 6:48 am
Ok, I understand point 3. But point 1. If your db was to go down, and you couldn't backup/restore the last tran log, you would tell the customer that the latest data you have is 4 hours ago? What do you think their reaction would be to that? (Genuine question, not a smarmy one).
January 11, 2006 at 7:40 am
In my case, that has happened twice. First time we lost 6 days of data and had to send our backup tape offsite to Veritas for recovery (crash was due to raid controller failing, made worse by a manager deleting the Quorum drive for the cluster. Then the backup tapes were worthless because neither Veritas nor the reseller told us when we purchased them that they wouldn't work with our system without a hotfix). The client didn't have any problem. The next one was just last month and lost us all the data. We migrated our main application to a new server following the vendor's directions (we first told them what hardware/OS we were going to use). Queries immediately showed the migrated data was corrupt. Turned out the vendor never bothered to tell us they don't support their product on Win2K3 with SP 1. Even uninstalling SP1 is unsupported. So we lost, at the most 1 month of data. Haven't restored from tape as the client doesn't need the data at this time, so we've archived it with a note about what occured in case there's a problem with the backup.
The bottom line is....what is your data used for? That determines how important it is to be able to restore to the second. We are evaluating another vendors product to replace the one we are currently using, but our data is 'old' after one week.
-SQLBill
January 11, 2006 at 7:51 am
I used to backup some every hour, some every 15 minutes. No idea about banks, but most of these guys would have multiple levels of protection, clusters, replication, etc.
You'd be surprised in many cases how little data has changed in 15 minutes. If you're a real time environment, like a store, then that might be a lot of transactions, but if you're doing daily corporate data entry, it might be ok. As Bill mentioned, you need to see what your data is used for.
Also, keep in mind restore time can be an issue. Some people would rather lose an hours worth of data than take an extra few minutes to restore a few more logs. If you're really worried, you might add a few differentials during to day to keep the restore time down.
January 11, 2006 at 11:06 am
Question / suggestions:
1. Question: They say "Enabling Volume Shadow Copy" on Windows 2003 server lets you to Windows-backup open files. Who knows anything about that for SQL Server databases?
http://www.microsoft.com/technet/technetmag/issues/2006/01/RapidRecovery/
says: "It can also be used with storage area network (SAN) hardware to simplify backup and recovery of large open files and databases..."
2. You can backup log with NO_TRUNCATE
3. Steve is right. Recent recommendations from msdn2 for SQL Server 2005:
"....Under the full recovery model, you should schedule frequent log backups. Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you need to restore after restoring the data..."
FROM:
http://msdn2.microsoft.com/ms191239(en-US,SQL.90).aspx
Updated: 5 December 2005
Regards,Yelena Varsha
January 13, 2006 at 8:42 am
Hi Drew,
since backing up the transaction log should not have a big impact on database performance (if it has you simply need better hardware) you could go as far as doing a backup every minute.
The only point is, the preceeding backup has to be finished before a new one can be started.
But if you do that, you will have to restore a _lot_ of backups in case of a failure... It's a trade-of between speed of recovery and security of the data.
If a full backup of the database required just 5 minutes you might even do a full backup every 5 minutes, so the recovery time would be very short. BUT that way you can not do a point-of-time recovery and you need a lot of backup media...
You need to get a clear definition of what your customers/company needs (and what's the budget), and according to that you build and implement a doctrine for backups and recovery (using full, differential and log backups, eventually clusters, standby servers and replication).
about limiting the growth of log- or db-files:
if your db files reside on their own (dedicated) drives, you may or may not limit the growth. If your database files should be on the windows system drive (standard install) you _need_ to set limits. If a database fills up the system drive not only the database fails, even windows can do (will do) a bluescreen and you might not be able to start the server again. To recover from something like that is possible too, but you need a lot of time and additional windows tool (like Windows PE or ERD Commander)
regards
karl
Best regards
karl
January 13, 2006 at 10:29 am
Thanks Karl. That makes sense to me. I suppose I was worried that backing up Trans frequently may be considered as extreme, and as I have no "mentor", it's difficult to know what is good or bad practise.
i work for a governmental department. If the system had to go down due to me performing restorations, well at least customers phoning in can be told ring back in 30 minutes, our systems our down. I think what would be unacceptable is losing customers information (customers that had already called and gone).
Drew
January 13, 2006 at 11:44 am
I would suggest that you consider doing more frequent differential backups. Maybe once or twice a day. When you do restores, a differential can really cut down the amount of transaction logs you have to restore. You just restore the latest full backup, the latest differential, and any tlog backups since the latest differential.
-SQLBill
January 16, 2006 at 10:24 am
How often you schedule trans log backups really depends upon your system - how much data, what type of data, how many users, how much read/write activity, hardware setup, appln's serviced and recovery times etc. There are no 'standards' as such as there are so many variables.
Read the white papers on backup/recovery solutions. A DBA should have this area of his work as his No.1 knowledge factor. (Point aside - concerning that Gov't databases are looked after by a 'true' DBA - no offence to yourself).
As to point 3. It is possible the final trans log cannot be backed up depending upon how the db. went down in the first place. This is where High Availabililty solutions such as Clustering, Log Shipping etc. come in and again depend upon costs, amount data, recovery time etc.
Again this is a very in-depth area which requires a large amount of reading and testing.
PRW.
Paul R Williams.
January 16, 2006 at 10:56 am
" (Point aside - concerning that Gov't databases are looked after by a 'true' DBA - no offence to yourself). "
Offence taken. Are you suggesting a DBA with more experience is a truer DBA than one with less? Academically, the white papers were read months ago, but does not account for experience, hence my posts to this and numerous other forums on all aspects of administrating. Here's hoping one day I grow up to be a "true" dba. Bad choice of word, Paul.
January 17, 2006 at 3:04 am
Oh Dear, if you get 'offended' by such a statement that's a pity.
Please read the statement in context. I would have grave concerns that Gov't databases aren't looked after by a true DBA considering the potential of data that they hold. You yourself stated you weren't a true DBA. That's not a reference to your knowledge but a reference to your area of expertise.
I as a DBA wouldn't want to be responsible for writing web pages for a secure web site. That should be done by a Web Developer with appropriate experience. If someone questioned that such work was being done by myself I wouldn't feel that was a slur on myself. I would agree with them.
Far too often in the world of IT these days we see very poorly written, insecure systems because the correct areas of expertise weren't employed. Not necessarily a fault of the individuals but rather poor training, poor project management etc. That was the point I was trying to make.
Apologies if you feel 'offended' by that but you seem to have jumped the gun very quickly to be offended by what I feel is a valid statement. You have taken it personally whereas if you actually read it correctly, you will see that it is directed as a concern for IT standards. There is no comment to you as an individual and your level of experience whatsoever. I stand by the fact that Gov't databases not being supported by a 'true DBA' (your words) would be a cause for concern, particularly if they contained personal and sensitive data which most Gov't databases do.
Paul R Williams.
January 17, 2006 at 6:31 am
"Apologies if you feel 'offended'
Apology accepted. We'll leave it at that. Though, the first time "true DBA" is mentioned at all in this thread is in your post.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply