1

I have an SQL Server database in Full recovery model. The data file is just 1 GB in size, but the log file is 20 GB.

I have a space problem, so I took a full backup and then a log backup with truncate option. Is correct now to shrink the log file? Does anything speak against doing that?

This is a production DB that is supposed to be in full recovery model. Til now no one implemented a backup strategy. The log became very big and they are running out of space. I took a full backup and then a log backup with truncate option using SSMS, in order to avoid the log file increase further. Now I thought that I can schedule daily a full backup and every hour a log backup to avoid that the log file increase over 2Gb. I'm not sure if after I took full + log backup is it possible to shrink the log?

The idea is to reduce the log file from 20Gb to 1 or 2 Gb because the server is running out of space and then take a daily full backup and every hour a log backup to avoid the log file increase too much.

0

1 Answer 1

2

You should be ok, transaction log backup will allow a point in time recovery. You can shrink the tlog file without any issues. Since you already took the tlog backup with truncate, the log space should be empty (unless there's something like replication, log shipping, etc) and you should be able to shrink the tlog.

After that, you can continue with your normal schedule of full backup and tlog backups.

The only situation when tlog shrink is not recommended is when you already know the tlog will grow due the "normal" activity of your database. But from what you said, it should be ok to shrink it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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