Blocking Question

I am beginning to see blocked and blocking processes in
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ' If not how can I resolve the blocking problems '
Database is a highly transactional ~500 user database.
Thanks for any feedback.Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
--
Eric Li
SQL DBA
MCDBA
Nick wrote:
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
> > I am beginning to see blocked and blocking processes in
> > Lock / Process ID tab under the current activity window.
> > SQL Server resolves this after a while but is this
> > normal ' If not how can I resolve the blocking problems '
> >
> > Database is a highly transactional ~500 user database.
> >
> > Thanks for any feedback.
>|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.com/sf_block_prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.com/reducing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ' If not how can I resolve the blocking problems '
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.|||Thanks to All.........
>--Original Message--
>I am beginning to see blocked and blocking processes in
>Lock / Process ID tab under the current activity window.
>SQL Server resolves this after a while but is this
>normal ' If not how can I resolve the blocking
problems '
>Database is a highly transactional ~500 user database.
>Thanks for any feedback.
>.
>

Blocking Question

I am beginning to see blocked and blocking processes in
Lock / Process ID tab under the current activity window.
SQL Server resolves this after a while but is this
normal ? If not how can I resolve the blocking problems ?
Database is a highly transactional ~500 user database.
Thanks for any feedback.
Blocking is OK, as long as there's no dead lock. If one process opens a
transaction and then the second process try to access the same tables
used by the first one, the second process will be blocked. It will just
sit there and wait until the first process finish its job (commit or
rollback), so it's normal.
Eric Li
SQL DBA
MCDBA
Nick wrote:

> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ? If not how can I resolve the blocking problems ?
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.
|||Just to add a little to Eric's answer. A little bit of blocking is normal
and can be OK as long as it doesn't wait too long. If your seeing a lot of
blocking you may need to tune some of your statements or indexes some.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:eDAeTbPREHA.1048@.tk2msftngp13.phx.gbl...
> Blocking is OK, as long as there's no dead lock. If one process opens a
> transaction and then the second process try to access the same tables
> used by the first one, the second process will be blocked. It will just
> sit there and wait until the first process finish its job (commit or
> rollback), so it's normal.
> --
> Eric Li
> SQL DBA
> MCDBA
> Nick wrote:
>
|||Hi,
Have a look into thebelow article which explains the strategies to reduce
blocking:-
http://www.sql-server-performance.co...prevention.asp
Have a look into the below link as well to reduce locks
http://www.sql-server-performance.co...cing_locks.asp
Thanks
Hari
MCDBA
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:1491601c444f2$f95cf610$a001280a@.phx.gbl...
> I am beginning to see blocked and blocking processes in
> Lock / Process ID tab under the current activity window.
> SQL Server resolves this after a while but is this
> normal ? If not how can I resolve the blocking problems ?
> Database is a highly transactional ~500 user database.
> Thanks for any feedback.

Blocking on something like Ghost Cleanup?

I noticed that several connections were blocked by something called Ghost Cleanup (or something like that). I know what the cleanup does, but it often causes blocking for quite a while . . .

Anything I can do about it? SS2000.

Thanks,

Michael

Did you check for error 602 in the error log? You want to make sure the object involved isn't damaged.

Other than that, being that they are created when row level locks are used for the delete, you can try using PAGLOCK or TABLOCK hints to avoid them being created.

-Sue

Blocking on something like Ghost Cleanup?

I noticed that several connections were blocked by something called Ghost Cleanup (or something like that). I know what the cleanup does, but it often causes blocking for quite a while . . .

Anything I can do about it? SS2000.

Thanks,

Michael

Did you check for error 602 in the error log? You want to make sure the object involved isn't damaged.

Other than that, being that they are created when row level locks are used for the delete, you can try using PAGLOCK or TABLOCK hints to avoid them being created.

-Sue

Blocking in Distribution Database

I noticed that the distribution agents are blocking themselves in the
distribution database.
It looks like this when i do a sp_who2 spid/blocked By:
80/Not blocked
80/80
80/80
80/80
80/80
Is this normal?
Probably not. Check to see what the process is doing which is doing the
blocking and being blocked. You may need to use DBCC inputbuffer(spid) to do
this. You will get locking when some of the replication agents are running,
and even EM will cause locking. It is also possible that if your server is
really under substaintial load the log reader and distribution agents will
lock themselves.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeff B" <JeffB@.discussions.microsoft.com> wrote in message
news:B25E48D1-6AC8-4D59-892C-AA7E92D7A95A@.microsoft.com...
>I noticed that the distribution agents are blocking themselves in the
> distribution database.
> It looks like this when i do a sp_who2 spid/blocked By:
> 80/Not blocked
> 80/80
> 80/80
> 80/80
> 80/80
> Is this normal?

Blocking behavior of trigger and sp_send_dbmail

Hi Folks
I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
short: sp_send_dbmail inside of trigger results in blocked process and never
ends.
I wanted to code something simple the get notified if new rows were inserted
or updated in table.
create table t1 (col1 int, col2 int)
go
create table t2 (col1 int, col2 int)
go
create trigger tr_ins_t1 on t1 for insert, update
as
set nocount on
truncate table t2
insert t2
select * from inserted
exec msdb.dbo.sp_send_dbmail
@.recipients = 'gene_golub@.hotmail.com'
, @.subject = '!! sql1..t1 db : inserted new rows'
, @.query = 'select * from queries.dbo.t1'
go
insert t1 values(1,1)
go
side comment: inserted table was not recognized inside of query sql string
which would be executed by sp_send_dbmail so I had to use another table to
insert rows first and then to send them over.
When I insert simple row, my query hangs forewer.
What i see in sp_who: some process which is blocked by insert statement.
And this block does not resolve itself untill I go and kill process which is
bloced by insert stm.
I had no problem before using xp_sendmail inside of the triggers. Why it
become a problem with sp_send_dbmail?
Can anybody explain me what am I doing wrong?
thank you, Gene.
Hi Gene,
I have seen that behavior too. Would this work for you?
select * from queries.dbo.t1 with (nolock)
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Gene." wrote:

> Hi Folks
> I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
> short: sp_send_dbmail inside of trigger results in blocked process and never
> ends.
> I wanted to code something simple the get notified if new rows were inserted
> or updated in table.
> create table t1 (col1 int, col2 int)
> go
> create table t2 (col1 int, col2 int)
> go
> create trigger tr_ins_t1 on t1 for insert, update
> as
> set nocount on
> truncate table t2
> insert t2
> select * from inserted
> exec msdb.dbo.sp_send_dbmail
> @.recipients = 'gene_golub@.hotmail.com'
> , @.subject = '!! sql1..t1 db : inserted new rows'
> , @.query = 'select * from queries.dbo.t1'
> go
> insert t1 values(1,1)
> go
> side comment: inserted table was not recognized inside of query sql string
> which would be executed by sp_send_dbmail so I had to use another table to
> insert rows first and then to send them over.
> When I insert simple row, my query hangs forewer.
> What i see in sp_who: some process which is blocked by insert statement.
> And this block does not resolve itself untill I go and kill process which is
> bloced by insert stm.
> I had no problem before using xp_sendmail inside of the triggers. Why it
> become a problem with sp_send_dbmail?
> Can anybody explain me what am I doing wrong?
> thank you, Gene.
|||Hi Ben
Thank you for looking into this issue.
My example was simplification of real situation. Real table has about 1500
rows and wider.
nolock option does not help in that case.
Thank you, Gene.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Gene,
> I have seen that behavior too. Would this work for you?
> select * from queries.dbo.t1 with (nolock)
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Gene." wrote:

Blocking behavior of trigger and sp_send_dbmail

Hi Folks
I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
short: sp_send_dbmail inside of trigger results in blocked process and never
ends.
I wanted to code something simple the get notified if new rows were inserted
or updated in table.
create table t1 (col1 int, col2 int)
go
create table t2 (col1 int, col2 int)
go
create trigger tr_ins_t1 on t1 for insert, update
as
set nocount on
truncate table t2
insert t2
select * from inserted
exec msdb.dbo.sp_send_dbmail
@.recipients = 'gene_golub@.hotmail.com'
, @.subject = '!! sql1..t1 db : inserted new rows'
, @.query = 'select * from queries.dbo.t1'
go
insert t1 values(1,1)
go
side comment: inserted table was not recognized inside of query sql string
which would be executed by sp_send_dbmail so I had to use another table to
insert rows first and then to send them over.
When I insert simple row, my query hangs forewer.
What i see in sp_who: some process which is blocked by insert statement.
And this block does not resolve itself untill I go and kill process which is
bloced by insert stm.
I had no problem before using xp_sendmail inside of the triggers. Why it
become a problem with sp_send_dbmail?
Can anybody explain me what am I doing wrong?
thank you, Gene.Hi Gene,
I have seen that behavior too. Would this work for you?
select * from queries.dbo.t1 with (nolock)
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Gene." wrote:

> Hi Folks
> I came accross very strange behavior of trigger utilizing sp_send_dbmail.
In
> short: sp_send_dbmail inside of trigger results in blocked process and nev
er
> ends.
> I wanted to code something simple the get notified if new rows were insert
ed
> or updated in table.
> create table t1 (col1 int, col2 int)
> go
> create table t2 (col1 int, col2 int)
> go
> create trigger tr_ins_t1 on t1 for insert, update
> as
> set nocount on
> truncate table t2
> insert t2
> select * from inserted
> exec msdb.dbo.sp_send_dbmail
> @.recipients = 'gene_golub@.hotmail.com'
> , @.subject = '!! sql1..t1 db : inserted new rows'
> , @.query = 'select * from queries.dbo.t1'
> go
> insert t1 values(1,1)
> go
> side comment: inserted table was not recognized inside of query sql string
> which would be executed by sp_send_dbmail so I had to use another table to
> insert rows first and then to send them over.
> When I insert simple row, my query hangs forewer.
> What i see in sp_who: some process which is blocked by insert statement.
> And this block does not resolve itself untill I go and kill process which
is
> bloced by insert stm.
> I had no problem before using xp_sendmail inside of the triggers. Why it
> become a problem with sp_send_dbmail?
> Can anybody explain me what am I doing wrong?
> thank you, Gene.|||Hi Ben
Thank you for looking into this issue.
My example was simplification of real situation. Real table has about 1500
rows and wider.
nolock option does not help in that case.
Thank you, Gene.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Gene,
> I have seen that behavior too. Would this work for you?
> select * from queries.dbo.t1 with (nolock)
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Gene." wrote:
>

Blocking behavior of trigger and sp_send_dbmail

Hi Folks
I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
short: sp_send_dbmail inside of trigger results in blocked process and never
ends.
I wanted to code something simple the get notified if new rows were inserted
or updated in table.
create table t1 (col1 int, col2 int)
go
create table t2 (col1 int, col2 int)
go
create trigger tr_ins_t1 on t1 for insert, update
as
set nocount on
truncate table t2
insert t2
select * from inserted
exec msdb.dbo.sp_send_dbmail
@.recipients = 'gene_golub@.hotmail.com'
, @.subject = '!! sql1..t1 db : inserted new rows'
, @.query = 'select * from queries.dbo.t1'
go
insert t1 values(1,1)
go
side comment: inserted table was not recognized inside of query sql string
which would be executed by sp_send_dbmail so I had to use another table to
insert rows first and then to send them over.
When I insert simple row, my query hangs forewer.
What i see in sp_who: some process which is blocked by insert statement.
And this block does not resolve itself untill I go and kill process which is
bloced by insert stm.
I had no problem before using xp_sendmail inside of the triggers. Why it
become a problem with sp_send_dbmail?
Can anybody explain me what am I doing wrong?
thank you, Gene.Hi Gene,
I have seen that behavior too. Would this work for you?
select * from queries.dbo.t1 with (nolock)
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Gene." wrote:
> Hi Folks
> I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
> short: sp_send_dbmail inside of trigger results in blocked process and never
> ends.
> I wanted to code something simple the get notified if new rows were inserted
> or updated in table.
> create table t1 (col1 int, col2 int)
> go
> create table t2 (col1 int, col2 int)
> go
> create trigger tr_ins_t1 on t1 for insert, update
> as
> set nocount on
> truncate table t2
> insert t2
> select * from inserted
> exec msdb.dbo.sp_send_dbmail
> @.recipients = 'gene_golub@.hotmail.com'
> , @.subject = '!! sql1..t1 db : inserted new rows'
> , @.query = 'select * from queries.dbo.t1'
> go
> insert t1 values(1,1)
> go
> side comment: inserted table was not recognized inside of query sql string
> which would be executed by sp_send_dbmail so I had to use another table to
> insert rows first and then to send them over.
> When I insert simple row, my query hangs forewer.
> What i see in sp_who: some process which is blocked by insert statement.
> And this block does not resolve itself untill I go and kill process which is
> bloced by insert stm.
> I had no problem before using xp_sendmail inside of the triggers. Why it
> become a problem with sp_send_dbmail?
> Can anybody explain me what am I doing wrong?
> thank you, Gene.|||Hi Ben
Thank you for looking into this issue.
My example was simplification of real situation. Real table has about 1500
rows and wider.
nolock option does not help in that case.
Thank you, Gene.
"Ben Nevarez" wrote:
> Hi Gene,
> I have seen that behavior too. Would this work for you?
> select * from queries.dbo.t1 with (nolock)
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Gene." wrote:
> > Hi Folks
> >
> > I came accross very strange behavior of trigger utilizing sp_send_dbmail. In
> > short: sp_send_dbmail inside of trigger results in blocked process and never
> > ends.
> >
> > I wanted to code something simple the get notified if new rows were inserted
> > or updated in table.
> >
> > create table t1 (col1 int, col2 int)
> > go
> > create table t2 (col1 int, col2 int)
> > go
> > create trigger tr_ins_t1 on t1 for insert, update
> > as
> > set nocount on
> > truncate table t2
> > insert t2
> > select * from inserted
> >
> > exec msdb.dbo.sp_send_dbmail
> > @.recipients = 'gene_golub@.hotmail.com'
> > , @.subject = '!! sql1..t1 db : inserted new rows'
> > , @.query = 'select * from queries.dbo.t1'
> > go
> > insert t1 values(1,1)
> > go
> >
> > side comment: inserted table was not recognized inside of query sql string
> > which would be executed by sp_send_dbmail so I had to use another table to
> > insert rows first and then to send them over.
> >
> > When I insert simple row, my query hangs forewer.
> > What i see in sp_who: some process which is blocked by insert statement.
> > And this block does not resolve itself untill I go and kill process which is
> > bloced by insert stm.
> >
> > I had no problem before using xp_sendmail inside of the triggers. Why it
> > become a problem with sp_send_dbmail?
> > Can anybody explain me what am I doing wrong?
> > thank you, Gene.

blocking alerts

Is there a way to create an alert when a query is blocked for however long I
choose? I know I can query the SysProcesses table but that will just tell me
who is blocked right now, not who has been blocked for say 60 seconds.
sql2k
TIA, ChrisR
Hi,
There is no build in functionality to alert for blocks based on some time.
Only way is to create a table and insert the contents of
blocked SPID's based on some intervals (say 60 seconds). After that
implement the logic to alert by querying the new table.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>
|||You can use the Blocked column and if it is other than 0 and the Wait Time
is > 60 seconds you have your man.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>
|||I cant do that... it would be way too easy. ;-)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>
|||P.S.
Forgot to say thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>

blocking alerts

Is there a way to create an alert when a query is blocked for however long I
choose? I know I can query the SysProcesses table but that will just tell me
who is blocked right now, not who has been blocked for say 60 seconds.
sql2k
TIA, ChrisRHi,
There is no build in functionality to alert for blocks based on some time.
Only way is to create a table and insert the contents of
blocked SPID's based on some intervals (say 60 seconds). After that
implement the logic to alert by querying the new table.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>|||You can use the Blocked column and if it is other than 0 and the Wait Time
is > 60 seconds you have your man.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>|||I cant do that... it would be way too easy. ;-)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>> Is there a way to create an alert when a query is blocked for however
>> long I choose? I know I can query the SysProcesses table but that will
>> just tell me who is blocked right now, not who has been blocked for say
>> 60 seconds.
>>
>> sql2k
>>
>> TIA, ChrisR
>|||P.S.
Forgot to say thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>> Is there a way to create an alert when a query is blocked for however
>> long I choose? I know I can query the SysProcesses table but that will
>> just tell me who is blocked right now, not who has been blocked for say
>> 60 seconds.
>>
>> sql2k
>>
>> TIA, ChrisR
>

blocking alerts

Is there a way to create an alert when a query is blocked for however long I
choose? I know I can query the SysProcesses table but that will just tell me
who is blocked right now, not who has been blocked for say 60 seconds.
sql2k
TIA, ChrisRHi,
There is no build in functionality to alert for blocks based on some time.
Only way is to create a table and insert the contents of
blocked SPID's based on some intervals (say 60 seconds). After that
implement the logic to alert by querying the new table.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>|||You can use the Blocked column and if it is other than 0 and the Wait Time
is > 60 seconds you have your man.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
> Is there a way to create an alert when a query is blocked for however long
> I choose? I know I can query the SysProcesses table but that will just
> tell me who is blocked right now, not who has been blocked for say 60
> seconds.
>
> sql2k
>
> TIA, ChrisR
>|||I cant do that... it would be way too easy. ;-)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>|||P.S.
Forgot to say thanks.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23joHsVvYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> You can use the Blocked column and if it is other than 0 and the Wait Time
> is > 60 seconds you have your man.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:%23bAx91uYFHA.4036@.tk2msftngp13.phx.gbl...
>

blocking

Frequently when I run sp_who2, I see a spid blocked by itself. Why/How does
this happen? Is there anything I can do about it?
Thanks, Andre
Hi
New feature in SP4.
http://groups.google.com/group/micro...513ab281?hl=en
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:eODlWin6FHA.2608@.tk2msftngp13.phx.gbl...
> Frequently when I run sp_who2, I see a spid blocked by itself. Why/How
> does
> this happen? Is there anything I can do about it?
> Thanks, Andre
>
|||Interesting - thank you.
Andre

blocking

Frequently when I run sp_who2, I see a spid blocked by itself. Why/How does
this happen? Is there anything I can do about it?
Thanks, AndreHi
New feature in SP4.
http://groups.google.com/group/micr...r />
281?hl=en
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:eODlWin6FHA.2608@.tk2msftngp13.phx.gbl...
> Frequently when I run sp_who2, I see a spid blocked by itself. Why/How
> does
> this happen? Is there anything I can do about it?
> Thanks, Andre
>|||Interesting - thank you.
Andre

blocking

Frequently when I run sp_who2, I see a spid blocked by itself. Why/How does
this happen? Is there anything I can do about it?
Thanks, AndreHi
New feature in SP4.
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Andre" <no@.spam.com> wrote in message
news:eODlWin6FHA.2608@.tk2msftngp13.phx.gbl...
> Frequently when I run sp_who2, I see a spid blocked by itself. Why/How
> does
> this happen? Is there anything I can do about it?
> Thanks, Andre
>|||Interesting - thank you.
Andre

Blocked transactions - how to identify the sql commands?

Hi SQLServer gurus :)

Just wondering if anyone can give me some info on how to find out the full syntax of the commands executed by the blocked and blocking SPID's in a locking situation.

Using sp_who or sp_who2 will give basic info on the blocked trans (such as DELETE, SELECT etc), but not the actual statement. The blocking spid's command is only showing AWAITING COMMAND.

Not really an urgent problem, but any suggestions appreciated!

Cheers,
MeganDBCC INPUTBUFFER
Displays the last statement sent from a client to Microsoft SQL Server.

Syntax
DBCC INPUTBUFFER (spid)

You can use this command to see what is the longest running command which may point to the transaction that is causing the blocking.

DBCC OPENTRAN
Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]|||Thanks for your response, achorozy.

Blocked Transactions

I was wanting to write a stored procedure which would identify queries which
are currently being blocked (spids 'blocked by' some other spid) when the
stored procedure is being run.
Is this possible? How do I go about figuring out how to do this?This query will get you the head of the blocking chain...
Declare @.SPID Varchar(500)
Select @.SPID = COALESCE(@.SPID + ',', '') + Cast(a.spid as varchar(20))
From master.dbo.sysprocesses a
where a.blocked = 0 and
a.spid IN (Select b.blocked From master.dbo.sysprocesses b Where b.blocked
!= 0)
Select @.SPID
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>|||Heavey
Just as a starting point, I found that sysprocesses may be a good
place to start. I don't know what this does or anything, but it
looked relevant to the task:
> Select distinct spid, blocked, dbid from master..sysprocesses where blocked
>!= 0 for read only
>open DBA_lockinfo
>Declare @.spid varchar(5)
>Declare @.blocked varchar(5)
>Declare @.dbid varchar(10)
>Declare @.msg varchar (50)
>Declare @.event varchar(500)
>Declare @.event2 varchar(500)
>Create table #aux (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>Create table #aux2 (EventType varchar(100), Parameters varchar(100),
>EventInfo varchar(500))
>fetch next from DBA_lockinfo into @.spid, @.blocked, @.dbid
>While @.@.fetch_status = 0
>Begin
>Insert into #aux exec ('dbcc inputbuffer (' + @.spid + ')')
>Insert into #aux2 exec ('dbcc inputbuffer (' + @.blocked + ')')
>Set @.event = (select EventInfo from #aux)
>Set @.event2 = (select EventInfo from #aux2)
Good luck! Shoot me an email if you get a handle on it.
-Toby
On Tue, 21 Oct 2003 20:35:23 -0500, "Jim Heavey"
<JimHeavey@.hotmail.com> wrote:
>I was wanting to write a stored procedure which would identify queries which
>are currently being blocked (spids 'blocked by' some other spid) when the
>stored procedure is being run.
>Is this possible? How do I go about figuring out how to do this?
>|||Here is something I use. Show blocked spid and the path down to the one
causing all the waiting.
ALTER PROCEDURE dbo.ListBlockedTransactions
( @.ShowResults int = 1
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE BlockedSPIDSCursor CURSOR
FAST_FORWARD
FOR
SELECT BASE.SPID,
BASE.BLOCKED,
BASE.WAITTIME,
BASE.LASTWAITTYPE BLOCKED_LASTWAITTYPE,
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BASE.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BASE.hostname)
END BLOCKED_HOST
FROM SYSPROCESSES BASE
LEFT OUTER JOIN PCDB.dbo.PC PCBASE ON (BASE.hostname = PCBASE.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBASE ON (PCBASE.PERSONID =PERSONBASE.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBASE.CDID =SCD.PERSONID)
WHERE BASE.BLOCKED <> 0
ORDER BY BASE.last_batch DESC
FOR READ ONLY
DECLARE @.SampleTime DATETIME
SET @.SampleTime = GETDATE()
DECLARE @.BLOCKED_SPID INT
DECLARE @.BLOCKED_BLOCKEDBY INT
DECLARE @.BLOCKED_WAITTIME INT
DECLARE @.BLOCKED_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKED_HOST VARCHAR(255)
OPEN BlockedSPIDSCursor
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
WHILE @.@.FETCH_STATUS = 0 -- while fetch returns a row
BEGIN
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKED_BLOCKEDBY, 1
FETCH BlockedSPIDSCursor INTO
@.BLOCKED_SPID,
@.BLOCKED_BLOCKEDBY,
@.BLOCKED_WAITTIME,
@.BLOCKED_LASTWAITTYPE,
@.BLOCKED_HOST
END
CLOSE BlockedSPIDSCursor
DEALLOCATE BlockedSPIDSCursor
RETURN 0
go
ALTER PROCEDURE dbo.ShowBlockingSPID
( @.SampleTime DATETIME,
@.BlockingSPID int,
@.NestLevel int
)
as
SET NOCOUNT ON
-- created by M. Thomas Groszko
DECLARE @.BLOCKING_SPID INT
DECLARE @.BLOCKING_BLOCKEDBY INT
DECLARE @.BLOCKING_WAITTIME INT
DECLARE @.BLOCKING_LASTWAITTYPE VARCHAR(32)
DECLARE @.BLOCKING_HOST VARCHAR(255)
DECLARE @.NESTNEXTLEVEL INT
SELECT @.BLOCKING_SPID = BLOCKING.SPID,
@.BLOCKING_BLOCKEDBY = BLOCKING.BLOCKED,
@.BLOCKING_WAITTIME = BLOCKING.WAITTIME,
@.BLOCKING_LASTWAITTYPE = BLOCKING.LASTWAITTYPE,
@.BLOCKING_HOST =
CASE WHEN [CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME,
SCD.FIRSTNAME, SCD.MIDDLENAME, SCD.PREFERREDNAME) IS NOT NULL
THEN rtrim(BLOCKING.hostname) + '-' +
[CORPORATEDIRECTORY].[dbo].[ConcatenateName](SCD.LASTNAME, SCD.FIRSTNAME,
SCD.MIDDLENAME, SCD.PREFERREDNAME)
ELSE rtrim(BLOCKING.hostname)
END
FROM SYSPROCESSES BLOCKING
LEFT OUTER JOIN PCDB.dbo.PC PCBLOCKING ON (BLOCKING.hostname =PCBLOCKING.MACHINEID)
LEFT OUTER JOIN PCDB.DBO.PERSON PERSONBLOCKING ON (PCBLOCKING.PERSONID =PERSONBLOCKING.PERSONID)
LEFT JOIN CORPORATEDIRECTORY.dbo.PERSON SCD ON (PERSONBLOCKING.CDID =SCD.PERSONID)
WHERE BLOCKING.SPID = @.BlockingSPID
IF @.BLOCKING_BLOCKEDBY <> 0
BEGIN
SET @.NESTNEXTLEVEL = @.NestLevel + 1
EXEC dbo.ShowBlockingSPID @.SampleTime, @.BLOCKING_BLOCKEDBY, @.NESTNEXTLEVEL
END
return 0
go
"Jim Heavey" <JimHeavey@.hotmail.com> wrote in message
news:O$u7BzDmDHA.2080@.TK2MSFTNGP10.phx.gbl...
> I was wanting to write a stored procedure which would identify queries
which
> are currently being blocked (spids 'blocked by' some other spid) when the
> stored procedure is being run.
> Is this possible? How do I go about figuring out how to do this?
>

Blocked transaction problem

Hello,

I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.

SERVER2 is a linked server.

BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN

I have same configuration in other 2 computers and it works ok.

What is the problem?

Thank you!!

This should not lock your transaction. But depending on the server you are using it can be that all data will retrieved from the linked server first to do a filter on the calling server. That will take more time than just doing the Select on the remote server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

there are only 3 rows on remote server.

Both servers are SQL Server 2000.

|||What about if you run the thing without any transaction ? Is it the same problem ? Do you see any locks on the table during the stale time ?

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello,

if I run it without any transaction it finishes very fast.

But I want to know the reason of the problem, because I have one place that runs it this way, and I can not install a new one with same queries.

Thank you.

|||See which locks are allocated when using the command with transactions to see where the blocking is based on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You are essentially doing a distributed transaction because of the use of DML with linked server table. So the delay might be attributed to network issues. Check the MSDTC configuration including network configuration to make sure everything is fine. You can also see the transaction statistics in the MSDTC console (Component Services applet) to see the average response time. If that is close to your execution time then the problem is external to SQL Server and you should ask in the Windows newsgroups or search MSKB for optimizing MSDTC setup.

If the problem is not related to MSDTC, then you can determine the wait types in SQL and troubleshoot the issue. Run following:

1. DBCC SQLPERF(WAITSTATS, 'CLEAR') WITH NO_INFOMSGS

2. your current batch

3. DBCC SQLPERF(WAITSTATS) WITH NO_INFOMSGS

Now, based on the wait type you can figure out what is the cause of the delay. Search MSKB articles for more details on using DBCC SQLPERF.

|||

Hello,

I can not see anything because the execution of the query never ends.
I leave up to 30 minutes and it does not end.

I can execute the query without a transaction and it finishes in a few miliseconds. So, it is posible to query other server.

Could it be a problem with server names?
I mean, the name registered at SQL Server Enterprise Manager, the one at Linked Server and aliases (SQL Server Client Network Utility).

Currently all of them are the same, but at SQL Server installation time the server name was a diferent one.

Thanks!!

|||

Hi!

I have the same problem!!

A simple select againts a second server with a TRAN

BEGIN TRAN
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2
COMMIT TRAN

execution of the query never ends

Without TRAN it finishes in a few miliseconds

someone have any ideas to resolve this problem?

thanks in advance.

Blocked tables

From time to time I get blocked tables in my database and application stope
working.
So I try next example:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
While this selects are working I try
in other query analyzer window to create an update on table2:
UPDATE table2 set column1='test'
and I get blocked tables.
I guess something similar is happening in my application.
How can I prevent this blocking?
regards,SIf you are happy with dirty reads you can do this
SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
http://sqlservercode.blogspot.com/
"simon" wrote:

> From time to time I get blocked tables in my database and application sto
pe
> working.
> So I try next example:
> declare @.n int
> set @.n=50
> while @.n>0
> begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
> end
> While this selects are working I try
> in other query analyzer window to create an update on table2:
> UPDATE table2 set column1='test'
> and I get blocked tables.
> I guess something similar is happening in my application.
> How can I prevent this blocking?
> regards,S
>
>|||Why the tables are blocked until I restart the sql server ?
I can't write nolock in each query. Is there some other way?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:E17B8CBD-CF39-4857-8FEF-7AEBEE25375C@.microsoft.com...
> If you are happy with dirty reads you can do this
> SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
>
> http://sqlservercode.blogspot.com/
> "simon" wrote:
>|||On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:

>Why the tables are blocked until I restart the sql server ?
>I can't write nolock in each query. Is there some other way?
Hi Simon,
It appears to me that there are two things wrong:
1. You have somehow set your transactions to an isolation leven that is
higher than the standard "READ COMMITTED" level. With read committed,
locks for data being read are released when the statement finishes. With
REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
transaction.
2. You are starting transactions that you don't finish. That might be
because not each BEGIN TRANSACTION in your app is matched by either a
COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
explicit transactions, but have the autocommit transaction mode switched
off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
automatically started by SQL Server, but they still have to be ended by
an explicit COMMIT or ROLLBACK statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
I don't set any transaction.
I just open sqlQueryAnalyzer and put this code:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
And open other window and put this code:
UPDATE table2 set column1='test'
If I execute each statement separately, than both works. The first statement
takes about minute, the update one less than second.
If I execute the update statement while select is working than I get blocked
tables for infinite time.
Any idea?
regards,S
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5cbtl1d40ntshsauftgqtjf2m4ucp3el1r@.
4ax.com...
> On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:
>
> Hi Simon,
> It appears to me that there are two things wrong:
> 1. You have somehow set your transactions to an isolation leven that is
> higher than the standard "READ COMMITTED" level. With read committed,
> locks for data being read are released when the statement finishes. With
> REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
> transaction.
> 2. You are starting transactions that you don't finish. That might be
> because not each BEGIN TRANSACTION in your app is matched by either a
> COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
> explicit transactions, but have the autocommit transaction mode switched
> off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
> automatically started by SQL Server, but they still have to be ended by
> an explicit COMMIT or ROLLBACK statement).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 26 Oct 2005 09:21:56 +0200, simon wrote:

>Hugo,
>I don't set any transaction.
>I just open sqlQueryAnalyzer and put this code:
>declare @.n int
>set @.n=50
>while @.n>0
>begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
>end
>And open other window and put this code:
>UPDATE table2 set column1='test'
>
>If I execute each statement separately, than both works. The first statemen
t
>takes about minute, the update one less than second.
>If I execute the update statement while select is working than I get blocke
d
>tables for infinite time.
>Any idea?
Hi Simon,
Not now - I'll need more information.
Please post details about your tables: CREATE TABLE statements
(including all properties, constraints, indexes, etc) for the tables,
INSERT statements for the data in your tables. Also, post the complete
code that you are using, as the code above will only return a syntax
error.
Another thing to try: when both statements are running, open a third
window and execute
sp_lock
Post the results in a reply to this message.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Blocked table

I have a merge sinchronization working just fine. And I have a table
sinchronizing a long time ago without data because i was not using it yet.
No that I have decided to use it, it always said that I can't insert Null in
the rouguid column.
How can I fix it?
Thanks a lot!
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use DataBaseName
go
update sysobjects set replinfo = 0 where name = 'TableName'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
sp_MSunmarkreplinfo 'TableName'
go
Then issue the following
alter tablename
drop column rowguid
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:6D8D5772-3C47-485B-A96B-997BA437C2D2@.microsoft.com...
> I have a merge sinchronization working just fine. And I have a table
> sinchronizing a long time ago without data because i was not using it yet.
> No that I have decided to use it, it always said that I can't insert Null
in
> the rouguid column.
> How can I fix it?
> Thanks a lot!

Blocked sleeping active connections

Hi

We just installed SQL service pack 4. I am now finding that when doing a sp_who2 active, there are a lot of connections that are blocked by itself. The common factor is they all have a status of 'sleeping'. The strange thing is that even though it shows the connection is blocked, it is in fact not and will still return results. Below is a snapshot of a portion of what the sp_who2 active returns:

SPID Status Login HostName BlkBy DBName
53 sleeping sa TRACKER 53 dbABC
58 sleeping sa TRACKER 58 dbCDE
64 sleeping sa TRACKER 64 dbSTA
66 RUNNABLE User12 PC24 . master
70 sleeping User5 ANALYSIS 70 dbBML
74 sleeping sa TRACKER 74 dbCDE
76 sleeping sa TRACKER 76 dbPTS
83 sleeping User5 ANALYSIS 83 dbANA
86 DORMANT User11 CPTDB . NULL

Has anyone seen this? Is it related to the installation of service pack 4? (We have installed the services pack on many other SQL servers, but have not come accross this before.

Tx,
TessZAThe "self-blocking spid" is a new feature of SP4. In short, it is not actual blocking. If a spid is waiting on a latch, it shows as blocking itself. I have not seen it on this scale, though. Do you happen to know if you are suffering a lot of disk activity around the times that a lot of self-blocking spids are showing up?