| Author |
Topic |
|
chulheekim
Starting Member
46 Posts |
 Posted - 2010-01-11 : 19:33:13
|
| I have a production database primarily for reporting purpose, not the main one. I found that its primary and log file is at a same drive and its recovery model is Full. It's growing fast and taking up a lot of space. My question is does that make sense to have Full Recovery model when its primary and log file is located at the same physical drive? Any objection to changing the recovery model to 'Simple' as temporary solution until moving the database to a different server? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-11 : 19:43:35
|
| The recovery model has little to do with where you place your database files. Separating them onto separate disks has to do with performance.If you switch to SIMPLE recovery model, then you lose your ability to restore to a point in time. We can't make that decision for you as that's a business decision. Can your business afford to lose all of the data since the last full or differential backup? We can't, so we never use SIMPLE.How often are you backing up your transaction logs? If the LDF file is biggish as compared to the MDF file, then you should increase your frequency of tlog backups or break your transactions into smaller units.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-01-12 : 11:44:54
|
| I don't know how often we do Transaction Log backup. I will find out during the meeting. I am a newbie and I may sound silly. Can you restore up to the point of failure even if the hard drive contains both the mdf and ldf file? I don't think you can because the hard drive is gone. Only things you can restore from is full backup, differential backup, and transaction log backup. Isn't it like you have to have the ldf file to restore up to the point of failure? sorry if I got totally wrong concept. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 12:48:47
|
My two-pennyworth:"I don't think you can because the hard drive is gone"I've had instances where the database was toast, i.e. in any real business sense, but I was able to make a "final" transaction log backup. Thus I was able to restore the database, from previous FULL and all subsequent TLOG backups, to the point of failure.If the server catches fire  , and thus nothing is retrievable, then your are only as good as the most recent backups you have which are OFF the machine. Therefore worth copying to a nearby-server as soon as the backups are made, or at least using a different disk drive to the MDF/LDF files, and then to tape / offsite according to the timelines in your company's policy on such things.FWIW I see NO reason to have Tlog backups at intervals of greater than 10 minutes. Lots of people take then once an hour, or less frequently. Why? Its just increasing the likelihood of lost data being an hour instead of just 10 minutes.(If you make TLog backups every 10 minutes you have 6 times as many files as if you did them hourly, but the total filesize is pretty much the same - give or take a few overhead bytes). |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-01-12 : 22:53:16
|
| I found out that we do full backup every sunday, differential backup and transaction log backup to other drive (F:) everyday night. Can I restore up to the point of the failure if the drive (E:) went dead. Remember that the mdf and ldf file is located in the same drive (E:). Isn't it that I can restore only up to the last transaction log backup? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-01-12 : 23:39:07
|
| You definitely need to increase the frequency of your transaction log backups as you could lose 24 hours of data in your current backup plan. And yes you can only restore up to the last tlog backup. That's how SQL backups work if you can't perform the final tlog backup to get it to a sooner point in time. Can your business afford to lose 24 hours of data? Mine can't, hence the reason why we perform tlog backups every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-13 : 02:49:32
|
| "differential backup and transaction log backup to other drive (F:) everyday night"If you are only doing Trasnaction log backups once a day there is little[**] point having them, you might as well change to SIMPLE recovery model and not have any transaction backups at all.[**] One reason would be that even with only a single transaction log backup per day you could restore to a point-in-time between that backup and the earlier/previous one.If you want transaction log backups make them every 10 - 15 minutes. |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2010-01-13 : 18:47:58
|
| Thank you so much. Sounds like I've got some work to do. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-14 : 03:53:01
|
| Have fun! Come back if you have any questions ... |
|
|
|
|
Full Recovery Model