SSDT Schema Compare fails when comparing my DB Project to my Azure Server
TL;DR
The query Visual Studio is executing is being blocked and the query times out.
Turns out I've managed to work this out for our situation: timing out due to blocking!
I ran a profiler trace with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events.
It would reach a certain query and then stop, when VS would claim that the comparison was complete.
Here's the batch of queries in question:
select object_id as id,
null as id2,
case
when [type] != 'D ' then [type]
when parent_object_id != 0 then 'D '
else 'LD'
end as [type],
schema_name(schema_id) as name1,
name as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.objects
where
type != 'S'
and is_ms_shipped != 1
union
select principal_id as id,
null as id2,
type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.database_principals
union
select assembly_id as id,
null as id2,
'CLR' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
null as v1,
modify_date as mod
from sys.assemblies
where
is_user_defined =1
union
select schema_id as id,
null as id2,
'SCH' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
null as v1,
null as mod
from sys.schemas
union
select user_type_id as id,
null as id2,
case
when is_assembly_type = 1 then 'UDT'
when is_table_type = 1 then 'TT'
else 'UDDT'
end as [type],
schema_name(schema_id) as name1,
name as name2,
null as name3,
null as a1,
binary_checksum(system_type_id, schema_id, max_length, precision, scale, is_nullable, is_assembly_type, default_object_id, rule_object_id) as v1,
null as mod
from sys.types
where
is_user_defined = 1
union
select idx.object_id as id,
idx.index_id as id2,
case
when idx.type = 3 then 'XIX'
when idx.type = 4 then 'SIX'
when idx.type = 6 then 'CIX'
else 'IDX'
end as [type],
schema_name(tab.schema_id) as name,
tab.name as name2,
idx.name as name3,
null as a1,
binary_checksum(is_unique, data_space_id, ignore_dup_key, is_primary_key, is_unique_constraint, fill_factor, is_padded, is_disabled, allow_row_locks, allow_page_locks) as v1,
null as mod
from sys.indexes idx
inner join sys.tables tab on idx.object_id = tab.object_id
where
idx.is_primary_key = 0
and idx.name is not null
and idx.is_unique_constraint = 0
and idx.is_hypothetical = 0
union
select stat.object_id as id,
stat.stats_id as id2,
'STAT' as [type],
schema_name(tab.schema_id) as name,
tab.name as name2,
stat.name as name3,
null as a1,
no_recompute as v1,
null as mod
from sys.stats stat
inner join sys.tables tab on stat.object_id = tab.object_id
where
stat.user_created = 1
union
select xml_collection_id as id,
null as id2,
'XSC' as type,
schema_name(schema_id),
name as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.xml_schema_collections
where xml_collection_id > 1
union
select
object_id id,
null id2,
'DDT' as type,
name,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.triggers
where
parent_class = 0
union
select
database_specification_id id,
null as id2,
'DAS' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.database_audit_specifications
union
select
certificate_id id,
null as id2,
'CERT' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
binary_checksum(pvt_key_encryption_type, thumbprint) as v1,
null as mod
from sys.certificates
union
select
asymmetric_key_id id,
null as id2,
'ASMK' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
null as v1,
null as mod
from sys.asymmetric_keys
union
select
symmetric_key_id id,
null as id2,
case
when name = N'##MS_DatabaseMasterKey##' then 'MK'
else 'SYMK'
end as type,
case
when name = N'##MS_DatabaseMasterKey##' then null
else name
end as name1,
null as name2,
null as name3,
principal_id as a1,
null as v1,
modify_date as mod
from sys.symmetric_keys
where
name = N'##MS_DatabaseMasterKey##'
or name not like N'##%'
union
select
fulltext_catalog_id id,
null as id2,
'FTC' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
binary_checksum(is_default, is_accent_sensitivity_on) as v1,
null as mod
from sys.fulltext_catalogs
union
select
ft.object_id id,
ft.unique_index_id as id2,
'FTI' as type,
schema_name(o.schema_id) as name1,
o.name as name2,
null as name3,
null as a1,
binary_checksum(is_enabled, change_tracking_state) as v1,
null as mod
from sys.fulltext_indexes ft
inner join sys.objects o on ft.object_id = o.object_id
union
select
stoplist_id id,
null as id2,
'FTSL' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
null as v1,
modify_date as mod
from sys.fulltext_stoplists
union
select
data_space_id id,
null as id2,
[type] as type,
name as name1,
null as name2,
null as name3,
null as a1,
is_default as v1,
null as mod
from sys.data_spaces
union
select
function_id id,
null as id2,
'PF' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.partition_functions
union
select
file_id id,
null as id2,
'FILE' as type,
name as name1,
null as name2,
null as name3,
null as a1,
binary_checksum(type, data_space_id, physical_name, state, growth, is_read_only, is_sparse, is_percent_growth) as v1,
null as mod
from sys.database_files
union
select
message_type_id id,
null as id2,
'MT' as [type],
name collate database_default as name1,
null as name2,
null as name3,
principal_id as a1,
binary_checksum(validation, xml_collection_id) as v1,
null as mod
from sys.service_message_types
union
select
service_contract_id id,
null as id2,
'SC' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
null as v1,
null as mod
from sys.service_contracts
union
select
service_id id,
null as id2,
'SERVICE' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
binary_checksum(service_queue_id) as v1,
null as mod
from sys.services
union
select
route_id id,
null as id2,
'ROUTE' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
binary_checksum(remote_service_name, broker_instance, lifetime, address, mirror_address) as v1,
null as mod
from sys.routes
union
select
remote_service_binding_id id,
null as id2,
'RSB' as type,
name as name1,
null as name2,
null as name3,
principal_id as a1,
binary_checksum(service_contract_id, remote_principal_id, is_anonymous_on) as v1,
null as mod
from sys.remote_service_bindings
union
select
object_id id,
null as id2,
'DEN' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.event_notifications
union
select
priority_id id,
null as id2,
'SBP' as type,
name as name1,
null as name2,
null as name3,
null as a1,
binary_checksum(service_contract_id, local_service_id, remote_service_name, priority) as v1,
null as mod
from sys.conversation_priorities
union
select
principal_id id,
null as id2,
'LOG' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.server_principals
where
type != 'R'
union
select
audit_id id,
null as id2,
'SAD' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.server_audits
union
select
credential_id id,
null as id2,
'CRD' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.credentials
union
select
msg.message_id id,
msg.language_id as id2,
'SEM' as type,
cast(msg.message_id as sysname) as name1,
lang.alias collate database_default as name2,
null as name3,
null as a1,
binary_checksum(msg.severity, msg.is_event_logged, msg.text) as v1,
null as mod
from sys.messages msg inner join sys.syslanguages lang on (msg.language_id = lang.lcid)
where
msg.message_id > 50000
union
select
base.endpoint_id id,
null as id2,
'SEP' as type,
base.name as name1,
null as name2,
null as name3,
base.principal_id as a1,
binary_checksum(*) as v1,
null as mod
from sys.endpoints base
left outer join sys.http_endpoints http on (base.endpoint_id = http.endpoint_id)
left outer join sys.service_broker_endpoints sb on (base.endpoint_id = sb.endpoint_id)
left outer join sys.soap_endpoints soap on (base.endpoint_id = soap.endpoint_id)
left outer join sys.tcp_endpoints tcp on (base.endpoint_id = tcp.endpoint_id)
left outer join sys.database_mirroring_endpoints mirror on (base.endpoint_id = mirror.endpoint_id)
union
select
server_id id,
null as id2,
'SLS' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.servers
union
select
logins.server_id id,
logins.local_principal_id as id2,
'SLL' as type,
srvs.name as name1,
lp.name as name2,
null as name3,
null as a1,
null as v1,
logins.modify_date as mod
from sys.linked_logins logins
inner join sys.servers srvs on (logins.server_id = srvs.server_id)
left join sys.server_principals lp on (logins.local_principal_id = lp.principal_id)
union
select
object_id id,
null as id2,
'SST' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.server_triggers
where
is_ms_shipped = 0
union
select
server_specification_id id,
null as id2,
'SAS' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
modify_date as mod
from sys.server_audit_specifications
union
select
event_session_id id,
null as id2,
'SES' as type,
name as name1,
null as name2,
null as name3,
null as a1,
null as v1,
null as mod
from sys.server_event_sessions
union
select
provider_id id,
null as id2,
'SCP' as type,
name as name1,
null as name2,
null as name3,
null as a1,
binary_checksum(guid, version, dll_path, is_enabled) as v1,
null as mod
from sys.cryptographic_providers
select
cast(32 as tinyint) as class,
'SES' as type,
event_session_id as majorid,
binary_checksum(event_id, name, package, module) as minorid,
name as name,
null as id1,
null as id2
from sys.server_event_session_actions
union
select
cast(32 as tinyint) as class,
'SES' as type,
event_session_id as majorid,
event_id as minorid,
name as name,
binary_checksum(predicate) as id1,
null as id2
from sys.server_event_session_events
union
select
cast(32 as tinyint) as class,
'SES' as type,
event_session_id as majorid,
object_id as minorid,
name as name,
binary_checksum(value) as id1,
null as id2
from sys.server_event_session_fields
union
select
cast(32 as tinyint) as class,
'SES' as type,
event_session_id as majorid,
event_id as minorid,
name as name,
null as id1,
null as id2
from sys.server_event_session_events
union
select
perms.class as class,
objectpropertyex(perms.major_id, 'BaseType') as type,
perms.major_id as majorid,
perms.minor_id as minorid,
perms.type + perms.state collate database_default as name,
perms.grantee_principal_id as id1,
perms.grantor_principal_id as id2
from sys.database_permissions perms
where
perms.major_id >= 0
union
select
props.class as class,
case
when objs.parent_object_id > 0 then objectpropertyex(objs.parent_object_id, 'BaseType')
else objectpropertyex(props.major_id, 'BaseType')
end as type,
case
when objs.parent_object_id > 0 then objs.parent_object_id
else props.major_id
end as majorid,
props.minor_id as minorid,
props.name as name,
binary_checksum(props.value) as id1,
null as id2
from sys.extended_properties props left outer join sys.objects objs on props.major_id = objs.object_id
union
select
cast(4 as tinyint) as class,
'R' as type,
roles.role_principal_id as majorid,
roles.member_principal_id as minorid,
'drm' as name,
null as id1,
null as id2
from sys.database_role_members roles
union
select
cast(6 as tinyint) as class,
'TT' as type,
tt.user_type_id as majorid,
cols.column_id as minorid,
cols.name as name,
binary_checksum(cols.user_type_id, cols.max_length, cols.precision, cols.scale,
cols.collation_name, cols.is_nullable, cols.is_ansi_padded, cols.is_rowguidcol, cols.is_identity,
cols.is_computed, cols.is_xml_document, cols.xml_collection_id, cols.default_object_id, cols.rule_object_id) as id1,
null as id2
from sys.table_types tt inner join sys.columns cols on (tt.type_table_object_id = cols.object_id)
select
cols.object_id as tableid,
cols.column_id as columnid,
binary_checksum(cols.name) as checksum
from sys.tables tabs
inner join sys.columns cols on (tabs.object_id = cols.object_id)
inner join sys.index_columns idx_cols on (idx_cols.object_id = tabs.object_id and idx_cols.column_id = cols.column_id)
where
tabs.is_ms_shipped = 0
union
select
object_id as tableid,
column_id as columnid,
binary_checksum(type_column_id, language_id) as checksum
from sys.fulltext_index_columns
Running the query in SSMS would never finish executing on the offending server but would take less than a second on every other environment.
So I started to pull it apart and found that the queries that are reading from the following 3 system views were the problem:
sys.server_event_session_actionssys.server_event_session_eventssys.server_event_session_fields
The first of these system views only contained 28 records (found by running a Count(*)), but wouldn't return the data unless I used the (NOLOCK) hint... [b]locking problem!
Finding out the head of the blocking chain was easy enough but it was not killable as it was an Extended Events session. There's a fix for this issue here: https://support.microsoft.com/en-gb/kb/2511963
But hopefully in your case you can just kill off the head of the chain nicely and return things to normal!
EDIT: Having now had a viable maintenance window for the affected SQL Server, a restart has cleared the SPID and now compares are working once more!
Also, here's a quick script I wrote to find the head of the blocking chain(s) that might be helpful for future readers: http://gvee.co.uk/files/sql/admin.blocking_chains.sql
IF Object_ID('admin.blocking_chains', 'V') IS NULL
BEGIN
EXEC ('CREATE VIEW admin.blocking_chains AS SELECT NULL As buffer;');
END
;
GO
ALTER VIEW admin.blocking_chains
AS
WITH processes AS (
SELECT s.spid
, s.blocked As blocking_spid
, DB_Name(s.dbid) As database_name
, s.program_name
, Cast(x.text As varchar(Max)) As definition
, hostname As host_name
, loginame As login_name
, nt_domain
, nt_username
FROM sys.sysprocesses (NOLOCK) As s
CROSS
APPLY sys.dm_exec_sql_text(s.sql_handle) As x
WHERE s.spid > 50
)
, blocking (spid, blocking_spid, blocking_statement, sequence, level, database_name) AS (
SELECT s.spid
, s.blocking_spid
, s.definition
, Row_Number() OVER (ORDER BY s.spid) As sequence
, 0 As level
, s.database_name
FROM processes As s
INNER
JOIN processes As s1
ON s1.blocking_spid = s.spid
WHERE s.blocking_spid = 0
UNION ALL
SELECT r.spid
, r.blocking_spid
, r.definition
, d.sequence
, d.level + 1
, r.database_name
FROM processes As r
INNER
JOIN blocking As d
ON d.spid = r.blocking_spid
WHERE r.blocking_spid > 0
)
, results AS (
SELECT blocking_spid
, spid
, blocking_statement
, level
, Min(sequence) As sequence
, Min(database_name) As database_name
FROM blocking
GROUP
BY blocking_spid
, spid
, blocking_statement
, level
)
SELECT Cast(CASE WHEN results.blocking_spid = 0 THEN 1 ELSE 0 END As bit) As is_head_of_chain
, results.sequence
, results.level
, results.database_name
, results.spid
, results.blocking_spid
, results.blocking_statement
, processes.host_name
, processes.program_name
, processes.login_name
, processes.nt_domain
, processes.nt_username
, Right('0000' + Cast(results.sequence As varchar(11)), 5) + '-' + Right('0000' + Cast(results.level As varchar(11)), 5) As sort_order
FROM results
LEFT
JOIN processes
ON processes.spid = results.spid
;