Skip to main content
Database Administrators

Return to Question

Added one row to create blocking
Source Link
edit approved Feb 10, 2016 at 7:51
Chintak Chhapia
edit approved Feb 10, 2016 at 7:51
Chintak Chhapia
  • 125
  • 7

I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)
INSERT INTO [dbo].[Table_1]([Test]) Select 1 

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)
INSERT INTO [dbo].[Table_1]([Test]) Select 1 

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

Tweeted twitter.com/StackDBAs/status/697095658122178560
added 74 characters in body; edited tags
Source Link
user507
user507

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) & sp_configure'block process threshold', 1 --> 1 sec go

To create blocking, in 1 session i executed

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)

sp_configure'block process threshold', 1  --> 1 sec
go

Begin tran Update [dbo].[Table_1] set [Test]=1To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session select * from [dbo].[Table_1]

select * from [dbo].[Table_1]

when i setup extended events, i got below

select * from [dbo].[Table_1] **DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'**** 
<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) & sp_configure'block process threshold', 1 --> 1 sec go

To create blocking, in 1 session i executed

Begin tran Update [dbo].[Table_1] set [Test]=1

and another session select * from [dbo].[Table_1]

when i setup extended events, i got below

select * from [dbo].[Table_1] **DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

I have very basic table

CREATE TABLE [dbo].[Table_1](   [Test] [int] NULL)

sp_configure'block process threshold', 1  --> 1 sec
go

To create blocking, in 1 session i executed

Begin tran
Update [dbo].[Table_1]
set [Test]=1

and another session

select * from [dbo].[Table_1]

when i setup extended events, i got below

<blocked-process-report monitorLoop="1670">
 <blocked-process>
  <process id="process1f18028c8"  .......>
   <executionStack>
    <frame line="1" stmtend="56" sqlhandle="0x02000000c7f1523220ed350bb649a019ae7dc2bca448a2000000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
select * from [dbo].[Table_1]
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="59"....>
   <executionStack />
   <inputbuf>
**DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition =
N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help

Source Link
Maneesh Arora
Maneesh Arora
  • 51
  • 3

Blocking Extended events

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) & sp_configure'block process threshold', 1 --> 1 sec go

To create blocking, in 1 session i executed

Begin tran Update [dbo].[Table_1] set [Test]=1

and another session select * from [dbo].[Table_1]

when i setup extended events, i got below

select * from [dbo].[Table_1] **DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'****

From which sky <<< DECLARE @edition sysname; ... came while it should be Update statement....

Please help