question

SaiVijaya avatar image
SaiVijaya asked

Shrink a database log file which is synchronized (Always on Group)

How to shrink a database log file which is synchronized(Always on Group).

SQL version: SQL server 2017 Enterprise edition

Preforming log backups every 30 mins and full backup every day night.

But didn't see any improvement from 1 week.

When I checked with developers they told that they were creating and deleting lots of tables weekly and asking us to delete logs during the maintenance.

Can I ask them, Delete large number of rows and tables without growing the transaction log?

Will it help instead of planning to shrink database log file regularly?

This database is in always on group and with Full recovery model.


Please suggest.

sql2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered

There is no concept of a incremental backup in SQL.


You have FULL, DIFF and LOG


If you are not doing LOG backups you need to start doing so urgently. Backing up the log is paramount in FULL RECOVERY model and will mark the log as reusable allowing you to shrink it as a 1 time only task if it has grown out of control.

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, we are taking log backup every 30 mins.

I checked with developers, They create and delete lots of tables weekly. They were asking us to delete logs during the maintenance.

Can I ask them, Delete large number of rows and tables without growing the transaction log?

Will it help instead of planning to shrink database log file regularly?

This database is in always on group and with Full recovery model.

0 Likes 0 ·

Do not delete the transaction logs. They are your means of restoration.

If they must delete so much data all the time you are going to need to live with the fact your going to need a large transaction log.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.