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 |
|
nathan.russell
Starting Member
13 Posts |
 Posted - 2004-10-15 : 05:56:26
|
| Hi,I wonder if anyone can help clear up my non-understanding of the SQL database files and transaction log files.My understanding is that the database file contains the data; and is always up to date (assuming nothing has gone wrong);and that the transaction logs contain a record of each transaction performed against the database file - transaction since when ?In the event of losing the database file, one can recover the last backup of the database file, and re-apply the transactions from the last backup of the transaction log ?? Right ??If this is the case, is it sensible to backup the database weekly, and backup the transactions logs daily ? (assuming the importance of the data allowed that - I'm currently thinking about weekly/daily as its easy for my simple mind to understand !)So, if I backup the db weekly (eg. Sunday), and the t/logs daily (eg. Sunday, Mon, Tues, Wed, Thurs, Fri, Sat):1) Do I need to backup the t/log on the Sunday, or is it effectively duplicate data, as the backup of the db file will contain all of the last transactions ?2) If I loose the system on Thursday, I can recover my last Sundays db backup; then do I need to just recover the Wed night t/log and apply the transactions, or do I need to recover all t/logs since the db backup and re-apply them in sequence ?Another related question:I have just started with this company and taken over from the guy who used to 'look after' the SQL server.Currently, we do database backups on Sunday only. We do not backup transaction logs (hence my questions above - I'm not that comfortable with the situation !)We currently have an issue with disk space, and when the server fills its disk, he (the old 'dba') is in the habit of using EM and truncating the t/logs and shrinking the database.Given my understanding of the db and t/log files above, this doesn't sound like a good idea ?? does it ??What does truncating the t/logs do ? How much of it does it truncate ?Is the transaction log a log of transactions since the last backup ? Does the process of backing up the data file effectively zero the transaction log ?Any help clear up my many mis-understandings will be very much appreciated,Best regardsNathan |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-15 : 07:46:14
|
| First of all, you need to understand how your system works... how often the database is updated.If the database is updated very often, the transaction log grows accordingly.If this is the case, is it sensible to backup the database weekly, and backup the transactions logs daily ? If the updates to the database are very frequent, the logs will grow huge over the day. When you need to restore the database from a crash, say on saturday, you will need to restore the database backup from Last sunday and restore ALL the transaction log backups for the last 6 days. This is painfull and time consuming process, causing long downtimes.1) Do I need to backup the t/log on the Sunday, or is it effectively duplicate data, as the backup of the db file will contain all of the last transactions ?Understand that, Ideally, when you take a transaction log backup, you also truncate the transaction log. So when you back up the log the next day, you will not have yesterday's transactions. If you do not truncate the log, it will grow huge, which seems to be your problem already.2) If I loose the system on Thursday, I can recover my last Sundays db backup; then do I need to just recover the Wed night t/log and apply the transactions, or do I need to recover all t/logs since the db backup and re-apply them in sequence ?From what I said above, you will need to restore all log backups since the last database backup.Given my understanding of the db and t/log files above, this doesn't sound like a good ideaNo it isn't. This will disable you from being able to restore the data to the point of incident, in case of a crash.What does truncating the t/logs do ? How much of it does it truncate Tuncating log will remove all the commited entries from the log. If there are any open transactions while this command is issued, they will not be removed from the log file.Is the transaction log a log of transactions since the last backup Transaction log is a log of all transactions since it was last truncated.Does the process of backing up the data file effectively zero the transaction log ?not sure what you mean by this...Ideally, I'd backup the database every night and take log backups everyhour during the day. This gives me the best of both worlds.Feel free to ask if you have any more questions of doubts...Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
 |
|
|
ewe1d@yahoo.de
Starting Member
17 Posts |
Posted - 2004-10-15 : 08:03:47
|
| Hi,What about taking an differential backup once a day? That way you start a restore with the last full backup, followed by the newest differential backup, followed by all transaction logs since then. Must be faster than applying all the old tran logs. |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-15 : 08:07:30
|
| Depending on the size of the database, unless it's huge, I find transactional backups more convenient than differential...Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
|
|
|
nathan.russell
Starting Member
13 Posts |
Posted - 2004-10-15 : 09:15:06
|
quote:
First of all, you need to understand how your system works... how often the database is updated.
OK, to keep this discussion (relatively !) simple, we have 1 main database, and within it, 2 pretty simple tables whose structures are roughly the same.Each table gets approx 4000 new records added per hour (on a 24/7 basis); and once a record has been created, it never gets updated, and we dont currently delete them.The database has been in operation since April this year; and on the basis of 4000 new records per hour, the 2 tables are growing by 96,000 records per day; currently standing at something like 576,000 records each.The database is currently 14GBFrom your description of what the transaction logs are, given the number of transactions per day - I can see that:a) the transaction log grows massively and I can understand why !b) the previous dba's habit of truncating the log anytime he feels fit is a bad idea !I now understand that when the system backs up the transaction log, it gets truncated at that point; and that backing up the database file does not truncate the log ? Right ?quote:
What about taking an differential backup once a day?
quote:
Depending on the size of the database, unless it's huge, I find transactional backups more convenient than differential...
Does my database count as 'huge' ?So, how would a differential plan work ?Do a full backup of the database on Sunday ?, then differentials every night (Mon - Sat) ?. So, what does the differential backup actually backup ? The database or the t/log ?And to restore from a differential backup ? I restore the last Sunday's full backup, then just the last differential ? Is that right ?CheersNathan |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-10-15 : 10:39:37
|
| Good lord....half a milion rows and your DB is already 14GB? yikes. But no, that's not a huge database.Ok, past that now.If you have 4,000 rows being added per hour, I would recommend a setup something like this:Early AM: Tlog backup, Database Backup.Hourly afterward: Tlog backupIt just occurred to me that your 14GB might be the total database size, including some pretty big tlogs, if you don't back up or truncate them regularly, and you get 4K records per hour. |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-16 : 00:32:42
|
| I concur...Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-16 : 02:45:53
|
| What would happen if the database failed at some point - can you get the "lost" data again from somewhere?If so I don't see any point in backing up the transaction logs. They will be just dead-weight. Set the database Recovery Model (Data : Properties in Enterprise Manager) to SIMPLE and thats it - no more wild transaction log growth, no transaction backup requirement, just do Full/Differntial backups. That's why your predecessor had probelms - with the database set to a FULL Recovery Model the transaction will grow until back'ed up (which normally is set to do a truncate too), or using the brute-force truncate-logs approach (which proves my point - if he did that then there is no point in having the transaction log in the first place!).If you canNOT recreate the data from elsewhere then I would adopt a transaction backup stratgey of "frequently", that way in the event of database failure you will only lose a small amount of data. "frequently" may be every 10 minutes, or hourly, but it probably ought to be more often than "daily"As your data is basically "insert and forget" then I don't see that a differential backup strategy is appropriate - if you did Full backup on Sunday and Differential on the other days you would have a small backup on Monday, one twice that size on Tuesday as it would also contain Monday's data, and so on (but I suppose you could throw-away Monday's backup once Tuesday's is done - or once it was safely on tape etc.)Our application has changing data. We adopt a stratagey of Full on Sunday, Differential each other day, and Transaction backups hourly (probably ought to be every 10 minutes ...). Our database is stored at an ISP, and I don't want to rely on them for a recovery from tape backup when we are in a hurry for a restore, so this strategy is to enable me to keep a full weeks worth of files online for restoration purposes - a FULL backup every day would run us out of disk space. We delete the Full backups after two weeks, the Differential backups after 4 days and the Transaction backups after 2 days - so we get less "granular" on our restoration ability the further back we have to go.Note that the Full/Differential backup and Transaction backup and not synchronised, as such. To restore to a point-in-time you restore a Full+Differential backup to get you to a point in time. Then you restore a Transaction backup that spans a period of time from BEFORE the Full/Differential backup was made and SQL figures our the latest "transaction number" the Full/Differential backup included, and then scans the Transaction backup to get to that point, and then starts restoring transactions from that point onwards. Then you apply the next transaction backup in sequence, and so on.The only other observation I would make is that big transaction logs slow systems down. I don't know if it effects normal running, but IME it hugely effects RESTORE times. So if your transaction logs are pretty big then your RESTORE will be longer too ... more frequent Transaction backups sorts that out. That might impact on your plan to do Transaction backups only daily.Separate to that, when you (or SQL's automaton) rebuild indexes etc. that tends to generate a lots of transactions; this can push up the size of the transaction log, and can justify a truncation of the file to get the size back down - but that's pretty pointless if the next index rebuild in 10 minutes is going to push the file size back up again!Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-16 : 02:47:16
|
| "I find transactional backups more convenient than differential"Hemanth, can you expand on that a bit - I'm curious!Kristen |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-16 : 03:25:09
|
| Kristen, With a differential backup, I am bound to restore all three different backups to bring the database back to the exact time of failure. Ofcource, the diff backup works great in reducing the restore times when the same data is being updated over-n-over in HUGE databases. But with a database of about 14 GB with only inserts (like in this case) it doesn't do me any good.In my experience the data loss is more due to accidental tamper with the data than due to hardware failures (considering you have the proper RAID setup). Most, actually ALL, my databases are maintained with in our enterprise, and considering the low hardware costs, we do not have much restriction on the size of disk arrays we use, and so do not have an issue with taking a FULL backup every day and keep a weeks worth of backups. But since you've mentioned that your database is on an ISP, limited by the space and hardware setup, you probably can't agree with that. More over, I think a transaction log back up every 10 minutes is an overkill, especially on a system with only 4000 inserts per hour. This amount of activity does not increase the log space to an extent to effect the peformance of the system.I think a nightly (or whenever the load is minimum) database backup and hourly log backups should work just fine for his requirement while not effecting the system, yet, in case of an "Event", providing a quick and easy recovery mode up to the time of the "event". Keeping old backups (about a weeks worth) is always advicable, in case of an "event" that goes unnoticed for more than a day.I also agree with you on the increased log sizes during rebuilding of indexes or such. Personally I prefer to take a 'log backup before' and 'full backup (and truncate log) after' such exercises.Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
|
|
|
|
|
|
|
|
Understanding of database & transaction log files