Starting with Dynamics NAV 4.0 SP3 build 24080, all builds of NAV (SQL option) have a prerequisite that SQL Server trace flag 4616 must be turned on. This was initially required when running NAV with SQL Server 2005 but subsequently was also required for SQL Server 2008 when that became a supported database platform.
Why did we need this trace flag? SQL Server 2005 introduced increased security protection for database metadata which means that visibility is restricted by default for users in such a way that they can no longer see connection details for other logins unless the user is granted “VIEW SERVER STATE” permissions. However, Dynamics NAV licensing functionality and the client feature for displaying database sessions both required that users must have visibility to all active connection details. SQL Server trace flag 4616 causes SQL Server 2005 and 2008 to behave in the same way as SQL Server 2000 with respect to metadata visibility. Therefore, when support for SQL Server 2005 was added to the Dynamics NAV product the requirement to turn on trace flag 4616 was also introduced.
Why do we no longer need this trace flag? With the release of Dynamics NAV 5.0 SP1 (Update 2 build 30488 or later) and Dynamics NAV 2009 SP1, trace flag 4616 is no longer required. These versions of the product now uses “VIEW SERVER STATE” permissions to enable visibility to the required connection information for each user.
For customers who have been using SQL Server trace flag 4616 for older versions of Dynamics NAV Microsoft recommends disabling this trace flag after upgrading to either Dynamics NAV 5.0 SP1 (Update 2 build 30488 or later) or Dynamics NAV 2009 SP1. Most environments will have this trace flag added to the startup parameters for SQL Server so the following steps can be used to remove it as required:
1. Go to Start-> Run and select the "Microsoft SQL Server 2005/2008" program group, select the "Configuration Tools" folder and then the "SQL Server Configuration Manager" tool.
2. Double-click on the SQL Server service.
3. Select the "Advanced" tab
4. In the list of values double-click on the one called "Startup Parameters"
5. In the list of values for this parameter (normally near the end), remove the following: ';-T4616'
6. Click OK to save the change.
7. Restart the SQL Server service
Before removing trace flag 4616 from your SQL Server environment please verify that no other database applications are depending on this feature.
In some rare cases the trace flag may be enabled on SQL Server using some other means (e.g. DBCC TRACEON command). Consult your SQL Server database administrator for advice in these scenarios.
Microsoft Dynamics NAV Support, UK
5. In the list of values for this parameter (normally near the end), remove the following: ';T4616'
Great article, you can also consider checking this list of the most important trace flags for SQL Server, I personally have this in my bookmarks as reference sqlturbo.com/the-most-important-trace-flags-for-sql-server