Can Shrinking of logfile causing query to run slow?

Avyayah 1,271 Reputation points
2022-04-12T22:22:25.613+00:00

Can shrinking of logfile cause the query to run slow? Transactional logfile grew in size and logfile was shrinked. Will this cause issue for the query to run slow? What query can I run if shrinking caused any issue?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,080 questions
0 comments No comments
{count} votes

Accepted answer
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2022-04-13T05:48:19.393+00:00

    Hi SahaSaha-5270,

    Will this cause issue for the query to run slow?

    Shrinking the log file may cause performance issues. Before shrinking the transaction log, please check Factors that can delay log truncation. If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations.
    And please refer to Manage the size of the transaction log file which might be helpful.

    Best Regards,
    Amelia


    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.


2 additional answers

Sort by: Most helpful
  1. Olaf Helper 42,386 Reputation points
    2022-04-13T05:56:37.913+00:00

    Shrinking a log file will cause I/O traffic and the later occuring auto growth will also cause I/O traffic; both will slow down the system.

    0 comments No comments

  2. Erland Sommarskog 103.5K Reputation points MVP
    2022-04-13T21:54:43.58+00:00

    Yes, shrinking the log in error can slow things down. Recall that when the log grows, the newly allocated area must be zeroed out.: Writing all these zeroes takes resources. And the query that triggers autogrow will be stalled.

    Thus, you should be very restricting of shrink database files. Shrinking is only something you should do if there has been exceptional event. For instance, someone left a transaction open in an SSMS window for three days. But if you don't know why the log grow, you should absolutely not shrink, but assume that it is simply due to the workload.