How to work with the ldf as it’s size is almost triple the size of the attachments (90GB),

SQL Server Administration
1

how to work with the ldf as it’s size is almost triple the size of the attachments (90GB)?

image
image1376×691 116 KB

2

Back up your transaction log regularly. At least every hour.

If you do not need to be able to restore that database to a specific point in time (for example, just before someone accidentally drops a table), then you can change your database to SIMPLE recovery model. This will clear and reuse the log after every checkpoint operation.

1 Like
3

After you have done what @robert_volk has recommended - then you can perform a one-time shrink of the transaction log file to get it back to a more manageable size.

To do that - you may have to perform the shrink file operation several times. If you setup and configure log backups, then you would perform a shrinkfile, log backup, shrinkfile (repeat until file is down to the size you want). If you switch to simple recovery, then - shrinkfile, checkpoint, shrinkfile.

DBCC SHRINKFILE(2, 1024);
CHECKPOINT;
--LOG BACKUP

Additionally, you want to make sure you set an appropriate size for the log file to grow. If it is set to a percentage - that is not a recommended value. Change it to a fixed size - something like 512MB would be a good starting point. If you do that - then shrink the file to less than that size and manually grow it to that size - then when it does have to grow it will grow in 512MB increments.

1 Like
4

@jeffw8713 ... You forgot to follow that up with a "here's how to get back to the FULL Recovery Model". Remember that the OP might not actually have a clue about that considering the question they asked.

1 Like
5

Actually - I meant to say 'If your database is in simple recovery'. But you are correct - if you switch to simple to be able to clear the transaction log and then decide to switch back to full, then you need to also perform a full backup after switching the recovery model.

Also - @robert_volk suggested a switch to simple if you do not need to be able to restore to a point in time.

To be absolutely clear - unless you are 100% sure you don't need point in time recovery then you should keep the database in full recovery model and perform frequent transaction log backups. The only time it would be okay to switch to simple and back to full - is if you have not been taking transaction log backups at all and that is the only option available - and you understand that you will not be able to recovery across that action (i.e. use a full backup prior to switching - and transaction logs past that).

2 Likes