Is Trace Flag 1117 enabled in Azure Managed Instance?

-> The list of trace flags enabled by default in an Azure Managed Instance doesn’t contain trace flag 1117.

-> The official documentation states that “A Managed Instance runs with all of the features of the most recent version of SQL Server, including online operations, automatic plan corrections, and other enterprise performance enhancements.”. This advises us that features related to trace flag 1117 should be enabled by default even though the trace flag is not explicitly enabled.

-> Lets check if this is actually true!

-> Managed instance that I am testing has below Tempdb files,

Blog42_1.PNG

-> I am inserting some data into a temporary table and check if all the tempdb files grow,

Blog42_2.PNG

-> It is pretty clear that all the tempdb files have grown. Lets check the SQL Server error log to see if there are any messages related to Tempdb autogrow,

Blog42_3.PNG

-> The messages recorded above shows us that autogrow has happened.

-> This proves that the features related to trace flag 1117 are enabled in the managed instance and it is not required for us to enable it explicitly.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s