sp_check: SQL Server Database Checks

Trace flag 1117

What's the issue?

Trace flag 1117 is not enabled globally. This trace flag is recommended for SQL Server 2014 and earlier.

Why is this a problem?

Enabling trace flag 1117 allows all files in a file group to grow at the same time. If you have set your tempdb files to grow equally, this means they will all grow when one grows, which should reduce the possibility of contention.

What should you do about this?

We recommend enabling trace flag 1117 for SQL Server 2014 and earlier. This trace flag is not needed in SQL Server 2016 or later because it is the default behavior. Just be aware this is an instance level setting that affects all databases, so you may not want to enable it if you do not want all files in the filegroups of your user databases to also grow simultaneously.

What do the Vulnerability Levels mean?

0 - Information only. This is stuff you should know about your instances like version and service account used, but if you don't know it…well, now you do.

1 - High vulnerability requiring action. These are the issues that could most likely lead to your company being front page news for all the wrong reasons. If your instances have any results at this level then we recommend cancelling that 3-martini lunch and instead huddling with your team to figure out when to address these issues.

2 - High vulnerability to review. These include settings and assigned permissions you should review soon, if not immediately. These findings may not necessarily indicate a clear vulnerability, but we've found unexpected vulnerabilities in these categories at many, many clients.

3 - Potential vulnerability to review. These are configurations or assigned permissions you may be using that could lead to problems for users. Or maybe they're just required for your applications. Either way, we recommend reviewing these to make sure these are correct.

4 – Low vulnerability with recommended action. These are typically security inconsistencies that should be addressed. They aren't likely to cause problems, but you should clean up the mess.