transaction log usage

Heisenberg 261 Reputation points
2021-10-21T21:56:45.9+00:00

hello,
We are running out of disk space on a d:\ drive on a server and we cant grow it much.
We have another drives on these servers where we have enough space. On the current d:\ drive we have tran. logs and data files. If i shrink current tran. log to 10% and set autogrow off and create another transaction log to the new drive where we have enough space , will this solution work? my concern is since i set autogrow off for 1 tran. log file any long running transaction should not fail.

PS, this database is part of always on

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,629 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-10-22T03:30:15.497+00:00

    Hi SQLServerBro,

    If i shrink current tran. log to 10% and set autogrow off and create another transaction log to the new drive where we have enough space , will this solution work? my concern is since i set autogrow off for 1 tran. log file any long running transaction should not fail.

    Yes, it will work. The transaction log files do not use proportional fill like data files, once the primary log file becomes full, it will move to the next log file. Please refer to this article which might be helpful.

    PS, this database is part of always on

    If you are adding a log file to the Primary Replica, the add file command will also be executed on the secondary replicas. If you have not placed your database files for your Primary and Secondary replica database on an identical path, then SQL Server will not be able to create that file on the Secondary Replica server. So Please make sure the drive do exist on the Secondary replica. Please check this blog.

    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.

    0 comments
  2. Heisenberg 261 Reputation points
    2021-10-22T16:06:07.21+00:00

    thank you for your response, what if the same situation occurs for data files?

    Ex:
    data file on drive is D:\ is getting full and i can not expand d:\ drive, then in that case can i set autogrow off for this data file and create another data file in the same filegroup on new drive?