A nice little error log feature that I noticed in SQL Server 2016 regarding tempdb.

Tempdb, a system database in SQL Server, some call it the “public toilet” but honestly it is where temporary user objects are created, internal objects are created by the database engine and row versioning relies upon it.

What I like about SQL Server 2016 is that it watches out for a bad configuration, if you are new to the SQL Server world then a bad tempdb configuration is quite possible but you will be told quite clearly about it within the error log.

I setup a bad configuration just to prove the point.

The image shows all sorts of mess here. First I have 3 data files with different initial sizes and a mixture of percentage based auto growth and X_MB increments.  This totally goes against common advice of using correct initial /growth sizes and the number of files is just random for a 4 core system.

With that being said, check the error log:

The tempdb database has 3 data file(s). The tempdb database data files are not configured with the same initial size and autogrowth settings. To reduce potential allocation contention, the initial size and autogrowth of the files should be same.

What happens if I set a more sensible setting? There will be no warning element to the message.

Starting up database ‘tempdb’. The tempdb database has 2 data file(s).

Slightly more respectable I guess. Thanks Microsoft for watching my back.