5

We have this SQL Server database that is fully backed up daily. Recently we noticed that the LOG file for this database is huge (> 50 GB). I did some research and I found out that it is because we weren't taking transaction logs.

So I know why my log is so big, but I'm wondering what I can do about it.

Clarification: we want to use the 'full' recovery option with regular transaction log backups. We realize we aren't right now and this will be fixed as soon as we can get the transaction log to a reasonable size.

The ideal route would be to take a log backup and then do a DBCC Shrink to get the file down to a normal size. However, if I do this on a full backup of the database, this takes a few seconds and creates a log backup of 5 GB and shrinks the transaction log way down to a few MB.

If I do this on the production environment, it takes really long and it seems to create a file far bigger. I estimate this will be about 50 GB. Is there anything I can do about this, because the server just doesn't have another 50 GB, so taking the log backup in this way isn't an option.

Since we weren't taking regular log backups, we only have one recovery point per day I suppose (the full backup taken every night), so is there a way to delete the log and free up the 50 GB of the log file in another way?

Or are there any other options you can think of that might help me in this situation?

  • What's the recovery model of your' db and if it's full why don't you take log backups regularly? If it's acceptable for you to loose 1 day of work, you should use simple recovery model – sepupic Jun 9 '17 at 8:50
  • Hi. That was a mistake of the past, where the recovery model was set to 'Full' but no backup logs were taken. This will be rectified as soon as we can get the logfile to a reasonable size – Steven Lemmens Jun 9 '17 at 9:04
  • 1
  • By that last sentence in the first paragraph ([...] because we weren't taking transaction logs): did you mean to say because we weren't taking transaction *log backups*? – TT. Apr 8 at 9:54
5

You've said that it is in FULL recovery mode currently, and you want to keep it that way, but you haven't said whether you might need to roll back to any point-in-time prior to today.

Here are your two choices:

  1. If there is any possibility you might need to restore to a past point-in-time, you need to fully back up the existing (huge) tran log (and keep the backup file). So go ahead and run that tran log backup. Yes, it will take a very long time, and yes, it will require a lot of disk space. But once that is done, each new tran log backup will be small and fast, and will only contain transactions from the last successfully log backup. You will be able to shrink the log down to something reasonable.

  2. If you don't anticipate ever needing to roll back to a prior point-in-time (like if a disaster happened, you'd just restore one of the prior nightly fulls anyway), and you only care about future possible point-in-time recoveries, then switch to SIMPLE, shrink the log, switch back to FULL, run a full backup, and start your log backups. This will clear the huge backlog of completed transactions.

To be crystal clear, both SIMPLE and FULL recovery mode would allow you to restore to any prior full (or differential) backups. FULL recovery mode allows you to restore to an intermediate point between two full backups.

So let's say your nightly full backups happen at 8pm, and your database was irretrievably corrupted or data was accidentally deleted at 10am the next day. With SIMPLE mode (or without available tran log backups), your only choice is to restore the 8pm full backup from the prior night. With FULL recovery mode (if you've taken and kept tran log backups), you could restore the database to immediately before the problem occurred, at 9:59:59am.

Of course if you would never need point-in-time recovery at all (it's a reporting or processing database where you could simply restore the full and then reimport/reprocess all subsequent data from other sources), then you could just switch to SIMPLE mode permanently and forget about log backups altogether (although SQL still uses the tran log for in-progress transactions, then marks the space for re-use when they are complete).

But this is a business decision, not a tech decision. Talk to your managers or business stakeholders.

1

If you only wish to backup once a day with a full backup.
Put the database into SIMPLE recovery mode and shrink the log file then let it in SIMPLE recovery mode.
This way the log file will be sized down.

USE [master]
GO
ALTER DATABASE [myDB] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (N'myDB_Log' , 0)
GO

Afterwards if you wish to take transactional backup,
just put it back to FULL recovery mode,
take a full (or a differencial) backup and start your transactional backups.

Also note that this won't work if you have mirroring or Always On enabled.
You may have to cancel the mirroring or pause it in order to achieve these operations.

  • Hi. I should have clarified (and I will edit my question). It was a mistake to not take transaction log backups. This will be rectified as soon as we can get the log file down to a reasonable size. So we don't want to use the 'simple' recovery mode, but want to use the full recovery mode – Steven Lemmens Jun 9 '17 at 9:21
  • FYI - After changing back to FULL, a differential backup is sufficient to restart the log chain and usually much faster than taking a full backup. That should minimize the amount of time you're exposed to not being able to recover to a point in time. sqlskills.com/blogs/paul/… – Scott Hodgin Jun 9 '17 at 9:23
  • I would also suggest taking a differential backup immediately before you switch to simple in your example. – Scott Hodgin Jun 9 '17 at 9:26
  • What is the advantage of the differential backup after switching back to full? So let's say I have a full backup which is taken early in the morning and I perform the switch to simple recovery mode and the shrink file and the return to full recovery mode in the evening. Will I still have the difference between how the database was in the morning and how it is now in the evening? – Steven Lemmens Jun 9 '17 at 9:29
  • 2
    So - you took a full backup in the morning and you did work during the day. Those transactions are in the current log. You switched to simple and truncated (shrunk) the log. You just lost those transaction in the log, but they are still in the database. Taking a differential after switching back to full ensures you get those 'missing' transactions. You also MUST take either a full or differential backup after switching from simple to full to restart your ability to take transaction log backups. – Scott Hodgin Jun 9 '17 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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