Autogrow All Files Feature in SSMS 18.0 Preview 4

One of the small improvements in SSMS 18.0 Preview 4 is the addition of an “Autogrow All Files” checkbox in the Database Properties dialog on Filegroups page as shown in Figure 1. This is another nice change that will hopefully make it easier for more people to enable this setting because the tooling actually makes it easier to do.

Checking that checkbox ends up generating the following command:

ALTER DATABASE [Test2019DB] MODIFY FILEGROUP [MAIN] AUTOGROW_ALL_FILES

This enables the same behavior for user databases as the old Trace Flag 1117, but controllable at the individual database level. This setting is explained in more detail in this Microsoft blog post:

SQL Server 2016: Changes in default behavior for autogrow and allocations for tempdb and user databases

If you have multiple files in a file group (and I think having at least two is a good idea), it is important to have them be the same size, so that SQL Server will spread the work more evenly between the files. If you have one large file and one small file, the small file tends to get ignored.


image

Figure 1: Database Properties – Filegroups Page

You can check the value of this setting for the current database with this query:

SELECT name, is_autogrow_all_files
FROM sys.filegroups;

Backup Checksum Feature in SSMS 18.0 Preview 4

I was pleasantly surprised to see a new “Backup checksum” checkbox in the Server Properties, Database Settings dialog page (as shown in Figure 1). This was something that I had been asking for for quite awhile, so I appreciate that it has been added to SSMS.

This simply provides tool support for the backup checksum default sp_configure option that was added in SQL Server 2014. This just adds the CHECKSUM keyword to your backup commands by default (in case you forgot to add it or some non-native backup solution is being used). You can override this default by specifying WITH NO_CHECKSUM in a backup command.

This gives you a little bit of extra protection for your database backups, since the backup operation verifies each page for checksum and torn page, and generates a checksum for the entire backup. This is not a substitute for actually restoring you database backups on a regular basis, but it is a useful extra step to help ensure that your backups are actually good.

Just to be clear, this is not new functionality, it just adds tool support in SSMS, so you don’t have to use T-SQL to enable this setting. This makes it more likely that more people will start using backup checksums, which is a good thing.

Microsoft has some more information about backup checksums:

Enable or Disable Backup Checksums During Backup or Restore (SQL Server)

How to enable the CHECKSUM option if backup utilities do not expose the option

Older versions of SQL Server can use trace flag 3023 to get the same effect.

image

Figure 1: SSMS 18.0 Server Properties, Database Settings Dialog

Recent SQL Server Software Updates

The third week of March 2018 has seen a plethora of SQL Server related software updates from Microsoft. These include:

SQL Server 2017 CU5

SQL Server 2016 SP1 CU8

SQL Server 2014 SP2 CU11

This list covers all of the currently supported branches of these major versions of SQL Server, meaning that if you are on a different branch of one of these versions (such as SQL Server 2014 SP1 for example), you are not on a supported branch and there won’t be anymore Cumulative Updates for that branch

We also saw a new minor release of SQL Server Management Studio (SSMS). The main purpose of this release was to add support for SQL Database Managed Instance, along with some bug fixes in other areas.

SSMS 17.6