SQL Server : huge log file, we know why, but what can we do about it

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?

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:

-
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.

-
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.

StackExchange Database Administrators Q#175865, answer score: 6