{"id":35776,"date":"2024-12-10T10:54:39","date_gmt":"2024-12-10T09:54:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=35776"},"modified":"2024-12-16T10:02:41","modified_gmt":"2024-12-16T09:02:41","slug":"help-yourself-out-with-the-log_reuse_wait_desc-column","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/help-yourself-out-with-the-log_reuse_wait_desc-column\/","title":{"rendered":"Help yourself out with the ‘log_reuse_wait_desc’ column"},"content":{"rendered":"\n

A few days ago a customer called me to ask if I could help him with a problem with the log file.<\/p>\n\n\n\n

He tried to shrink it, but the system wasn’t doing anything and he didn’t know why.<\/p>\n\n\n\n

With the column ‘log_reuse_wait_desc’ you can find it out.<\/h2>\n\n\n\n

When I asked him what was in the ‘log_reuse_wait_desc’ column for the database, there was silence at first, then the question quickly arose as to where the column could be found at all.<\/p>\n\n\n\n

But let’s start at the beginning.<\/p>\n\n\n\n

I arranged a session with the customer and asked him to show me what he had in mind.<\/p>\n\n\n\n

He tried to reduce the log file to the smallest possible size with the following command:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The output is successfull and nothing has happened.<\/p>\n\n\n\n

But why?<\/p>\n\n\n\n

I quickly found out that this was a database located in an AlwaysOn group.<\/p>\n\n\n\n

Some time before his call, an application had made large changes to the database by mistake. This caused the log file to grow enormously. From there also the desire to restore the old size.<\/p>\n\n\n\n

In the well-known system table sys.databases there is a column with the name ‘log_reuse_wait_desc’ which tells us why it does not work.<\/p>\n\n\n\n

So I executed a query based on the name of the database and the column:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The reuse_wait ‘AVAILABILITY_REPLICA’ was recognizable.<\/p>\n\n\n\n

This means that the log file is replicating the transactions that have been carried out. Until this has happened, the log file cannot be manipulated.<\/p>\n\n\n\n

We therefore had to wait until all transactions had been replicated and the status had changed:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

On the status ‘ACTIVE_TRANSACTION’, we are already able to shrink the previous enlargement.<\/p>\n\n\n\n

Then we could reduce the size of the file.<\/p>\n\n\n\n

Are there any other log waits?<\/p>\n\n\n\n

The following are listed in the official Microsoft documentation:<\/p>\n\n\n\n