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?