0

When running the Query Tuning Assistant (using the New Database Upgrade Session) on SQL Server 2017 I have a new schema msqta created in the corresponding database as supposed, along with six tables and five stored procedures in the schema.

After finishing the database upgrade and deleting all sessions in QTA, disabling the Query Store and purging the Query Store data (Purge Query Data button) the aforementioned schema and associated objects are still present in the database.

I did my google searching and reading but was unable to find an option or a command to remove the msqta schema and the associated objects. I guess these objects are supposed to be removed automatically in some sort, when not needed. It is a little bit annoying having the tables in every one of my production databases.

On a test server, I tried to remove the schema and the objects manually, first deleting the tables and the stored procedures and then trying to drop the schema, but got the error:

Cannot drop schema 'msqta' because it is being referenced by object 'ExecutionStatType'.

Strangely, this returns nothing:

SELECT * FROM sys.objects WHERE schema_id = SCHEMA_ID('msqta')

I would very much appreciate any sort of advice on the matter.

1

Based on the name of the object (ExecutionStatType), it sounds like this is a user-defined data type, which is not visible via sys.objects.

To demonstrate, I'll create a new schema, and a new type that uses that schema:

use [/dev/null]
GO

CREATE SCHEMA msqta;

CREATE TYPE msqta.ExecutionStatType  
FROM varchar(11) NOT NULL ; 

Now, if you query sys.objects and sys.types for objects in that schema, you'll see that the type is only visible in sys.types and not `sys.objects:

SELECT *
FROM sys.objects
WHERE schema_id = schema_id('msqta');

SELECT *
FROM sys.types
WHERE schema_id = schema_id('msqta');

To clean these up, you'll just need to drop the type first, then drop the schema:

DROP TYPE msqta.ExecutionStatType;

DROP SCHEMA msqta;

Because of the way SQL Server throws errors when dropping a schema, it is possible you'll get a similar error for a different type or different object when you drop the schema. You'd need to make sure you dropped all of those as well.

2
  • Thank you very much. I feel a little ashamed because the name is self explanatory ending in Type :) I am still puzzled thou, why should I clean this things by hand. – Vasil Draganov Jun 9 at 16:28
  • It is a bit confusing because sys.tables and sys.views and sys.stored_procedures all roll up to sys.objects .... but sys.types is a bit special & different. As for why those objects are left behind, it could be a bug, or an unfortunate design decision. But in either way, I've found tools like the QTA are notorious for leaving bits behind that you either need to ignore or clean up. – AMtwo Jun 9 at 18:09
1

In case someone else is struggling with the same issue here is a list of all commands needed to clean after the New Database Upgrade Session (QTA):

DROP TABLE [msqta].[ExecutionStat]
DROP TABLE [msqta].[MetaData]
DROP TABLE [msqta].[QueryOptionGroup]
DROP TABLE [msqta].[TuningSession_TuningQuery]
DROP TABLE [msqta].[TuningSession]
DROP TABLE [msqta].[TuningQuery]

DROP PROCEDURE [msqta].[spPurgeData]
DROP PROCEDURE [msqta].[spQueryGet]
DROP PROCEDURE [msqta].[spQuerySave]
DROP PROCEDURE [msqta].[spSessionById]
DROP PROCEDURE [msqta].[spSessionSave]

DROP TYPE [msqta].[ExecutionStatType]
DROP TYPE [msqta].[QueryOptionGroupType]
DROP TYPE [msqta].[TuningQueryType]
DROP TYPE [msqta].[TuningSessionType]

DROP SCHEMA [msqta]

Tested on SQL Server 14.0.3391.2

New contributor
Vasil Draganov is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.