0

i have a .NET program request the MSSQL database, the request contains large amount of deletion and insertion.

In such case, there is a potential that the log would grow as the transaction log space is not enough.

And it is found out that the event of growing transaction log take long time to do.

I want to know if the time required for this event will trigger the database time out or not?

Or the auto grow event will not affect the regular time out setup of the database?

1
  • "the event of growing transaction log take long time to do", growing the transaction log as adding MB to the file is fast. The problem is the operation(s) that are making the transaction log grow outside it's max size. What do you mean with "database time out"?
    – EzLo
    Mar 28, 2019 at 11:31

2 Answers 2

0

Growing the logfile (as well as database files) freezes the database operation for a short time. It is especially bad if it is needed repeatingly.

A good initial size and a big grow factor can reduce the number of resizes. What you also should avoid is shrinking the log because it will just grow again. The initial size of the log should cover all changes likely to happen within the backup period.

Timeout per se is not expected to happen by this, but of course if an application sets very low timeouts it will experience them. However if you work with small timeouts you should also implement proper retries since you are dealing with a enterprise software stack which has certainly no RT guarantees (and therefore timeouts will happen even without a log resize)

For database files (but not for logfiles) IFI (instant data file initialisation) can reduce the time to extend a data file. It requires SQL Server to be started with special volume management privileges.

2
  • is it correct for my understand? grow factors means log is full, then it grows the size equals to current size * grow factor. is it correct? if it is the case, increase grow factor means "potentially decrease times of grow", "increase the duration of grow event"
    – SKLTFZ
    Mar 29, 2019 at 3:01
  • Yes, I typically not use a factor but a constant large size like 100MB or so. It reduces number of small grows with fragmentation in the beginning and makes sure effort stays predictable.
    – eckes
    Mar 29, 2019 at 3:05
0

With the log size and growth values in your other questions, I would recommend you read the article at https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Also the additional links within that. It could be that you are experiencing issues with too many VLFs. Quoting from the article "Having an excessive number of VLFs can negatively impact all transaction log related activities, especially around restores and crash recovery."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.