In SQL Server 2022, set your transaction log file autogrowth rate to 64 MB

A late-breaking change that arrived in SQL Server 2022 is an important one that could be a new line item in your health checks.

The basics:

Starting with SQL Server 2022, transaction log file growth events up to 64 MB in size can benefit from instant file initialization (IFI). As usual, the transaction log is otherwise unable to benefit from instant file initialization. 

This should be a big performance improvement if your transaction log files unexpectedly grow. Of course, you should try to avoid autogrowth events altogether. 

There's lots more cool new stuff in SQL Server 2022, too.

Impact to health checks:

The default autogrowth rate for transaction log files in new databases is already 64 MB. Unless you have specific needs for your workload, consider keeping this default now, considering the dramatic advantage IFI brings to file space allocation.

In both your database and log files you should proactively pre-allocate enough space to avoid autogrowth events anyway. Regularly monitor the space in database files to prevent autogrowth events. The proactive DBA, outside of busy hours, should grow database data and log files manually, so that they do not autogrow during busy hours. 

And whatever you do, don't shrink your data and log files, only to have them autogrow again the next business cycle.

Details:

• Applies to both manual file size changes and autogrowth events <=64 MB.
• Transaction log growth events larger than 64 MB are unaffected by this performance improvement. No change.
• Still no IFI for database restores or initial transaction log file initialization for new databases.
• This change to autogrowth events up to 64 MB is new for SQL Server 2022, and arrives Azure SQL Database soon if not already, from what I understand. 
• It is not yet available for Azure SQL Managed Instance, but I would guess it will be there soon.
• This feature is available on all editions of SQL Server.

Make sure IFI is enabled:

Of course, this only works if your SQL Server instance is capable of instant file initialization. The SQL Server service account must be granted the Perform Volume Maintenance Tasks permission in Local Security Policy on the Windows server. A quick script works for modern versions of SQL Server: 

SELECT servicename, instant_file_initialization_enabled

FROM sys.dm_server_services

WHERE filename LIKE '%sqlservr.exe%';

Potential Backup Software Issues with Read-Only Databases in FULL Recovery

If you decide to park a database in read-only mode for a long period of time, go ahead and alter it into SIMPLE recovery mode too.

The message returned by SQL when you try to take a transaction log backup is:
 "BACKUP LOG cannot modify the database because the database is read-only. The backup will continue, although subsequent backups will duplicate the work of this backup."
 which is fairly self-explanatory to a human.

But to an automated system, such as a third-party enterprise backup platform, this message is not always interpreted correctly.

 eVault, for example, does not detect the read-only condition or the message from SQL and instead returns this generic error message:

 "A gap was detected in log files for database foo. You will need to re-run a Full backup."

Which is misleading and incorrect and pointed troubleshooting efforts of backup administrators in the wrong direction.

Actual Emails: The Customer Doesn't Want Full Recovery Mode

Got this question in an email.  I'll paraphrase.

William 

Hoping you can advise me on best way to handle this. 

My client doesn’t want to use SQL backups. Instead, they are using VM and a third party backup software by choice.

Without a DBA on staff, they would prefer to deal with SQL the less the better.

They do not plan on doing any transaction logs and are not concerned with restore from point of failure.

... 

Goal would be to not have transaction logs or somehow keep those logs small.   

Colleague

Here's how I replied.

So, the first question to ask a client who doesn't want transaction log backups is, "why?" 

The transaction log is not something to be afraid of. Backing up the transaction log is a simple process and clears out the log. Backup the log at regular intervals (somewhere between 15 and 60min is typical). Then, you get all the advantages of point-in-time recovery, so if you have a 5:01pm disaster, you could restore all the way to 5:00pm, for example. Plus, full recovery mode is required for most SQL-based HA/DR scenarios.

Using a third party backup software is fine, but they must have purchased the SQL plugin.  This is usually an extra cost add-on for whatever backup license they bought.  You CANNOT backup an .mdf file with a file system backup and expect that to work. SQL MUST BACKUP SQL. 

That SQL plugin does exactly that for the third party software - sends commands to SQL to perform a backup. It's fine if they don't want to use sql maintenance plans or jobs to perform the backups, but they must use the sql plugin for any 3rd party enterprise backup software.

With a database in full recovery mode, you must perform full and transaction log backups, or the log will grow.

Typically, you do a nightly full and hourly tran log backups. The reason people get scared/confused/frustrated with transaction logs is that they don't realize you are supposed to back up the log (which empties it) regularly. As in, hourly at most. If they want the log to stay small, back it up every 15 minutes. BackupExec can do that. 

If they are really stubborn about not wanting to use all the DR capability that they paid for, putting the database in simple mode is fine. Just full sql backups are needed then. In simple mode, transactions aren't stored in the log after they commit. A transaction commits, there's a checkpoint, and then it is wiped from the log. A simple recovery mode database will have its transaction log grow to the size of the biggest single concurrent set of transactions at any given time. 

Never truncate a log file. Perform a log file backup, which will empty the file. The file may still be large though it is empty, in this case if you absolutely need the space back, you can shrink the log file. Never shrink a data or log file on a schedule. It creates fragmentation which hurts performance. 

Let me know if you have any questions! 

-William