create database could not obtain exclusive lock on model due to Sql Server data collector controller

Question

I can not create a new database.

Some suggestions were

1.to try reconnecting to the database. This did not work.

2.Restarting the service, which i refuse to do.

3. killing the spid holding the lock. 

Point 3. yes there is a Sql Server Data Collector - Controller that is holding a shared lock. 

From the sys.dm_tran_lock i have the following details . 

I am using the following query 

select  d.name, resource_type,resource_database_id,request_mode,request_status,request_owner_type,request_type

from sys.databases d inner join sys.dm_tran_locks l

on d.database_id = l.resource_database_id

and l.resource_database_id = 3 

 

name resource_type resource_database_id request_mode request_status request_owner_type request_type

model DATABASE 3 S GRANT SHARED_TRANSACTION_WORKSPACE LOCK

 

AND

 

select spid ,PROGRAM_NAME from master..sysprocesses 

where DB_NAME(dbid) = 'model' 

Gives me,

spid PROGRAM_NAME

74 SQL Server Data Collector - Controller              

Now, the data collector is being used by the Management Data Warehouse.

SOLUTION FOUND IN THE PROCESS:

OK, now disabling the data collection helps. Definitely an Architectural limitation here with SQL SERVER, but now you know, and so do I.

Hope this helps someone else.                                                                            

 

 

Answer 1

The above question is with a solution. Thanks.

Answer 2

Hi,

I ran the SQL scripts mentioned above and even though my screen showed (obviously) different result sets, I still cannot create a database while receiving the same error message again. What am I missing? I am running MS SQL Server 2012 Developer edition.

Thanks,

GG