Alwayson hadr

Question

Alwayson hadr

Laxmi 61

Whether we can shrink log file in always on?if yes then how to check logs are committed in standy server before shrinking?

Andreas Kreuzberg 96 Reputation points

2022-02-24T11:00:27.733+00:00

HI,
how big is your database, how big is your logfile

Are there many transactions/sec on your database?

How much space would you win, if you shrink the logfile?

And, what was the root cause, that the logfile would be so big?

How many vlf have you got in your logfile?

Kind regards,

Andreas

Answer accepted by question author

Seeya Xi-MSFT 16,756

Hi @Laxmi ,

First of all, there must be a full backup in Alwayson. Unlike the simple recovery model, the system does not automatic truncate the transaction log after each transaction. For databases with a full or bulk log recovery model, the system truncates the transaction log only after a successful backup of the transaction log.
Some people choose the following: Instance out of availability group - Modify to simple recovery mode - Modify back to full recovery mode - Full backup - Add back to availability group - Restore the secondary copy (note that it is WITH NORECOVERY) - Restart synchronization. I think it is also feasible except it is tedious.
For databases with frequent transaction log growth, you can establish a maintenance plan and back up the transaction log periodically, from a few minutes to an hour.
It is not recommended to shrink the database, because it will still grow after you finish shrinking it.

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Laxmi 61 Reputation points

2022-02-24T10:43:38.91+00:00

Removing from always on and adding it to AOG is not possible in production without downtime. In that case , even t-log backup is scheduled for every 1 hour... In that case how to check whether transaction is committed in standby server? Is there any way to check?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-02-24T22:32:37.967+00:00

Is the secondary set up as synchronous or asynchronous?

If it is synchronous, the transaction will not commit, until it has been written to the transaction log on the secondary. Which is all you need to know. At some point (supposedly quite directly), the redo process will apply the change to the data pages.

If the replica is asynchronous, there may be transactions that have not been written to the secondary. I would expect that if you check log_reuse_desc in sys.databases directly after having backed up the transaction log, it would read AVAILABILITY_REPLICA if there transactions that have been shipped to the secondary.

Not that I am really sure why you would care. When you shrink the log, you are only removing unused space in the log, and transactions not written to the secondary are not unused space, so they will not be lost.

Then, I don't know why you think you should shrink the transaction login in the first place. Most likely, you shouldn't.
Laxmi 61 Reputation points

2022-02-25T11:45:18.923+00:00

Thanks @ Erland. I was just questioned ,by one of collegue whether transactions are comitted Or not how you done shrinking in always on?

1 additional answer

Your answer

Andreas Kreuzberg 96 Reputation points

2022-02-24T11:00:27.733+00:00

HI,
how big is your database, how big is your logfile

Are there many transactions/sec on your database?

How much space would you win, if you shrink the logfile?

And, what was the root cause, that the logfile would be so big?

How many vlf have you got in your logfile?

Kind regards,

Andreas
Laxmi 61 Reputation points

2022-02-24T10:43:38.91+00:00

Removing from always on and adding it to AOG is not possible in production without downtime. In that case , even t-log backup is scheduled for every 1 hour... In that case how to check whether transaction is committed in standby server? Is there any way to check?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-02-24T22:32:37.967+00:00

Is the secondary set up as synchronous or asynchronous?

If it is synchronous, the transaction will not commit, until it has been written to the transaction log on the secondary. Which is all you need to know. At some point (supposedly quite directly), the redo process will apply the change to the data pages.

If the replica is asynchronous, there may be transactions that have not been written to the secondary. I would expect that if you check log_reuse_desc in sys.databases directly after having backed up the transaction log, it would read AVAILABILITY_REPLICA if there transactions that have been shipped to the secondary.

Not that I am really sure why you would care. When you shrink the log, you are only removing unused space in the log, and transactions not written to the secondary are not unused space, so they will not be lost.

Then, I don't know why you think you should shrink the transaction login in the first place. Most likely, you shouldn't.
Laxmi 61 Reputation points

2022-02-25T11:45:18.923+00:00

Thanks @ Erland. I was just questioned ,by one of collegue whether transactions are comitted Or not how you done shrinking in always on?

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

As far as I know, you can, but why would you? Shrinking the transaction log is a very exceptional thing to do.

Laxmi 61 Reputation points

2022-03-02T17:58:36.467+00:00

Yes.. We after scheduled tlog for every 1 hour logs are growing so we are receiving drive space alert , there only residing log files of all user databases. To reduce physically we are shrinking virtual log files
Tom Phillips 17,786 Reputation points

2022-03-02T19:35:07.397+00:00

The logs are as big as they need to be. Shrinking the logs is just causing them to grow again. It sounds more like you need more disk space for your usage of the server.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-03-02T21:59:27.583+00:00

Yes.. We after scheduled tlog for every 1 hour logs are growing so we are receiving drive space alert , there only residing log files of all user databases. To reduce physically we are shrinking virtual log files

What you shrink will grow again, unless you take measures to prevent the growing. In this case, you should probably increase the frequency of the log backup to be every ten minutes. Then again, the amount of log records is not evenly distributed within an hour, so you probably still need to get more disk space, so that you don't get an outage because of a full disk.

And shrinking is not going to help you!

Share via

Alwayson hadr

1 additional answer

And shrinking is not going to help you!

Your answer