I don't understand why a CREATE PROC from 3.5 hours ago is the head blocker.
The SP wasn't changed since in months so this must be a compile/recompile?
It's not a COMPILE lock though but a PAGE lock. It's certainly not the
initial creation. What else can be done to debug this? It's happening and
usually resolving itself in 4-6 hours or if I kill the head blocker myself.
It doesn't happen every day but almost every day.
I've retrieved this info about the blocking from DMVStats and found similar
info using my regular blocking info script also pulling info from DMV's.
I'm running SQL 2005 SP2 Enterprise.
statement started 6:35AM
waiting statement (PAGE lock):
insert into grades (blah,blah)
select blah blah from homework join blah blah
statement started at 3:01AM
blocking statement: ============================================= -- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- ============================================= CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
@.homeworkResultId int,
@.NumDeleted int output
AS
delete homeworksessions where homeworkresult_id = @.homeworkresultId
set @.NumDeleted=@.@.ROWCOUNT
delete homeworkresults where homeworkresult_id=@.homeworkresultId
set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNTDemi,
I don't think that the CREATE PROCEDURE is blocking anything, but someone is
running this procedure and it is blocking on some internal step. If you are
getting this string from sys.dm_exec_sql_text you will need to look at the
start and end offsets to see which statement in the procedure is actually
running.
You can get these from:
select sql_handle, statement_start_offset, statement_end_offset from
sys.dm_exec_requests
RLF
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
>I don't understand why a CREATE PROC from 3.5 hours ago is the head
>blocker.
> The SP wasn't changed since in months so this must be a compile/recompile?
> It's not a COMPILE lock though but a PAGE lock. It's certainly not the
> initial creation. What else can be done to debug this? It's happening and
> usually resolving itself in 4-6 hours or if I kill the head blocker
> myself.
> It doesn't happen every day but almost every day.
> I've retrieved this info about the blocking from DMVStats and found
> similar
> info using my regular blocking info script also pulling info from DMV's.
> I'm running SQL 2005 SP2 Enterprise.
> statement started 6:35AM
> waiting statement (PAGE lock):
> insert into grades (blah,blah)
> select blah blah from homework join blah blah
> statement started at 3:01AM
> blocking statement: =============================================> -- Author: <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- =============================================> CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
> @.homeworkResultId int,
> @.NumDeleted int output
> AS
> delete homeworksessions where homeworkresult_id = @.homeworkresultId
> set @.NumDeleted=@.@.ROWCOUNT
> delete homeworkresults where homeworkresult_id=@.homeworkresultId
> set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT|||Thanks. I will find out which statement, but it's going to be one delete
statement or the other and both are trivial. Since this SP call is the head
blocker I have no information that I can see about what made IT block. It
has no parent blocker, it's just stuck. How can I proceed investigating?
"Russell Fields" wrote:
> Demi,
> I don't think that the CREATE PROCEDURE is blocking anything, but someone is
> running this procedure and it is blocking on some internal step. If you are
> getting this string from sys.dm_exec_sql_text you will need to look at the
> start and end offsets to see which statement in the procedure is actually
> running.
> You can get these from:
> select sql_handle, statement_start_offset, statement_end_offset from
> sys.dm_exec_requests
> RLF
> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
> >I don't understand why a CREATE PROC from 3.5 hours ago is the head
> >blocker.
> > The SP wasn't changed since in months so this must be a compile/recompile?
> > It's not a COMPILE lock though but a PAGE lock. It's certainly not the
> > initial creation. What else can be done to debug this? It's happening and
> > usually resolving itself in 4-6 hours or if I kill the head blocker
> > myself.
> > It doesn't happen every day but almost every day.
> >
> > I've retrieved this info about the blocking from DMVStats and found
> > similar
> > info using my regular blocking info script also pulling info from DMV's.
> >
> > I'm running SQL 2005 SP2 Enterprise.
> >
> > statement started 6:35AM
> > waiting statement (PAGE lock):
> >
> > insert into grades (blah,blah)
> > select blah blah from homework join blah blah
> >
> > statement started at 3:01AM
> > blocking statement: =============================================> > -- Author: <Author,,Name>
> > -- Create date: <Create Date,,>
> > -- Description: <Description,,>
> > -- =============================================> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
> > @.homeworkResultId int,
> > @.NumDeleted int output
> > AS
> >
> > delete homeworksessions where homeworkresult_id = @.homeworkresultId
> >
> > set @.NumDeleted=@.@.ROWCOUNT
> >
> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
> >
> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
>
>|||Demi,
Perhaps you have an orphaned connection, as mentioned here:
http://support.microsoft.com/kb/137983/en-us
and at the end of this article:
http://support.microsoft.com/kb/224453/en-us
The solution offered for 2005 is the KILL command:
http://msdn2.microsoft.com/en-us/library/ms173730.aspx
Also, I have found that sometimes having the computer that made the
connection log off of the domain, and then log back in again, will alert SQL
Server that all is not well with the existing transaction and it will roll
back. (Kind of like it 'sees' the computer login, says to itself "Then this
transaction must be junk", and throws it away.)
RLF
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
> Thanks. I will find out which statement, but it's going to be one delete
> statement or the other and both are trivial. Since this SP call is the
> head
> blocker I have no information that I can see about what made IT block. It
> has no parent blocker, it's just stuck. How can I proceed investigating?
> "Russell Fields" wrote:
>> Demi,
>> I don't think that the CREATE PROCEDURE is blocking anything, but someone
>> is
>> running this procedure and it is blocking on some internal step. If you
>> are
>> getting this string from sys.dm_exec_sql_text you will need to look at
>> the
>> start and end offsets to see which statement in the procedure is actually
>> running.
>> You can get these from:
>> select sql_handle, statement_start_offset, statement_end_offset from
>> sys.dm_exec_requests
>> RLF
>> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
>> >I don't understand why a CREATE PROC from 3.5 hours ago is the head
>> >blocker.
>> > The SP wasn't changed since in months so this must be a
>> > compile/recompile?
>> > It's not a COMPILE lock though but a PAGE lock. It's certainly not the
>> > initial creation. What else can be done to debug this? It's happening
>> > and
>> > usually resolving itself in 4-6 hours or if I kill the head blocker
>> > myself.
>> > It doesn't happen every day but almost every day.
>> >
>> > I've retrieved this info about the blocking from DMVStats and found
>> > similar
>> > info using my regular blocking info script also pulling info from
>> > DMV's.
>> >
>> > I'm running SQL 2005 SP2 Enterprise.
>> >
>> > statement started 6:35AM
>> > waiting statement (PAGE lock):
>> >
>> > insert into grades (blah,blah)
>> > select blah blah from homework join blah blah
>> >
>> > statement started at 3:01AM
>> > blocking statement: =============================================>> > -- Author: <Author,,Name>
>> > -- Create date: <Create Date,,>
>> > -- Description: <Description,,>
>> > -- =============================================>> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
>> > @.homeworkResultId int,
>> > @.NumDeleted int output
>> > AS
>> >
>> > delete homeworksessions where homeworkresult_id = @.homeworkresultId
>> >
>> > set @.NumDeleted=@.@.ROWCOUNT
>> >
>> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
>> >
>> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
>>|||That's an interesting theory. We do have an overnight process kicked off
from a http handler that runs for 30 minutes until the page timeout kicks in.
The job has to run off hours and is essentially a processing loop so we
limit how long it can go and it often doesn't have time to finish all items
some days before the timeout. We have the database connection inside a
"using" block so I figured the transaction in progress should be rolled back
and the connection closed. Is there a possibility that this isn't always the
case? If so is there anything we can really do in .NET to make the timeout
clean up resources any more safely? Would catching ThreadAbortException and
closing the connection explicitly help at all?
We're not using a domain logon environment. This is coming from a webserver
frontend.
"Russell Fields" wrote:
> Demi,
> Perhaps you have an orphaned connection, as mentioned here:
> http://support.microsoft.com/kb/137983/en-us
> and at the end of this article:
> http://support.microsoft.com/kb/224453/en-us
> The solution offered for 2005 is the KILL command:
> http://msdn2.microsoft.com/en-us/library/ms173730.aspx
> Also, I have found that sometimes having the computer that made the
> connection log off of the domain, and then log back in again, will alert SQL
> Server that all is not well with the existing transaction and it will roll
> back. (Kind of like it 'sees' the computer login, says to itself "Then this
> transaction must be junk", and throws it away.)
> RLF
> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
> >
> > Thanks. I will find out which statement, but it's going to be one delete
> > statement or the other and both are trivial. Since this SP call is the
> > head
> > blocker I have no information that I can see about what made IT block. It
> > has no parent blocker, it's just stuck. How can I proceed investigating?
> >
> > "Russell Fields" wrote:
> >
> >> Demi,
> >>
> >> I don't think that the CREATE PROCEDURE is blocking anything, but someone
> >> is
> >> running this procedure and it is blocking on some internal step. If you
> >> are
> >> getting this string from sys.dm_exec_sql_text you will need to look at
> >> the
> >> start and end offsets to see which statement in the procedure is actually
> >> running.
> >>
> >> You can get these from:
> >>
> >> select sql_handle, statement_start_offset, statement_end_offset from
> >> sys.dm_exec_requests
> >>
> >> RLF
> >>
> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> >> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
> >> >I don't understand why a CREATE PROC from 3.5 hours ago is the head
> >> >blocker.
> >> > The SP wasn't changed since in months so this must be a
> >> > compile/recompile?
> >> > It's not a COMPILE lock though but a PAGE lock. It's certainly not the
> >> > initial creation. What else can be done to debug this? It's happening
> >> > and
> >> > usually resolving itself in 4-6 hours or if I kill the head blocker
> >> > myself.
> >> > It doesn't happen every day but almost every day.
> >> >
> >> > I've retrieved this info about the blocking from DMVStats and found
> >> > similar
> >> > info using my regular blocking info script also pulling info from
> >> > DMV's.
> >> >
> >> > I'm running SQL 2005 SP2 Enterprise.
> >> >
> >> > statement started 6:35AM
> >> > waiting statement (PAGE lock):
> >> >
> >> > insert into grades (blah,blah)
> >> > select blah blah from homework join blah blah
> >> >
> >> > statement started at 3:01AM
> >> > blocking statement: =============================================> >> > -- Author: <Author,,Name>
> >> > -- Create date: <Create Date,,>
> >> > -- Description: <Description,,>
> >> > -- =============================================> >> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
> >> > @.homeworkResultId int,
> >> > @.NumDeleted int output
> >> > AS
> >> >
> >> > delete homeworksessions where homeworkresult_id = @.homeworkresultId
> >> >
> >> > set @.NumDeleted=@.@.ROWCOUNT
> >> >
> >> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
> >> >
> >> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
> >>
> >>
> >>
>
>|||Closing the connection completely should roll back any open transactions but
you can use @.@.TRANCOUNT to see if there are any open trans on that
connection. If so issue a ROLLBACK and then close the connection.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:F9CBE982-C2AB-4770-8180-0F3EE272B04B@.microsoft.com...
> That's an interesting theory. We do have an overnight process kicked off
> from a http handler that runs for 30 minutes until the page timeout kicks
> in.
> The job has to run off hours and is essentially a processing loop so we
> limit how long it can go and it often doesn't have time to finish all
> items
> some days before the timeout. We have the database connection inside a
> "using" block so I figured the transaction in progress should be rolled
> back
> and the connection closed. Is there a possibility that this isn't always
> the
> case? If so is there anything we can really do in .NET to make the
> timeout
> clean up resources any more safely? Would catching ThreadAbortException
> and
> closing the connection explicitly help at all?
> We're not using a domain logon environment. This is coming from a
> webserver
> frontend.
> "Russell Fields" wrote:
>> Demi,
>> Perhaps you have an orphaned connection, as mentioned here:
>> http://support.microsoft.com/kb/137983/en-us
>> and at the end of this article:
>> http://support.microsoft.com/kb/224453/en-us
>> The solution offered for 2005 is the KILL command:
>> http://msdn2.microsoft.com/en-us/library/ms173730.aspx
>> Also, I have found that sometimes having the computer that made the
>> connection log off of the domain, and then log back in again, will alert
>> SQL
>> Server that all is not well with the existing transaction and it will
>> roll
>> back. (Kind of like it 'sees' the computer login, says to itself "Then
>> this
>> transaction must be junk", and throws it away.)
>> RLF
>> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
>> >
>> > Thanks. I will find out which statement, but it's going to be one
>> > delete
>> > statement or the other and both are trivial. Since this SP call is the
>> > head
>> > blocker I have no information that I can see about what made IT block.
>> > It
>> > has no parent blocker, it's just stuck. How can I proceed
>> > investigating?
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Demi,
>> >>
>> >> I don't think that the CREATE PROCEDURE is blocking anything, but
>> >> someone
>> >> is
>> >> running this procedure and it is blocking on some internal step. If
>> >> you
>> >> are
>> >> getting this string from sys.dm_exec_sql_text you will need to look at
>> >> the
>> >> start and end offsets to see which statement in the procedure is
>> >> actually
>> >> running.
>> >>
>> >> You can get these from:
>> >>
>> >> select sql_handle, statement_start_offset, statement_end_offset from
>> >> sys.dm_exec_requests
>> >>
>> >> RLF
>> >>
>> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> >> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
>> >> >I don't understand why a CREATE PROC from 3.5 hours ago is the head
>> >> >blocker.
>> >> > The SP wasn't changed since in months so this must be a
>> >> > compile/recompile?
>> >> > It's not a COMPILE lock though but a PAGE lock. It's certainly not
>> >> > the
>> >> > initial creation. What else can be done to debug this? It's
>> >> > happening
>> >> > and
>> >> > usually resolving itself in 4-6 hours or if I kill the head blocker
>> >> > myself.
>> >> > It doesn't happen every day but almost every day.
>> >> >
>> >> > I've retrieved this info about the blocking from DMVStats and found
>> >> > similar
>> >> > info using my regular blocking info script also pulling info from
>> >> > DMV's.
>> >> >
>> >> > I'm running SQL 2005 SP2 Enterprise.
>> >> >
>> >> > statement started 6:35AM
>> >> > waiting statement (PAGE lock):
>> >> >
>> >> > insert into grades (blah,blah)
>> >> > select blah blah from homework join blah blah
>> >> >
>> >> > statement started at 3:01AM
>> >> > blocking statement: =============================================>> >> > -- Author: <Author,,Name>
>> >> > -- Create date: <Create Date,,>
>> >> > -- Description: <Description,,>
>> >> > -- =============================================>> >> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
>> >> > @.homeworkResultId int,
>> >> > @.NumDeleted int output
>> >> > AS
>> >> >
>> >> > delete homeworksessions where homeworkresult_id = @.homeworkresultId
>> >> >
>> >> > set @.NumDeleted=@.@.ROWCOUNT
>> >> >
>> >> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
>> >> >
>> >> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
>> >>
>> >>
>> >>
>>|||If the Server.ScriptTimeout expires while my SqlCommand.ExecuteNonQuery() is
running, "System.Web.HttpException: Request timed out" is thrown and my
connection is closed before the first line of the catch so the transaction is
not rolled back leaving an open transaction for that spid in Sql Server.
This is very bad and seems like a Framework bug.
try
{
if (_SqlConn == null)
{
_SqlConn = OpenConnection();
}
else if (_SqlConn.State == ConnectionState.Closed)
{
_SqlConn.Open();
}
// GetCommand creates a command and begins a transaction
using (SqlCommand cmd = GetCommand(_SqlConn))
{
if (_Timeout > 0)
cmd.CommandTimeout = _Timeout;
cmd.ExecuteNonQuery();
}
}
catch
{
HttpContext.Current.Trace.Write("DB", "Catch" + ((_SqlConn
== null) ? "no conn" : _SqlConn.State.ToString()));
// At first line in the catch the conn is closed
_SqlConn.State == Closed
// but the transaction is not rolled back
CloseConnection(); // this does a rollback if a transaction
exists, but the db connection is closed so the rollback doesn't work
}
"Andrew J. Kelly" wrote:
> Closing the connection completely should roll back any open transactions but
> you can use @.@.TRANCOUNT to see if there are any open trans on that
> connection. If so issue a ROLLBACK and then close the connection.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> news:F9CBE982-C2AB-4770-8180-0F3EE272B04B@.microsoft.com...
> >
> > That's an interesting theory. We do have an overnight process kicked off
> > from a http handler that runs for 30 minutes until the page timeout kicks
> > in.
> > The job has to run off hours and is essentially a processing loop so we
> > limit how long it can go and it often doesn't have time to finish all
> > items
> > some days before the timeout. We have the database connection inside a
> > "using" block so I figured the transaction in progress should be rolled
> > back
> > and the connection closed. Is there a possibility that this isn't always
> > the
> > case? If so is there anything we can really do in .NET to make the
> > timeout
> > clean up resources any more safely? Would catching ThreadAbortException
> > and
> > closing the connection explicitly help at all?
> >
> > We're not using a domain logon environment. This is coming from a
> > webserver
> > frontend.
> >
> > "Russell Fields" wrote:
> >
> >> Demi,
> >>
> >> Perhaps you have an orphaned connection, as mentioned here:
> >> http://support.microsoft.com/kb/137983/en-us
> >> and at the end of this article:
> >> http://support.microsoft.com/kb/224453/en-us
> >>
> >> The solution offered for 2005 is the KILL command:
> >> http://msdn2.microsoft.com/en-us/library/ms173730.aspx
> >>
> >> Also, I have found that sometimes having the computer that made the
> >> connection log off of the domain, and then log back in again, will alert
> >> SQL
> >> Server that all is not well with the existing transaction and it will
> >> roll
> >> back. (Kind of like it 'sees' the computer login, says to itself "Then
> >> this
> >> transaction must be junk", and throws it away.)
> >>
> >> RLF
> >>
> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> >> news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
> >> >
> >> > Thanks. I will find out which statement, but it's going to be one
> >> > delete
> >> > statement or the other and both are trivial. Since this SP call is the
> >> > head
> >> > blocker I have no information that I can see about what made IT block.
> >> > It
> >> > has no parent blocker, it's just stuck. How can I proceed
> >> > investigating?
> >> >
> >> > "Russell Fields" wrote:
> >> >
> >> >> Demi,
> >> >>
> >> >> I don't think that the CREATE PROCEDURE is blocking anything, but
> >> >> someone
> >> >> is
> >> >> running this procedure and it is blocking on some internal step. If
> >> >> you
> >> >> are
> >> >> getting this string from sys.dm_exec_sql_text you will need to look at
> >> >> the
> >> >> start and end offsets to see which statement in the procedure is
> >> >> actually
> >> >> running.
> >> >>
> >> >> You can get these from:
> >> >>
> >> >> select sql_handle, statement_start_offset, statement_end_offset from
> >> >> sys.dm_exec_requests
> >> >>
> >> >> RLF
> >> >>
> >> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> >> >> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
> >> >> >I don't understand why a CREATE PROC from 3.5 hours ago is the head
> >> >> >blocker.
> >> >> > The SP wasn't changed since in months so this must be a
> >> >> > compile/recompile?
> >> >> > It's not a COMPILE lock though but a PAGE lock. It's certainly not
> >> >> > the
> >> >> > initial creation. What else can be done to debug this? It's
> >> >> > happening
> >> >> > and
> >> >> > usually resolving itself in 4-6 hours or if I kill the head blocker
> >> >> > myself.
> >> >> > It doesn't happen every day but almost every day.
> >> >> >
> >> >> > I've retrieved this info about the blocking from DMVStats and found
> >> >> > similar
> >> >> > info using my regular blocking info script also pulling info from
> >> >> > DMV's.
> >> >> >
> >> >> > I'm running SQL 2005 SP2 Enterprise.
> >> >> >
> >> >> > statement started 6:35AM
> >> >> > waiting statement (PAGE lock):
> >> >> >
> >> >> > insert into grades (blah,blah)
> >> >> > select blah blah from homework join blah blah
> >> >> >
> >> >> > statement started at 3:01AM
> >> >> > blocking statement: =============================================> >> >> > -- Author: <Author,,Name>
> >> >> > -- Create date: <Create Date,,>
> >> >> > -- Description: <Description,,>
> >> >> > -- =============================================> >> >> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
> >> >> > @.homeworkResultId int,
> >> >> > @.NumDeleted int output
> >> >> > AS
> >> >> >
> >> >> > delete homeworksessions where homeworkresult_id = @.homeworkresultId
> >> >> >
> >> >> > set @.NumDeleted=@.@.ROWCOUNT
> >> >> >
> >> >> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
> >> >> >
> >> >> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||I don't know what that line of code is actually doing but it can't really be
closing the connection or it would roll back. I assume you are using a
connection pool and this may have something to do with it. In any case I
would think the code should be such that it checks for open trans and rolls
them back if any are found before it closes the conn.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:6A00F3ED-9C0B-4E41-87EF-0DAEA200F600@.microsoft.com...
> If the Server.ScriptTimeout expires while my SqlCommand.ExecuteNonQuery()
> is
> running, "System.Web.HttpException: Request timed out" is thrown and my
> connection is closed before the first line of the catch so the transaction
> is
> not rolled back leaving an open transaction for that spid in Sql Server.
> This is very bad and seems like a Framework bug.
> try
> {
> if (_SqlConn == null)
> {
> _SqlConn = OpenConnection();
> }
> else if (_SqlConn.State == ConnectionState.Closed)
> {
> _SqlConn.Open();
> }
> // GetCommand creates a command and begins a transaction
> using (SqlCommand cmd = GetCommand(_SqlConn))
> {
> if (_Timeout > 0)
> cmd.CommandTimeout = _Timeout;
> cmd.ExecuteNonQuery();
> }
> }
> catch
> {
> HttpContext.Current.Trace.Write("DB", "Catch" + ((_SqlConn
> == null) ? "no conn" : _SqlConn.State.ToString()));
> // At first line in the catch the conn is closed
> _SqlConn.State == Closed
> // but the transaction is not rolled back
> CloseConnection(); // this does a rollback if a transaction
> exists, but the db connection is closed so the rollback doesn't work
> }
>
> "Andrew J. Kelly" wrote:
>> Closing the connection completely should roll back any open transactions
>> but
>> you can use @.@.TRANCOUNT to see if there are any open trans on that
>> connection. If so issue a ROLLBACK and then close the connection.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> news:F9CBE982-C2AB-4770-8180-0F3EE272B04B@.microsoft.com...
>> >
>> > That's an interesting theory. We do have an overnight process kicked
>> > off
>> > from a http handler that runs for 30 minutes until the page timeout
>> > kicks
>> > in.
>> > The job has to run off hours and is essentially a processing loop so we
>> > limit how long it can go and it often doesn't have time to finish all
>> > items
>> > some days before the timeout. We have the database connection inside a
>> > "using" block so I figured the transaction in progress should be rolled
>> > back
>> > and the connection closed. Is there a possibility that this isn't
>> > always
>> > the
>> > case? If so is there anything we can really do in .NET to make the
>> > timeout
>> > clean up resources any more safely? Would catching
>> > ThreadAbortException
>> > and
>> > closing the connection explicitly help at all?
>> >
>> > We're not using a domain logon environment. This is coming from a
>> > webserver
>> > frontend.
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Demi,
>> >>
>> >> Perhaps you have an orphaned connection, as mentioned here:
>> >> http://support.microsoft.com/kb/137983/en-us
>> >> and at the end of this article:
>> >> http://support.microsoft.com/kb/224453/en-us
>> >>
>> >> The solution offered for 2005 is the KILL command:
>> >> http://msdn2.microsoft.com/en-us/library/ms173730.aspx
>> >>
>> >> Also, I have found that sometimes having the computer that made the
>> >> connection log off of the domain, and then log back in again, will
>> >> alert
>> >> SQL
>> >> Server that all is not well with the existing transaction and it will
>> >> roll
>> >> back. (Kind of like it 'sees' the computer login, says to itself
>> >> "Then
>> >> this
>> >> transaction must be junk", and throws it away.)
>> >>
>> >> RLF
>> >>
>> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> >> news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
>> >> >
>> >> > Thanks. I will find out which statement, but it's going to be one
>> >> > delete
>> >> > statement or the other and both are trivial. Since this SP call is
>> >> > the
>> >> > head
>> >> > blocker I have no information that I can see about what made IT
>> >> > block.
>> >> > It
>> >> > has no parent blocker, it's just stuck. How can I proceed
>> >> > investigating?
>> >> >
>> >> > "Russell Fields" wrote:
>> >> >
>> >> >> Demi,
>> >> >>
>> >> >> I don't think that the CREATE PROCEDURE is blocking anything, but
>> >> >> someone
>> >> >> is
>> >> >> running this procedure and it is blocking on some internal step.
>> >> >> If
>> >> >> you
>> >> >> are
>> >> >> getting this string from sys.dm_exec_sql_text you will need to look
>> >> >> at
>> >> >> the
>> >> >> start and end offsets to see which statement in the procedure is
>> >> >> actually
>> >> >> running.
>> >> >>
>> >> >> You can get these from:
>> >> >>
>> >> >> select sql_handle, statement_start_offset, statement_end_offset
>> >> >> from
>> >> >> sys.dm_exec_requests
>> >> >>
>> >> >> RLF
>> >> >>
>> >> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> >> >> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
>> >> >> >I don't understand why a CREATE PROC from 3.5 hours ago is the
>> >> >> >head
>> >> >> >blocker.
>> >> >> > The SP wasn't changed since in months so this must be a
>> >> >> > compile/recompile?
>> >> >> > It's not a COMPILE lock though but a PAGE lock. It's certainly
>> >> >> > not
>> >> >> > the
>> >> >> > initial creation. What else can be done to debug this? It's
>> >> >> > happening
>> >> >> > and
>> >> >> > usually resolving itself in 4-6 hours or if I kill the head
>> >> >> > blocker
>> >> >> > myself.
>> >> >> > It doesn't happen every day but almost every day.
>> >> >> >
>> >> >> > I've retrieved this info about the blocking from DMVStats and
>> >> >> > found
>> >> >> > similar
>> >> >> > info using my regular blocking info script also pulling info from
>> >> >> > DMV's.
>> >> >> >
>> >> >> > I'm running SQL 2005 SP2 Enterprise.
>> >> >> >
>> >> >> > statement started 6:35AM
>> >> >> > waiting statement (PAGE lock):
>> >> >> >
>> >> >> > insert into grades (blah,blah)
>> >> >> > select blah blah from homework join blah blah
>> >> >> >
>> >> >> > statement started at 3:01AM
>> >> >> > blocking statement: =============================================>> >> >> > -- Author: <Author,,Name>
>> >> >> > -- Create date: <Create Date,,>
>> >> >> > -- Description: <Description,,>
>> >> >> > -- =============================================>> >> >> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
>> >> >> > @.homeworkResultId int,
>> >> >> > @.NumDeleted int output
>> >> >> > AS
>> >> >> >
>> >> >> > delete homeworksessions where homeworkresult_id =>> >> >> > @.homeworkresultId
>> >> >> >
>> >> >> > set @.NumDeleted=@.@.ROWCOUNT
>> >> >> >
>> >> >> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
>> >> >> >
>> >> >> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||I've created a simplified example with all the source. You need to run it
with compilation debug="false". I run a profiler trace as well looking at
all the transaction events. There's a begin but no end when the page
(script) timeout hits. If there's a sql timeout then the handling is fine,
but the page timeout results in bad behavior. You can confirm this by seeing
the open transaction for the spid that executed the statement.
Server.ScriptTimeout = 5;
using (SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
{
using (SqlCommand command = connection.CreateCommand())
{
SqlTransaction transaction = null;
command.CommandTimeout = 90;
try
{
connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText = "WAITFOR DELAY '00:00:10'";
command.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
"Andrew J. Kelly" wrote:
> I don't know what that line of code is actually doing but it can't really be
> closing the connection or it would roll back. I assume you are using a
> connection pool and this may have something to do with it. In any case I
> would think the code should be such that it checks for open trans and rolls
> them back if any are found before it closes the conn.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> news:6A00F3ED-9C0B-4E41-87EF-0DAEA200F600@.microsoft.com...
> >
> > If the Server.ScriptTimeout expires while my SqlCommand.ExecuteNonQuery()
> > is
> > running, "System.Web.HttpException: Request timed out" is thrown and my
> > connection is closed before the first line of the catch so the transaction
> > is
> > not rolled back leaving an open transaction for that spid in Sql Server.
> > This is very bad and seems like a Framework bug.
> >
> > try
> > {
> > if (_SqlConn == null)
> > {
> > _SqlConn = OpenConnection();
> > }
> > else if (_SqlConn.State == ConnectionState.Closed)
> > {
> > _SqlConn.Open();
> > }
> >
> > // GetCommand creates a command and begins a transaction
> > using (SqlCommand cmd = GetCommand(_SqlConn))
> > {
> > if (_Timeout > 0)
> > cmd.CommandTimeout = _Timeout;
> > cmd.ExecuteNonQuery();
> > }
> > }
> > catch
> > {
> > HttpContext.Current.Trace.Write("DB", "Catch" + ((_SqlConn
> > == null) ? "no conn" : _SqlConn.State.ToString()));
> > // At first line in the catch the conn is closed
> > _SqlConn.State == Closed
> > // but the transaction is not rolled back
> >
> > CloseConnection(); // this does a rollback if a transaction
> > exists, but the db connection is closed so the rollback doesn't work
> > }
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Closing the connection completely should roll back any open transactions
> >> but
> >> you can use @.@.TRANCOUNT to see if there are any open trans on that
> >> connection. If so issue a ROLLBACK and then close the connection.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> >> news:F9CBE982-C2AB-4770-8180-0F3EE272B04B@.microsoft.com...
> >> >
> >> > That's an interesting theory. We do have an overnight process kicked
> >> > off
> >> > from a http handler that runs for 30 minutes until the page timeout
> >> > kicks
> >> > in.
> >> > The job has to run off hours and is essentially a processing loop so we
> >> > limit how long it can go and it often doesn't have time to finish all
> >> > items
> >> > some days before the timeout. We have the database connection inside a
> >> > "using" block so I figured the transaction in progress should be rolled
> >> > back
> >> > and the connection closed. Is there a possibility that this isn't
> >> > always
> >> > the
> >> > case? If so is there anything we can really do in .NET to make the
> >> > timeout
> >> > clean up resources any more safely? Would catching
> >> > ThreadAbortException
> >> > and
> >> > closing the connection explicitly help at all?
> >> >
> >> > We're not using a domain logon environment. This is coming from a
> >> > webserver
> >> > frontend.
> >> >
> >> > "Russell Fields" wrote:
> >> >
> >> >> Demi,
> >> >>
> >> >> Perhaps you have an orphaned connection, as mentioned here:
> >> >> http://support.microsoft.com/kb/137983/en-us
> >> >> and at the end of this article:
> >> >> http://support.microsoft.com/kb/224453/en-us
> >> >>
> >> >> The solution offered for 2005 is the KILL command:
> >> >> http://msdn2.microsoft.com/en-us/library/ms173730.aspx
> >> >>
> >> >> Also, I have found that sometimes having the computer that made the
> >> >> connection log off of the domain, and then log back in again, will
> >> >> alert
> >> >> SQL
> >> >> Server that all is not well with the existing transaction and it will
> >> >> roll
> >> >> back. (Kind of like it 'sees' the computer login, says to itself
> >> >> "Then
> >> >> this
> >> >> transaction must be junk", and throws it away.)
> >> >>
> >> >> RLF
> >> >>
> >> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> >> >> news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
> >> >> >
> >> >> > Thanks. I will find out which statement, but it's going to be one
> >> >> > delete
> >> >> > statement or the other and both are trivial. Since this SP call is
> >> >> > the
> >> >> > head
> >> >> > blocker I have no information that I can see about what made IT
> >> >> > block.
> >> >> > It
> >> >> > has no parent blocker, it's just stuck. How can I proceed
> >> >> > investigating?
> >> >> >
> >> >> > "Russell Fields" wrote:
> >> >> >
> >> >> >> Demi,
> >> >> >>
> >> >> >> I don't think that the CREATE PROCEDURE is blocking anything, but
> >> >> >> someone
> >> >> >> is
> >> >> >> running this procedure and it is blocking on some internal step.
> >> >> >> If
> >> >> >> you
> >> >> >> are
> >> >> >> getting this string from sys.dm_exec_sql_text you will need to look
> >> >> >> at
> >> >> >> the
> >> >> >> start and end offsets to see which statement in the procedure is
> >> >> >> actually
> >> >> >> running.
> >> >> >>
> >> >> >> You can get these from:
> >> >> >>
> >> >> >> select sql_handle, statement_start_offset, statement_end_offset
> >> >> >> from
> >> >> >> sys.dm_exec_requests
> >> >> >>
> >> >> >> RLF
> >> >> >>
> >> >> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
> >> >> >> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
> >> >> >> >I don't understand why a CREATE PROC from 3.5 hours ago is the
> >> >> >> >head
> >> >> >> >blocker.
> >> >> >> > The SP wasn't changed since in months so this must be a
> >> >> >> > compile/recompile?
> >> >> >> > It's not a COMPILE lock though but a PAGE lock. It's certainly
> >> >> >> > not
> >> >> >> > the
> >> >> >> > initial creation. What else can be done to debug this? It's
> >> >> >> > happening
> >> >> >> > and
> >> >> >> > usually resolving itself in 4-6 hours or if I kill the head
> >> >> >> > blocker
> >> >> >> > myself.
> >> >> >> > It doesn't happen every day but almost every day.
> >> >> >> >
> >> >> >> > I've retrieved this info about the blocking from DMVStats and
> >> >> >> > found
> >> >> >> > similar
> >> >> >> > info using my regular blocking info script also pulling info from
> >> >> >> > DMV's.
> >> >> >> >
> >> >> >> > I'm running SQL 2005 SP2 Enterprise.
> >> >> >> >
> >> >> >> > statement started 6:35AM
> >> >> >> > waiting statement (PAGE lock):
> >> >> >> >
> >> >> >> > insert into grades (blah,blah)
> >> >> >> > select blah blah from homework join blah blah
> >> >> >> >
> >> >> >> > statement started at 3:01AM
> >> >> >> > blocking statement: =============================================> >> >> >> > -- Author: <Author,,Name>
> >> >> >> > -- Create date: <Create Date,,>
> >> >> >> > -- Description: <Description,,>
> >> >> >> > -- =============================================> >> >> >> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
> >> >> >> > @.homeworkResultId int,
> >> >> >> > @.NumDeleted int output
> >> >> >> > AS
> >> >> >> >
> >> >> >> > delete homeworksessions where homeworkresult_id => >> >> >> > @.homeworkresultId
> >> >> >> >
> >> >> >> > set @.NumDeleted=@.@.ROWCOUNT
> >> >> >> >
> >> >> >> > delete homeworkresults where homeworkresult_id=@.homeworkresultId
> >> >> >> >
> >> >> >> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||I have no idea what the Server.ScriptTimeout does when it fires. If it
doesn't fire the CATCH then it doesn't sound like something you don't want
to set. I think this might be better if you posted in the C# newsgroups to
see why the CATCH isn't firing. This isn't a SQL Server issue per say and C#
isn't my forte so I probably can't provide any more useful assistance.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:2D2C60B4-C724-4736-B5D7-FF967F48B956@.microsoft.com...
> I've created a simplified example with all the source. You need to run it
> with compilation debug="false". I run a profiler trace as well looking at
> all the transaction events. There's a begin but no end when the page
> (script) timeout hits. If there's a sql timeout then the handling is
> fine,
> but the page timeout results in bad behavior. You can confirm this by
> seeing
> the open transaction for the spid that executed the statement.
> Server.ScriptTimeout = 5;
> using (SqlConnection connection => new
> SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
> {
> using (SqlCommand command => connection.CreateCommand())
> {
> SqlTransaction transaction = null;
> command.CommandTimeout = 90;
> try
> {
> connection.Open();
> transaction = connection.BeginTransaction();
> command.Transaction = transaction;
> command.CommandText = "WAITFOR DELAY '00:00:10'";
> command.ExecuteNonQuery();
> transaction.Commit();
> }
> catch
> {
> transaction.Rollback();
> throw;
> }
> finally
> {
> connection.Close();
> }
> }
> }
> "Andrew J. Kelly" wrote:
>> I don't know what that line of code is actually doing but it can't really
>> be
>> closing the connection or it would roll back. I assume you are using a
>> connection pool and this may have something to do with it. In any case I
>> would think the code should be such that it checks for open trans and
>> rolls
>> them back if any are found before it closes the conn.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> news:6A00F3ED-9C0B-4E41-87EF-0DAEA200F600@.microsoft.com...
>> >
>> > If the Server.ScriptTimeout expires while my
>> > SqlCommand.ExecuteNonQuery()
>> > is
>> > running, "System.Web.HttpException: Request timed out" is thrown and my
>> > connection is closed before the first line of the catch so the
>> > transaction
>> > is
>> > not rolled back leaving an open transaction for that spid in Sql
>> > Server.
>> > This is very bad and seems like a Framework bug.
>> >
>> > try
>> > {
>> > if (_SqlConn == null)
>> > {
>> > _SqlConn = OpenConnection();
>> > }
>> > else if (_SqlConn.State == ConnectionState.Closed)
>> > {
>> > _SqlConn.Open();
>> > }
>> >
>> > // GetCommand creates a command and begins a transaction
>> > using (SqlCommand cmd = GetCommand(_SqlConn))
>> > {
>> > if (_Timeout > 0)
>> > cmd.CommandTimeout = _Timeout;
>> > cmd.ExecuteNonQuery();
>> > }
>> > }
>> > catch
>> > {
>> > HttpContext.Current.Trace.Write("DB", "Catch" +
>> > ((_SqlConn
>> > == null) ? "no conn" : _SqlConn.State.ToString()));
>> > // At first line in the catch the conn is closed
>> > _SqlConn.State == Closed
>> > // but the transaction is not rolled back
>> >
>> > CloseConnection(); // this does a rollback if a
>> > transaction
>> > exists, but the db connection is closed so the rollback doesn't work
>> > }
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Closing the connection completely should roll back any open
>> >> transactions
>> >> but
>> >> you can use @.@.TRANCOUNT to see if there are any open trans on that
>> >> connection. If so issue a ROLLBACK and then close the connection.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> >> news:F9CBE982-C2AB-4770-8180-0F3EE272B04B@.microsoft.com...
>> >> >
>> >> > That's an interesting theory. We do have an overnight process
>> >> > kicked
>> >> > off
>> >> > from a http handler that runs for 30 minutes until the page timeout
>> >> > kicks
>> >> > in.
>> >> > The job has to run off hours and is essentially a processing loop so
>> >> > we
>> >> > limit how long it can go and it often doesn't have time to finish
>> >> > all
>> >> > items
>> >> > some days before the timeout. We have the database connection
>> >> > inside a
>> >> > "using" block so I figured the transaction in progress should be
>> >> > rolled
>> >> > back
>> >> > and the connection closed. Is there a possibility that this isn't
>> >> > always
>> >> > the
>> >> > case? If so is there anything we can really do in .NET to make the
>> >> > timeout
>> >> > clean up resources any more safely? Would catching
>> >> > ThreadAbortException
>> >> > and
>> >> > closing the connection explicitly help at all?
>> >> >
>> >> > We're not using a domain logon environment. This is coming from a
>> >> > webserver
>> >> > frontend.
>> >> >
>> >> > "Russell Fields" wrote:
>> >> >
>> >> >> Demi,
>> >> >>
>> >> >> Perhaps you have an orphaned connection, as mentioned here:
>> >> >> http://support.microsoft.com/kb/137983/en-us
>> >> >> and at the end of this article:
>> >> >> http://support.microsoft.com/kb/224453/en-us
>> >> >>
>> >> >> The solution offered for 2005 is the KILL command:
>> >> >> http://msdn2.microsoft.com/en-us/library/ms173730.aspx
>> >> >>
>> >> >> Also, I have found that sometimes having the computer that made the
>> >> >> connection log off of the domain, and then log back in again, will
>> >> >> alert
>> >> >> SQL
>> >> >> Server that all is not well with the existing transaction and it
>> >> >> will
>> >> >> roll
>> >> >> back. (Kind of like it 'sees' the computer login, says to itself
>> >> >> "Then
>> >> >> this
>> >> >> transaction must be junk", and throws it away.)
>> >> >>
>> >> >> RLF
>> >> >>
>> >> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> >> >> news:21C07BE7-E2F4-4A3F-B7D8-24D57C09DE10@.microsoft.com...
>> >> >> >
>> >> >> > Thanks. I will find out which statement, but it's going to be
>> >> >> > one
>> >> >> > delete
>> >> >> > statement or the other and both are trivial. Since this SP call
>> >> >> > is
>> >> >> > the
>> >> >> > head
>> >> >> > blocker I have no information that I can see about what made IT
>> >> >> > block.
>> >> >> > It
>> >> >> > has no parent blocker, it's just stuck. How can I proceed
>> >> >> > investigating?
>> >> >> >
>> >> >> > "Russell Fields" wrote:
>> >> >> >
>> >> >> >> Demi,
>> >> >> >>
>> >> >> >> I don't think that the CREATE PROCEDURE is blocking anything,
>> >> >> >> but
>> >> >> >> someone
>> >> >> >> is
>> >> >> >> running this procedure and it is blocking on some internal step.
>> >> >> >> If
>> >> >> >> you
>> >> >> >> are
>> >> >> >> getting this string from sys.dm_exec_sql_text you will need to
>> >> >> >> look
>> >> >> >> at
>> >> >> >> the
>> >> >> >> start and end offsets to see which statement in the procedure is
>> >> >> >> actually
>> >> >> >> running.
>> >> >> >>
>> >> >> >> You can get these from:
>> >> >> >>
>> >> >> >> select sql_handle, statement_start_offset, statement_end_offset
>> >> >> >> from
>> >> >> >> sys.dm_exec_requests
>> >> >> >>
>> >> >> >> RLF
>> >> >> >>
>> >> >> >> "Demi" <Demi@.discussions.microsoft.com> wrote in message
>> >> >> >> news:9DDA2A4C-F1E3-47B1-B523-28ECD0CAEC3A@.microsoft.com...
>> >> >> >> >I don't understand why a CREATE PROC from 3.5 hours ago is the
>> >> >> >> >head
>> >> >> >> >blocker.
>> >> >> >> > The SP wasn't changed since in months so this must be a
>> >> >> >> > compile/recompile?
>> >> >> >> > It's not a COMPILE lock though but a PAGE lock. It's certainly
>> >> >> >> > not
>> >> >> >> > the
>> >> >> >> > initial creation. What else can be done to debug this? It's
>> >> >> >> > happening
>> >> >> >> > and
>> >> >> >> > usually resolving itself in 4-6 hours or if I kill the head
>> >> >> >> > blocker
>> >> >> >> > myself.
>> >> >> >> > It doesn't happen every day but almost every day.
>> >> >> >> >
>> >> >> >> > I've retrieved this info about the blocking from DMVStats and
>> >> >> >> > found
>> >> >> >> > similar
>> >> >> >> > info using my regular blocking info script also pulling info
>> >> >> >> > from
>> >> >> >> > DMV's.
>> >> >> >> >
>> >> >> >> > I'm running SQL 2005 SP2 Enterprise.
>> >> >> >> >
>> >> >> >> > statement started 6:35AM
>> >> >> >> > waiting statement (PAGE lock):
>> >> >> >> >
>> >> >> >> > insert into grades (blah,blah)
>> >> >> >> > select blah blah from homework join blah blah
>> >> >> >> >
>> >> >> >> > statement started at 3:01AM
>> >> >> >> > blocking statement:
>> >> >> >> > =============================================>> >> >> >> > -- Author: <Author,,Name>
>> >> >> >> > -- Create date: <Create Date,,>
>> >> >> >> > -- Description: <Description,,>
>> >> >> >> > -- =============================================>> >> >> >> > CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
>> >> >> >> > @.homeworkResultId int,
>> >> >> >> > @.NumDeleted int output
>> >> >> >> > AS
>> >> >> >> >
>> >> >> >> > delete homeworksessions where homeworkresult_id =>> >> >> >> > @.homeworkresultId
>> >> >> >> >
>> >> >> >> > set @.NumDeleted=@.@.ROWCOUNT
>> >> >> >> >
>> >> >> >> > delete homeworkresults where
>> >> >> >> > homeworkresult_id=@.homeworkresultId
>> >> >> >> >
>> >> >> >> > set @.NumDeleted=@.NumDeleted + @.@.ROWCOUNT
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>
![Embarrassed [:$]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_ulY4EVigK_yJRzUB7tjq8jjA8pleioZxkbEO1-Nh7brD1P-h-PtwGtLqmuIQr0I0iXY2Z-fildPdLAUXqutwjR3qON-0ESXoHXvxr2x_wS2tdqEn0RNA=s0-d)
