You are in the middle of a SQL Tuning Advisor session and while the engine is analyzing the data the application suddenly stops working returning an error that says:

Tuning process exited unexpectedly (DTAEngine)

Depending on the workload the tool was supposed to analyze, this error could cost the DBA hours of work. Therefore, before even starting using this application, some preparation work is needed.

A reliable network connection

First of all, the DBA should make sure the application is running on a machine that is connected on a stable network: the Ethernet cable should be chosen over WiFi antennas. As a wireless connection might not be able to provide the Database Engine Tuning Advisor with an uninterrupted flow of data, the application might just stop working.

Updating the database server

Regardless the versions of the operating system and the SQL server that are hosting the DB, the DBA should always go through the Microsoft documentation in order to make sure all latest and required patches are correctly installed. In fact, SQL Server 2012 and SQL Server 2008 R2 require to be at the correct maintenance level in order to prevent the “Tuning process exited unexpectedly” error from happening, as described in the knowledge base article number KB2728419.

Splitting the work

Instead of trying to build the complete list of recommendations at once, the DBA might want to split the work targeting just a bunch of tables at a time. Should anything go wrong during the session, the administrator will not end up losing any work.

Managing DTA sessions

The DBA should make sure all DTA sessions are closed after they have completed their work. In fact, having multiple idle or inactive DTA sessions might cause the “Tuning process exited unexpectedly (DTAEngine)” error. Before starting the analysis, the DBA will need to kill all unnecessary sessions to prevent any issue with the DTA. Therefore, by running the following SQL query the DBA will get a list of all DTA sessions:

USE MSDB
EXECUTE dbo.sp_DTA_help_session

The DBA then will kill all unused sessions by running:

USE MSDB
EXECUTE dbo.sp_DTA_delete_session <session_id>

Conclusions

With the hope the reader found this article helpful, any feedback would be more than welcome as DBAs are encouraged to post about their own experiences with this useful tool.