0

I have configured log shipping in sql server. During logshipping the size of log file increased. And I need to shrink log file. for that I have to change database to simple. when I will change the recovery mode from Full to simple chance of break of log shipping will be maximum as I have read in Microsoft guide.

Is it possible to shrink log file in database when it is being used for log shipping?

Sunil Nepali
  • 141
  • 3
  • 18

2 Answers2

5

Assuming you have taken log backups since the log file grew, you should be able to shrink the file without changing recovery models.

But you absolutely, 100% have to understand that shrinking the log file is an absolutely useless activity if the log file is going to grow again. Let it stay as big as it's going to get during normal operations, because shrinking it to free space temporarily doesn't gain you anything. What will you use that space for today when you know that tomorrow the log file will grow again? This is like watering the lawn right before a thunderstorm. Perhaps you are not backing up the log often enough? Doing it more often

Please read this in full before proceeding. And I really mean in full. Like, the whole thing.

Now, if you have read that entire page, and are absolutely, 100% sure that you need to shrink the log file, and that it is a worthwhile activity because you know the log file won't grow again, then:

USE your_database;
GO
DBCC SHRINKFILE(your_database_log, 100); -- hypothetical, target size is in MB
GO

If you don't know that the log file won't grow again, put the mouse down and leave the log file alone.

Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
2

Second to what Aaron mentioned.

You can use DBBCC shrinkfile command for database in logshipping but make sure you dont use TRUNCATEONLY option. If you use this option Logshipping would eventually break. Its better to use NOTRUNCATE option when shrinking

Its quite common scenario for log file to grow because of some huge index rebuild or huge delete operation. In such case log file increased because it required space to log information. If you shrink it, again it would grow next weekend when the delete operation or index rebuild job runs. So whats the gain in shrinking ?

Instant file initialization is not there for log files( it works little differently for Tempdb log file). When information on log file is being written space is zeroed out first and then information is written this could be a performance bottleneck because whole operation has to wait for information to be written in log file first.

More about Instant File Initilization

Shanky
  • 19,148
  • 4
  • 37
  • 58