question

Heisenberg avatar image
0 Votes"
Heisenberg asked ErlandSommarskog commented

transaction log usage

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi SQLServerBro,

Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.

Best Regards,
Amelia

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered ErlandSommarskog commented

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?

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You can, but it is not really the same thing, because log and data files are written to in completely different ways.

SQL Server writes to a log file sequentially. This means that if you add a second file, you can easily get rid of it, once you have added more disk space to the original drive. (Which in many cases is an easy thing, if it is a VM, the disk comes from one big fat SAN.) You only wait until the active portion of the log is back on the original file. Or drop the original log file if the entire active portion of the log is one the new file. (Although, I am not 100 per cent sure that you can drop the original log file.)

Writes to data files, on the other hand, are all over the place, since the tables can be both here and there. And if you have multiple data files, SQL Server writes to the files in a round-robin fashion, and ideally when you have multiple data files, they should all be of the same size, so that they will grow in parallel..

Thus, while you can apply the same solution to the data file, it is less desirable, and it will be more work to rectify the situation, and in the end you may be living with this imperfect makeshift solution for years. So it may be better to accept some downtime and move the existing data file to the new drive.

0 Votes 0 ·

Thank you @ErlandSommarskog and @Ameliagu-msft. One more question if i apply this solution of restricting existing data file not to autogrow and add another data file on new drive, will it have any performance impact ?

0 Votes 0 ·

To be honest, I don't know. I don't directly see why there would be, but I can guess that there is a lot "it depends". And, as I said, this is a situation I would try to avoid.

1 Vote 1 ·

Hi SQLServerBro,

Thanks for your reply.
I agree with Erland.
And if you want to add data file to primary database in alwayson, it is still necessary to ensure that the drive exists on the secondary replica.

Best Regards,
Amelia

0 Votes 0 ·