1

This old SQL Server 2000 instance has a 14Gb disc for data and a 30Gb disc for backups.

Even though it is old, it still has tons of users doing transactions so the log file of the only database that is on this server fills the data drive everyday.

I figured I would put it in simple recovery and flush the transaction log, but I wonder what it impacts.

Without a T-log you cannot use your backup to recover to any other point in time than the time of the full backup, right ? What are the most important impacts apart of this I should be aware of when doing such a change to a database ? What else than losing a day of data (it is backup up daily to tape) might happen when I'm not generating t-logs ?

Thanks in advance

Improve this question
9
  • 1
    why not backup the log more often? Wait - your question states it fills the data drive every day? Why are you wondering about the T-Log?
    – Hannah Vernon ♦
    Commented Mar 22, 2016 at 19:42
  • 2
    Why is the log file filling the disk? If you are in full recovery mode, aren't you taking log backups throughout the day? This is the purpose of being in full recovery mode and should control log growth generally. If you don't want to back up logs, then switch to simple. Period. Just make sure the stakeholders know what scenario they'll be in if the database goes corrupt (which will be roughly the same scenario they're in now).
    – Aaron Bertrand
    Commented Mar 22, 2016 at 19:43
  • There is no room for such backups, it will fill the drive within a day and I'll have more problems then.
    – A_V
    Commented Mar 22, 2016 at 19:43
  • 3
    Is your data important, or not? Do you not have any place on the entire network large enough to hold a day's worth of transaction logs? I find that hard to believe. WalMart across the street has 2 TB drives you can plug into the USB port for about $40. Maybe I'm exaggerating a little, but not enough space is a terrible excuse in 2016.
    – Aaron Bertrand
    Commented Mar 22, 2016 at 19:44
  • 1
    Yes, that's what I am doing right now but the question here is : What else than my point-in-time recovery am I impacting from using a simple recovery model.
    – A_V
    Commented Mar 22, 2016 at 19:50

2 Answers 2

Reset to default
5

First a quick note on moving to SIMPLE recovery. This is a business decision. It is not in any way a technical decision. Your business needs to weigh the cost of staying in FULL (which may mean a new server with additional space, archiving current data to free up some space, etc) vs the cost of losing up to a day or more of data. Once they have decided then you can proceed.

Based on your question and the comments I see one of two scenarios:

You are in FULL recovery mode but are not taking log backups.

At this point you need to start taking log backups on a regular basis. In all probability this will actually save you space. If you take a log backup to clear the log less than once a day (Note: FULL backups do not release log space) then your transaction log is probably larger than it needs to be. Get your backups in order then figure out how big your transaction log actually needs to be. In all probability it will be quite a bit smaller than it currently is giving you additional space for backups. Worst case move your backups to tape more frequently than you currently are.

You are in FULL recovery mode but are taking log backups.

If this is the case then you may need to increase the frequency of your log backups. This will not incur additional space requirements since the log files will just be smaller (there is some small overhead for header info but it's not that significant). This will allow your log to clear faster and stay smaller. Assuming (and this is a big assumption) that you don't have one or more individual transactions that are requiring the log space. If that is the case then only changing your code to decrease the size of your transactions is going to let you get away with a smaller log file. Even if you switch to SIMPLE recovery you are still going to require log space for the largest single transaction (or largest combination of simultaneous transactions). If you shrink your log file and run a transaction that needs more space than is avialable it's just going to grow the log again.

Again switching between FULL and SIMPLE recovery is a business decision. Making that kind of decision without consulting the business (and then being wrong) is a good way to get yourself fired.

Improve this answer
1
  • Thanks that's some extensive information on logs, really what I was looking for. Will follow up here what I end up doing.
    – A_V
    Commented Mar 23, 2016 at 14:17
3

You are right, you cannot recover a database that in has a SIMPLE recovery model beyond the last full backup. Furthermore, you pointed out the reason you should have this DB in FULL recovery model: "it still has tons of users doing transactions so the logfile of the only database that is on this server fills the data drive everyday." Do you really want to lose the data that tons of users rely on?

No.

Try transaction log backups every hour (or 15 minutes if you can afford the space) and save the .trn files on the backup drive. Set up a cleanup task to delete the .trn files every few days if you take a daily full backup. Problem solved.

Here's another reason you should stay in FULL recovery model: Imagine that a new employee drops critical_table in the database at 12:35pm and the application using the DB goes down. You could take a transaction log backup here, restore the full backup to a new database**, and then restore the .trn with a stopat time of 12:34pm and recover the data!

**if the log chain is broken between the last full backup and the tlog backup you just created, you'd be boned, so don't restore over the old database until you are sure you can restore both together.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.