April 8, 2015 at 12:36 am
can some body explain this ?
One input from my side mentioned sql view are not get altered in database though deadlock details showed them here
<?xml version="1.0" encoding="utf-8" ?>
- <deadlock>
- <victim-list>
<victimProcess id="process8c2f0c8" />
</victim-list>
- <process-list>
- <process XDES="0x3465e38790" clientapp="SQLDEPLOY" clientoption1="673319008" clientoption2="128056" currentdb="30" ecid="0" hostname="DC01P2BLD001" hostpid="3756" id="process8c2f0c8" isolationlevel="read committed (2)" kpid="10332" lastattention="1900-01-01T00:00:00.507" lastbatchcompleted="2015-04-08T01:30:35.507" lastbatchstarted="2015-04-08T01:30:35.507" lasttranstarted="2015-04-08T01:30:35.620" lockMode="Sch-S" lockTimeout="4294967295" loginname="ocrman" logused="0" ownerId="9410039274" priority="0" sbid="0" schedulerid="34" spid="257" status="suspended" taskpriority="0" trancount="2" transactionname="WstrObjDefI4I4" waitresource="OBJECT: 30:1461618483:33" waittime="297" xactid="9410037925">
- <executionStack>
<frame line="64" procname="1325286014" sqlhandle="0x03001e007e42fe4ea5b1360000a4000000000000000000000000000000000000000000000000000000000000" stmtstart="3714">INSERT INTO ABC_DBA.dbo.OBJECT_REFRESH_QUEUE ( database_name , [object_name] ) SELECT DB_NAME() , name FROM SYS.OBJECTS so WITH ( NOLOCK ) WHERE type = 'v' AND name LIKE 'vw_%' AND EXISTS ( SELECT * FROM SYS.SQL_MODULES sc WITH ( NOLOCK ) WHERE so.[object_id] = sc.[object_id] AND definition LIKE '%' + @object_name + '%' ) AND name <> @object_name</frame>
<frame line="1" procname="adhoc" sqlhandle="0x01001e007290b62f10d89e0ff200000000000000000000000000000000000000000000000000000000000000">ALTER VIEW vw_ns_email_GetContactInfo AS select c.cont_stub, c.acct_id, c.email_addr as [C-EMAIL], c.cont_title as [C-TITLE], CASE when c.cont_first_name is NULL OR c.cont_first_name = ' ' then '' else c.cont_first_name end AS [C-FIRST NAME], CASE when c.cont_last_name is NULL OR c.cont_last_name = ' ' then '' else c.cont_last_name end AS [C-LAST NAME], CASE when cont_first_name is NULL OR cont_first_name = ' ' then '' when cont_last_name is NULL OR cont_last_name = ' ' then cont_first_name else c.cont_first_name + ' ' + c.cont_last_name end AS [C-FULLNAME], CASE when cont_first_name is NULL OR cont_first_name = ' ' then (CASE when cont_last_name is NULL OR cont_last_name = ' ' then N'Guest' else cont_last_name end) when cont_last_name is NULL OR cont_last_name = ' ' then cont_first_name else cont_last_name + ', ' + cont_first_name end AS cont_last_comma_first, CASE when c.con</frame>
</executionStack>
<inputbuf>ALTER VIEW vw_ns_email_GetContactInfo AS select c.cont_stub, c.acct_id, c.email_addr as [C-EMAIL], c.cont_title as [C-TITLE], CASE when c.cont_first_name is NULL OR c.cont_first_name = ' ' then '' else c.cont_first_name end AS [C-FIRST NAME], CASE when c.cont_last_name is NULL OR c.cont_last_name = ' ' then '' else c.cont_last_name end AS [C-LAST NAME], CASE when cont_first_name is NULL OR cont_first_name = ' ' then '' when cont_last_name is NULL OR cont_last_name = ' ' then cont_first_name else c.cont_first_name + ' ' + c.cont_last_name end AS [C-FULLNAME], CASE when cont_first_name is NULL OR cont_first_name = ' ' then (CASE when cont_last_name is NULL OR cont_last_name = ' ' then N'Guest' else cont_last_name end) when cont_last_name is NULL OR cont_last_name = ' ' then cont_first_name else cont_last_name + ', ' + cont_first_name end AS cont_last_comma_first, CASE when c.co</inputbuf>
</process>
- <process XDES="0x4e3ba5dcc0" clientapp="SQLDEPLOY" clientoption1="673319008" clientoption2="128056" currentdb="30" ecid="0" hostname="DC01P2BLD001" hostpid="3756" id="process2c2f0c8" isolationlevel="read committed (2)" kpid="10876" lastattention="1900-01-01T00:00:00.503" lastbatchcompleted="2015-04-08T01:30:35.503" lastbatchstarted="2015-04-08T01:30:35.503" lasttranstarted="2015-04-08T01:30:35.630" lockMode="Sch-S" lockTimeout="4294967295" loginname="ocrman" logused="0" ownerId="9410039392" priority="0" sbid="0" schedulerid="4" spid="258" status="suspended" taskpriority="0" trancount="2" transactionname="WstrObjDefI4I4" waitresource="OBJECT: 30:2005620421:3" waittime="287" xactid="9410037908">
- <executionStack>
<frame line="64" procname="1325286014" sqlhandle="0x03001e007e42fe4ea5b1360000a4000000000000000000000000000000000000000000000000000000000000" stmtstart="3714">INSERT INTO ABC_DBA.dbo.OBJECT_REFRESH_QUEUE ( database_name , [object_name] ) SELECT DB_NAME() , name FROM SYS.OBJECTS so WITH ( NOLOCK ) WHERE type = 'v' AND name LIKE 'vw_%' AND EXISTS ( SELECT * FROM SYS.SQL_MODULES sc WITH ( NOLOCK ) WHERE so.[object_id] = sc.[object_id] AND definition LIKE '%' + @object_name + '%' ) AND name <> @object_name</frame>
<frame line="1" procname="adhoc" sqlhandle="0x01001e00f7444721901f924d3300000000000000000000000000000000000000000000000000000000000000">ALTER VIEW vw_ns_email_GetAppointmentInfo AS SELECT a.acct_id ,a.evt_stub ,a.appt_stub ,aa.appt_attendee_stub ,a.appt_title as '{[AP-TITLE]}' ,dbo.uf_utl_DisplayFormatDate(ew.date_display_fmt_id, a.start_date) + ' ' + dbo.uf_utl_DisplayFormatTime(ew.time_display_fmt_id, a.start_date) as '{[AP-START DATE]}' ,dbo.uf_utl_DisplayFormatDate(ew.date_display_fmt_id, a.end_date) + ' ' + dbo.uf_utl_DisplayFormatTime(ew.time_display_fmt_id, a.end_date) as '{[AP-END DATE]}' ,a.appt_description as '{[AP-DESCRIPTION]}' ,a.appt_location as '{[AP-LOCATION]}' ,ew.locale_id as locale_id /* Fetching appointment custom field xml */ ,( SELECT '{[AP-CUSTOM:' + upper(cf.cust_field_code) + ']}' as '@key', case when ecf.answer_count > 1 then stuff((select '#~!^`^!~#' + isnull(tr.translated_value,ecfd.answ_text) FROM dbo.EVENT_ENTITY_CUSTOM_FIELD_DETAIL ecfd with (nolock) LEFT OUTER JOIN dbo.TRANSLATION_RESOURCE tr with (nolock) ON ecfd.acct_id = tr.ac</frame>
</executionStack>
<inputbuf>ALTER VIEW vw_ns_email_GetAppointmentInfo AS SELECT a.acct_id ,a.evt_stub ,a.appt_stub ,aa.appt_attendee_stub ,a.appt_title as '{[AP-TITLE]}' ,dbo.uf_utl_DisplayFormatDate(ew.date_display_fmt_id, a.start_date) + ' ' + dbo.uf_utl_DisplayFormatTime(ew.time_display_fmt_id, a.start_date) as '{[AP-START DATE]}' ,dbo.uf_utl_DisplayFormatDate(ew.date_display_fmt_id, a.end_date) + ' ' + dbo.uf_utl_DisplayFormatTime(ew.time_display_fmt_id, a.end_date) as '{[AP-END DATE]}' ,a.appt_description as '{[AP-DESCRIPTION]}' ,a.appt_location as '{[AP-LOCATION]}' ,ew.locale_id as locale_id /* Fetching appointment custom field xml */ ,( SELECT '{[AP-CUSTOM:' + upper(cf.cust_field_code) + ']}' as '@key', case when ecf.answer_count > 1 then stuff((select '#~!^`^!~#' + isnull(tr.translated_value,ecfd.answ_text) FROM dbo.EVENT_ENTITY_CUSTOM_FIELD_DETAIL ecfd with (nolock) LEFT OUTER JOIN dbo.TRANSLATION_RESOURCE tr with (nolock) ON ecfd.acct_id = tr.a</inputbuf>
</process>
</process-list>
- <resource-list>
- <objectlock associatedObjectId="1461618483" dbid="30" id="lock48ab4a8780" lockPartition="33" mode="Sch-M" objectname="ABC_REPORT.dbo.vw_ns_email_GetAppointmentInfo" objid="1461618483" subresource="FULL">
- <owner-list>
<owner id="process2c2f0c8" mode="Sch-M" />
</owner-list>
- <waiter-list>
<waiter id="process8c2f0c8" mode="Sch-S" requestType="wait" />
</waiter-list>
</objectlock>
- <objectlock associatedObjectId="2005620421" dbid="30" id="lock4e0e370e80" lockPartition="3" mode="Sch-M" objectname="ABC_REPORT.dbo.vw_ns_email_GetContactInfo" objid="2005620421" subresource="FULL">
- <owner-list>
<owner id="process8c2f0c8" mode="Sch-M" />
</owner-list>
- <waiter-list>
<waiter id="process2c2f0c8" mode="Sch-S" requestType="wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 8, 2015 at 2:57 am
Yes, those are ALTER VIEW statements running. Whoever told you the views don't get altered is mistaken. The locks involved are Schema Modification locks, those are taken when an object is altered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2015 at 4:40 am
If it says ALTER VIEW, guess what's happening. The deadlock error is not going to lie to you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2015 at 7:25 am
I note that this piece of information "clientapp="SQLDEPLOY"" could mean that the devs really DON'T know what is going on under the covers if they have some automated thingy doing stuff. I have seen that more than a few times at clients!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply