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 |
|
joswoody
Starting Member
4 Posts |
 Posted - 2015-04-01 : 07:49:49
|
| Hi there!From what I can understand if your Recovery Model is set to Full you will be able to restore your data at any point in time. To be able to do this you need to backup your transaction logs every now and then. For example: every hour. so we are saying if you are havin a full backup at night and transaction logs every hour (2am, 3am,4am etc) and the server dies at 2.45pm you will be able to restore the database from the 2pm transaction log backup.Am I right?1) If your model is set to full and you never take backups of your transaction logs you still have to do a restore from your full backup so if you are happy that you can restore from your Full backup you should set the model to Simple as it is useless to have your recovery model in FULL. Is there any other use of the Recovery set to full?2) If I make that the Transaction Logs are taken every 15 minutes or every 30minutes will this be a lot of overhead on the Database and will effect users?Thanks,Joseph |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 08:12:08
|
| If you backup every hour and your server dies at 2.45 then your most recent backup is 2am. You cannot restore AFTER that time.However, it is often possible (after database corruption etc. - but NOT if the server caught fire!!) to make a final "tail backup". You would do that at, say, 2:46. Let's say that everything past 2:44 is garbage, you could restore the Full backup, the 1am and the 2am Transsaction log backups and then restore the 2:46 tail-backup using STOP AT 2:441) If you never backup your Transaction Log the file will grow and grow ... and eventually you have disk full! It is possible to truncate the Log file, but not really a good idea. I suppose you COULD use the Transaction Log to make a TAIL BACKUP, after a disaster, and then use that in addition to restoring the Full Backup. But Full Recovery Model only really makes sense if you also make regular transaction log backups.2) There is an overhead, but it is modest. Basically in the course of 24hours you are going to backup xGB of transaction data. If you back it up once and hour, or every minute, that is the same. Once committed transactions are backed up the log file space is available for reuse, and that data is not included in the next log backup.There is an overhead - let's say it is yKB per backup. So it you do hourly TLog backups that is 24 x yKB in addition to the total xGB of space. If you do every 10 minutes that is 144 x yKB + xGB.The decision should be made on how much data you can afford to lose. If you have people making data entry and they cannot easily repeat that data entry (e.g. they are making data entry based on conversations on the phone in a call centre, rather than just entering paper invoices [which could easily be repeated from the original documents]) then you want the shortest period possible.We have done 10 minutes TLog backups for as long as I can remember, but I am now hearing that DBAs are doing TLog backups every minute or two ...The impact on users (of a TLog backup) is minimal.Consider:Once a TLog backup is made it is important to copy it to a remote machine. If all the backups are on a drive in the main SQL server and that "dies" you will only have the last full backup on Tape.Make a TLog backup immediately before the Full backup. That will reduce the size of the full backup. (If you are doing Tlog backups every few minutes then that is probably fine.)Regular TLog backups will prevent the Log File itself becoming huge (compared to making TLog backups less frequently).We change our 10 minute log backup schedule to every minute during index rebuilds (as they are the most TLog intensive operation we do and they fill the TLog file very quickly) |
 |
|
|
joswoody
Starting Member
4 Posts |
Posted - 2015-04-01 : 11:28:46
|
| Thanks for your reply.So what you are saying is that if no backups of the logs are being made and the recovery model is set to FULL, technically (I know its not good to do, just for my knowledge) you can go back with the tail backup?So know that I have a big log file which I never backed up, what is the best way to start doing it in the right way? Start by backing up the 1st one - which will be quite big? Is that the right way?Thanks,Joseph |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-01 : 13:07:29
|
quote:
Originally posted by joswoody
So what you are saying is that if no backups of the logs are being made and the recovery model is set to FULL, technically (I know its not good to do, just for my knowledge) you can go back with the tail backup?
Yes, however you are presumably doing this because you had a disaster ... it might be that your disaster has destroyed your Log File (or the physical media it is on), so not a good one to stake your career on!! But from a philosophical point "yes"  quote:
So now that I have a big log file which I never backed up, what is the best way to start doing it in the right way? Start by backing up the 1st one - which will be quite big? Is that the right way?
You could back it up, and then SQL will start reusing the space from the beginning. However, the physical Log File will be huge. You could take the log backup and then Shrink the log file to a realistic size. Alternatively you could decide that you just don't need the data currently in the log file, and you want to start log backups From Now.In the second case you can set the Recovery Model to Simple. That will chuck away all the transactions in the Log File. Then set it back to Full Recovery model. You also need to shrink the file to get it to a sensible size (do that after setting it to Simple, or after setting it back to Full, I don't think the order matters.) |
|
|
|
joswoody
Starting Member
4 Posts |
Posted - 2015-04-02 : 04:18:48
|
| Thanks for your reply.No I am asking so I set it up in a right way :)So I was thinking of this scenerio:1AM - Full Backup2AM - T-LOG Backup2.30AM - T-LOG Backup3AM - T-LOG Backup3.30AM - T-LOG Backup............11.30PM - TLOG Backup12:00AM - TLOG Backup12:30AM - TLOG BACKUP + TRUNCATE ALL LOGSWith the Truncate at 12.30 it will remove all logs and keep the log file small.Is this a good scenario or is there something wrong in it?With this I can keep my log file small and I can restore back to every 30minutes?Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-02 : 09:44:12
|
You should never truncate your log file (except in extreme circumstances)Consider this:Every day Full Backup at 1AM and Log Backup every 30 minutes.Disaster happens at 2AM on Thursday. (Disaster might be server catching fire / breaking, might also be corruption of the physical file for some reason, might also be some Twit!! accidentally deleting half the customer records or a DEV rolling out an Update that actuall damages some data )You restore the Full backup from Thursday 1AM and then plan to restore the TLog backups from 1:30AM ... onwards. But you find that your Full backup is corrupted - or missing (lets say that you had a disk full on Wed/Thur night).No matter, you restore the Wednesday Full backup. You find that that is fine, and not corrupted. You then restore TLog backup from 1:30AM Wednesday ... ALL the Wednesday TLog backups ... then ALL the Thursday TLog backups, up to the point of the disaster. You are now good to go If Wednesday's Full backup was broken you could start with Tuesday's ... or Monday's ... you can go back as far as you like, although the further you go back the more TLog backup files you have to restore and the longer it will take. But it IS a "Get out of jail free" card However, if you were to truncate the log at any point you would not be able to restore the TLog backups PAST that point. This is referred to as "Breaking the backup chain".Point to note is that corruption of SQL Data Files is not uncommon. It happens. Spike on the disk controller card, bug in SQL, or some other event. The first you know about it is when users start telling you they are having problems, or when you run a DBCC CHECKDB (which checks that the database "structure" is intact). [You can schedule that, every night if you like, and set it up to send you an Email if&when it finds a problem. That would be Best Practice. If for some reason you cannot run it on the Production datbases, e.g. it takes too long, you can run it on a restored copy of a Full Backup, on another server, that restored full backup will also have the exact same corruption which DBCC CHECKDB can then find].But the good news here is that it is incredible rare for the TLog to ALSO be corrupted. Best Practice says to put the Tlog on different physical media to the Data file, which also reduces the risk. The way that the Tlog is written to, sequentially, is also less complex than the random-access/concurrent way that the main data file is written to. So even less chance of error.So when you find the database data file is corrupted:Set the database to SINGLE USER so no users or scheduled tasks can access it.Take one final "tail" Tlog backupRestore from the last Full backup. Run DBCC CHECKDB to prove that it is not corrupted. If it is restore an earlier Full backupThen restore all Tlog backups since then, including the final Tail Backup.Run another DBCC CHECKDB to check that everything is good. I reckon that (on the rare occasions when this happens) there is better than 99%, maybe even 99.9%, chance that this "replaying" or all TLog backups to a Known-Good restored, but old, version of the database will give you a clean database and, importantly, zero data loss.If all you had was Full Backups / Simple Recovery Model you are hosed. Your only good database is the most recent backup that is not corrupted. Might be a week, or a month, ago  |
|
|
|
joswoody
Starting Member
4 Posts |
Posted - 2015-04-06 : 04:25:51
|
| Thanks for your reply and good explanation!But if I do not Truncate the logs but take a regular backup, will the log file still keep on growing?Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-07 : 07:56:39
|
| No. When you take a TLog backup it marks the space used by the (committed) transactions in the TLog file as "available", and that space is then re-used. The file is not shrunk automatically, just "reused" on a round-robin basis.If, at some time, there is a large volume of transactions, before the next TLog backup runs, which fills up the TLog file then the TLog file will be extended. Thus, over a period of time, the file will grow to the largest size it needs to be to accommodate the worst-case scenario.It is worth monitoring the size of the file daily over, say, a week and seeing how much it grows. Once it settles down make a note of that size; in the event that you have some one-off situation in the future which causes the TLog file to grow significantly you can then shrink the TLog file back to that "working size". (No sense shrinking it smaller as A) you have worked out how much space is actually need and B) repeatedly shrinking and growing the TLog file fragments it on the disk which hurts performance. [In fact we optimise our Tlog files, after they have grown to "working size", to be contiguous on the disk for best performance])If you find that the space used by your TLog file (i.e. during checking daily, for a week) is larger than you are comfortable with then shrink it again and monitor its size hourly (or even more often) and establish if there is some activity that dramatically increases the size of the TLog. Our most Tlog-hungry task is the Index Rebuild housekeeping task. Consider optimising whatever is causing the excessive growth and/or running Tlog backups more frequently during that task. |
|
|
|
|
|
|
|
|
Full Backup and SQL Logs