Problem
A user with db_owner access was trying to run the Database Tuning Advisor (DTA) on a newly built SQL Server, unfortunately he was getting this error message: “Failed to open a new connection. In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (DTAClient).” Do you have any suggestions on how to solve this issue? Check out this tip to learn more.
Solution
This error, “Failed to open a new connection. In order to start tuning application needs to create some objects in MSDB database. You have to be a member of sysadmin fixed server role in order to initialize MSDB for tuning (DTAClient)”, occurs when the Database Tuning Advisor (DTA) is not initialized on that instance by a SQL Server System Administrator the first time the application is launched. Luckily, if the DTA has already been run on an instance by a SQL Server System Administrator you will not face this issue.
Just to take a step back, the Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server. This is achieved by the Database Engine Tuning Advisor analyzing a workload and the physical implementation of one or more databases. A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor uses trace files, trace tables, or Transact-SQL scripts as workload input when tuning databases.
After analyzing a workload, the Database Engine Tuning Advisor can recommend adding, removing or modifying the physical design structures in your databases. The advisor can also recommend what statistics should be collected to improve the physical design structures. The physical design structures include clustered indexes, nonclustered indexes, indexed views, and partitions. The Database Engine Tuning Advisor is intended to recommend a set of physical design structures that reduces the query optimizers estimated cost of the workload.
On first use, the Database Engine Tuning Advisor must be started by a user with system administrator permissions to initialize the application. After initialization, both members of the sysadmin fixed server role and members of the db_owner fixed database role can use Database Engine Tuning Advisor to tune databases. Keep in mind however, that the db_owner role members can only tune databases that they own.
To initialize Database Engine Tuning Advisor, a user with SQL Server System Administrator (sa) permissions performs either of the following actions:
- Use the Database Engine Tuning Advisor graphical user interface to connect to an instance of Microsoft SQL Server 2005 or later
- Use the dta utility to tune the first workload
SQL Server Database Engine Tuning Advisor Objects in the MSDB Database
Below is a screen shot of the objects created in the MSDB database by the SQL Server Database Engine Tuning Advisor:
Next Steps
- If you are not a SQL Server System Administrator, be sure to have some who is initialize DTA before running the application.
- Be sure to completely understand the requirements from the DTA and thoroughly test the requirements prior to loading the scripts in a production environment.
- CCheck out these related tips:
