create database could not obtain exclusive lock on model due to Sql Server data collector controller
Wednesday, December 15, 2010 1:56 AM
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
select spid ,PROGRAM_NAME from master..sysprocesses
where DB_NAME(dbid) = 'model'Gives me,
spid PROGRAM_NAME74 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.
- Changed Type Tom Li - MSFTModerator Thursday, December 16, 2010 8:07 AM
Wednesday, December 22, 2010 12:15 AMThe above question is with a solution. Thanks.
Friday, September 21, 2012 5:54 PM
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.