I'm suspecting that one of the .NET applications that connects to our SQL
Server 2005 occasionnaly goes into an infinite loop while inside a
transaction.

Is there a way to force an error on the connection in case it is in a
transaction for more than a specific amount of time, say 30 seconds ?

What would you guys recommend to protect the database from such client
behaviour ? (we don't have much influence on how the clients are programmed)

Well I would fix the app first and foremost. But you can use a scheduled
job that checks DBCC OPENTRAN for transactions running longer than x many
seconds. Then use DBCC INPUTBUFFER or fn_Get_Sql to see what that SPID is
doing or look in sysprocesses if this app ahs a particular login etc.. If
you are sure it is the app then KILL the connection.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
news:49CEF947-C799-4742-8625-F878408B5094@microsoft.com...
> I'm suspecting that one of the .NET applications that connects to our SQL
> Server 2005 occasionnaly goes into an infinite loop while inside a
> transaction.
>
> Is there a way to force an error on the connection in case it is in a
> transaction for more than a specific amount of time, say 30 seconds ?
>
> What would you guys recommend to protect the database from such client
> behaviour ? (we don't have much influence on how the clients are
> programmed)

To start, I'd run DBCC OPENTRAN to spot check if there is any long running
tran.

You can write a T-SQL script to take snapshots of
sys.dm_tran_active_transactions DMV in a loop, and if a transaction runs
longer than 30 seconds, log an error yourself.

BTW, what made you suspect that an application was going into an infinite
loop while inside a transaction?

Linchi

"AlanPorter" wrote:

> I'm suspecting that one of the .NET applications that connects to our SQL
> Server 2005 occasionnaly goes into an infinite loop while inside a
> transaction.
>
> Is there a way to force an error on the connection in case it is in a
> transaction for more than a specific amount of time, say 30 seconds ?
>
> What would you guys recommend to protect the database from such client
> behaviour ? (we don't have much influence on how the clients are programmed)

I'm new in this position (1 week, previous DBA left abruptly), and people are
mentionning occurences of the database being locked up. From what I've
gathered until now (a number of .NET apps and Excel-based apps are allowed to
read/write the DB tables directly) this sounds like a possible scenario.
Believe it or not this is a fairly critical DB, and we need to stop this
from happening. Even if if find the culprit using the methods you and Andrew
are suggesting, the same bug could be lurching in another client application,
or is about to be deployed by some junior .NET programmer.

It would be great if someone could suggest a generic solution (short of
blocking access to the tables, I've already suggested that and it won't
happen in the near future as it means changes to all of those client
applications)


"Linchi Shea" wrote:

> To start, I'd run DBCC OPENTRAN to spot check if there is any long running
> tran.
>
> You can write a T-SQL script to take snapshots of
> sys.dm_tran_active_transactions DMV in a loop, and if a transaction runs
> longer than 30 seconds, log an error yourself.
>
> BTW, what made you suspect that an application was going into an infinite
> loop while inside a transaction?
>
> Linchi
>
> "AlanPorter" wrote:
>
> > I'm suspecting that one of the .NET applications that connects to our SQL
> > Server 2005 occasionnaly goes into an infinite loop while inside a
> > transaction.
> >
> > Is there a way to force an error on the connection in case it is in a
> > transaction for more than a specific amount of time, say 30 seconds ?
> >
> > What would you guys recommend to protect the database from such client
> > behaviour ? (we don't have much influence on how the clients are programmed)

>> Believe it or not this is a fairly critical DB, and we need to stop this
>> from happening.

If this really is the case then the company should hire a consultant who can
diagnose the problem immediately and offer solutions. Hunting and pecking
for a solution on a news group can easily drag on for days with iterative
questions and answers and suggestions and still not get the problem
resolved.

You can also try Microsoft support to see if they can .

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
news:7687EE42-DF54-4BD9-B581-3176E36E80C0@microsoft.com...
> I'm new in this position (1 week, previous DBA left abruptly), and people
> are
> mentionning occurences of the database being locked up. From what I've
> gathered until now (a number of .NET apps and Excel-based apps are allowed
> to
> read/write the DB tables directly) this sounds like a possible scenario.
> Believe it or not this is a fairly critical DB, and we need to stop this
> from happening. Even if if find the culprit using the methods you and
> Andrew
> are suggesting, the same bug could be lurching in another client
> application,
> or is about to be deployed by some junior .NET programmer.
>
> It would be great if someone could suggest a generic solution (short of
> blocking access to the tables, I've already suggested that and it won't
> happen in the near future as it means changes to all of those client
> applications)
>
>
> "Linchi Shea" wrote:
>
>> To start, I'd run DBCC OPENTRAN to spot check if there is any long
>> running
>> tran.
>>
>> You can write a T-SQL script to take snapshots of
>> sys.dm_tran_active_transactions DMV in a loop, and if a transaction runs
>> longer than 30 seconds, log an error yourself.
>>
>> BTW, what made you suspect that an application was going into an infinite
>> loop while inside a transaction?
>>
>> Linchi
>>
>> "AlanPorter" wrote:
>>
>> > I'm suspecting that one of the .NET applications that connects to our
>> > SQL
>> > Server 2005 occasionnaly goes into an infinite loop while inside a
>> > transaction.
>> >
>> > Is there a way to force an error on the connection in case it is in a
>> > transaction for more than a specific amount of time, say 30 seconds ?
>> >
>> > What would you guys recommend to protect the database from such client
>> > behaviour ? (we don't have much influence on how the clients are
>> > programmed)

>>>It would be great if someone could suggest a generic solution (short of
blocking access to the tables,

What was wrong with what I suggested? It is about as generic as you can get.
If you are looking to fix the root cause this is about all you can do.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
news:7687EE42-DF54-4BD9-B581-3176E36E80C0@microsoft.com...
> I'm new in this position (1 week, previous DBA left abruptly), and people
> are
> mentionning occurences of the database being locked up. From what I've
> gathered until now (a number of .NET apps and Excel-based apps are allowed
> to
> read/write the DB tables directly) this sounds like a possible scenario.
> Believe it or not this is a fairly critical DB, and we need to stop this
> from happening. Even if if find the culprit using the methods you and
> Andrew
> are suggesting, the same bug could be lurching in another client
> application,
> or is about to be deployed by some junior .NET programmer.
>
> It would be great if someone could suggest a generic solution (short of
> blocking access to the tables, I've already suggested that and it won't
> happen in the near future as it means changes to all of those client
> applications)
>
>
> "Linchi Shea" wrote:
>
>> To start, I'd run DBCC OPENTRAN to spot check if there is any long
>> running
>> tran.
>>
>> You can write a T-SQL script to take snapshots of
>> sys.dm_tran_active_transactions DMV in a loop, and if a transaction runs
>> longer than 30 seconds, log an error yourself.
>>
>> BTW, what made you suspect that an application was going into an infinite
>> loop while inside a transaction?
>>
>> Linchi
>>
>> "AlanPorter" wrote:
>>
>> > I'm suspecting that one of the .NET applications that connects to our
>> > SQL
>> > Server 2005 occasionnaly goes into an infinite loop while inside a
>> > transaction.
>> >
>> > Is there a way to force an error on the connection in case it is in a
>> > transaction for more than a specific amount of time, say 30 seconds ?
>> >
>> > What would you guys recommend to protect the database from such client
>> > behaviour ? (we don't have much influence on how the clients are
>> > programmed)

I was looking for something that would prevent an outage, rather than fixing
the problem after it has started locking out people. Could RLV be a quick fix
until I get to know the system better ?

"Andrew J. Kelly" wrote:

> >>>It would be great if someone could suggest a generic solution (short of
> blocking access to the tables,
>
> What was wrong with what I suggested? It is about as generic as you can get.
> If you are looking to fix the root cause this is about all you can do.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
> news:7687EE42-DF54-4BD9-B581-3176E36E80C0@microsoft.com...
> > I'm new in this position (1 week, previous DBA left abruptly), and people
> > are
> > mentionning occurences of the database being locked up. From what I've
> > gathered until now (a number of .NET apps and Excel-based apps are allowed
> > to
> > read/write the DB tables directly) this sounds like a possible scenario.
> > Believe it or not this is a fairly critical DB, and we need to stop this
> > from happening. Even if if find the culprit using the methods you and
> > Andrew
> > are suggesting, the same bug could be lurching in another client
> > application,
> > or is about to be deployed by some junior .NET programmer.
> >
> > It would be great if someone could suggest a generic solution (short of
> > blocking access to the tables, I've already suggested that and it won't
> > happen in the near future as it means changes to all of those client
> > applications)
> >
> >
> > "Linchi Shea" wrote:
> >
> >> To start, I'd run DBCC OPENTRAN to spot check if there is any long
> >> running
> >> tran.
> >>
> >> You can write a T-SQL script to take snapshots of
> >> sys.dm_tran_active_transactions DMV in a loop, and if a transaction runs
> >> longer than 30 seconds, log an error yourself.
> >>
> >> BTW, what made you suspect that an application was going into an infinite
> >> loop while inside a transaction?
> >>
> >> Linchi
> >>
> >> "AlanPorter" wrote:
> >>
> >> > I'm suspecting that one of the .NET applications that connects to our
> >> > SQL
> >> > Server 2005 occasionnaly goes into an infinite loop while inside a
> >> > transaction.
> >> >
> >> > Is there a way to force an error on the connection in case it is in a
> >> > transaction for more than a specific amount of time, say 30 seconds ?
> >> >
> >> > What would you guys recommend to protect the database from such client
> >> > behaviour ? (we don't have much influence on how the clients are
> >> > programmed)
>
>

What is RLV? The only way I know of to prevent this is to fix the app.
There is no way to know from the server standpoint what the clients
intentions are and there would be no reasonable way to determine a good
transaction from a bad one. If none of the normal transactions will ever
last more than xx seconds you can kill the ones open longer than that but
that is about as far as it goes. The db has to be flexible enough to let you
do what ever you need to for your applications business requirements. So it
doesn't have anything built in to protect against something like this
because that could be normal for other apps. You could also base this on
how much blocking a particular spid is doing instead of just the length of
the tran but that too can be tricky to tell good from bad.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
news:05375AC6-26F3-42D8-B84E-9FC173EC1C4D@microsoft.com...
>I was looking for something that would prevent an outage, rather than
>fixing
> the problem after it has started locking out people. Could RLV be a quick
> fix
> until I get to know the system better ?
>
> "Andrew J. Kelly" wrote:
>
>> >>>It would be great if someone could suggest a generic solution (short
>> >>>of
>> blocking access to the tables,
>>
>> What was wrong with what I suggested? It is about as generic as you can
>> get.
>> If you are looking to fix the root cause this is about all you can do.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
>> news:7687EE42-DF54-4BD9-B581-3176E36E80C0@microsoft.com...
>> > I'm new in this position (1 week, previous DBA left abruptly), and
>> > people
>> > are
>> > mentionning occurences of the database being locked up. From what I've
>> > gathered until now (a number of .NET apps and Excel-based apps are
>> > allowed
>> > to
>> > read/write the DB tables directly) this sounds like a possible
>> > scenario.
>> > Believe it or not this is a fairly critical DB, and we need to stop
>> > this
>> > from happening. Even if if find the culprit using the methods you and
>> > Andrew
>> > are suggesting, the same bug could be lurching in another client
>> > application,
>> > or is about to be deployed by some junior .NET programmer.
>> >
>> > It would be great if someone could suggest a generic solution (short of
>> > blocking access to the tables, I've already suggested that and it won't
>> > happen in the near future as it means changes to all of those client
>> > applications)
>> >
>> >
>> > "Linchi Shea" wrote:
>> >
>> >> To start, I'd run DBCC OPENTRAN to spot check if there is any long
>> >> running
>> >> tran.
>> >>
>> >> You can write a T-SQL script to take snapshots of
>> >> sys.dm_tran_active_transactions DMV in a loop, and if a transaction
>> >> runs
>> >> longer than 30 seconds, log an error yourself.
>> >>
>> >> BTW, what made you suspect that an application was going into an
>> >> infinite
>> >> loop while inside a transaction?
>> >>
>> >> Linchi
>> >>
>> >> "AlanPorter" wrote:
>> >>
>> >> > I'm suspecting that one of the .NET applications that connects to
>> >> > our
>> >> > SQL
>> >> > Server 2005 occasionnaly goes into an infinite loop while inside a
>> >> > transaction.
>> >> >
>> >> > Is there a way to force an error on the connection in case it is in
>> >> > a
>> >> > transaction for more than a specific amount of time, say 30 seconds
>> >> > ?
>> >> >
>> >> > What would you guys recommend to protect the database from such
>> >> > client
>> >> > behaviour ? (we don't have much influence on how the clients are
>> >> > programmed)
>>
>>

If by RLV you mean Row Level Locking, you can set yourself up for
more/different heartache if you force that and also IIRC lock escalation
will still take place if the engine determines it to be appropriate. Not
100% certain on that however.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
news:05375AC6-26F3-42D8-B84E-9FC173EC1C4D@microsoft.com...
>I was looking for something that would prevent an outage, rather than
>fixing
> the problem after it has started locking out people. Could RLV be a quick
> fix
> until I get to know the system better ?
>
> "Andrew J. Kelly" wrote:
>
>> >>>It would be great if someone could suggest a generic solution (short
>> >>>of
>> blocking access to the tables,
>>
>> What was wrong with what I suggested? It is about as generic as you can
>> get.
>> If you are looking to fix the root cause this is about all you can do.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>>
>> "AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
>> news:7687EE42-DF54-4BD9-B581-3176E36E80C0@microsoft.com...
>> > I'm new in this position (1 week, previous DBA left abruptly), and
>> > people
>> > are
>> > mentionning occurences of the database being locked up. From what I've
>> > gathered until now (a number of .NET apps and Excel-based apps are
>> > allowed
>> > to
>> > read/write the DB tables directly) this sounds like a possible
>> > scenario.
>> > Believe it or not this is a fairly critical DB, and we need to stop
>> > this
>> > from happening. Even if if find the culprit using the methods you and
>> > Andrew
>> > are suggesting, the same bug could be lurching in another client
>> > application,
>> > or is about to be deployed by some junior .NET programmer.
>> >
>> > It would be great if someone could suggest a generic solution (short of
>> > blocking access to the tables, I've already suggested that and it won't
>> > happen in the near future as it means changes to all of those client
>> > applications)
>> >
>> >
>> > "Linchi Shea" wrote:
>> >
>> >> To start, I'd run DBCC OPENTRAN to spot check if there is any long
>> >> running
>> >> tran.
>> >>
>> >> You can write a T-SQL script to take snapshots of
>> >> sys.dm_tran_active_transactions DMV in a loop, and if a transaction
>> >> runs
>> >> longer than 30 seconds, log an error yourself.
>> >>
>> >> BTW, what made you suspect that an application was going into an
>> >> infinite
>> >> loop while inside a transaction?
>> >>
>> >> Linchi
>> >>
>> >> "AlanPorter" wrote:
>> >>
>> >> > I'm suspecting that one of the .NET applications that connects to
>> >> > our
>> >> > SQL
>> >> > Server 2005 occasionnaly goes into an infinite loop while inside a
>> >> > transaction.
>> >> >
>> >> > Is there a way to force an error on the connection in case it is in
>> >> > a
>> >> > transaction for more than a specific amount of time, say 30 seconds
>> >> > ?
>> >> >
>> >> > What would you guys recommend to protect the database from such
>> >> > client
>> >> > behaviour ? (we don't have much influence on how the clients are
>> >> > programmed)
>>
>>

I mean Row Level Versioning. I don't have any experience with that,
apparently other connections wouldn't be blocked even if a bad behaved .NET
app stays in a transaction for a very long time. Other connections would just
get the version of the row that was valid before the bad behaved transaction
started.
This would allow us to bridge the time it will take to get the 10+ .NET
applications verified/rewritten.

Anyone has gone this path ?

It may if these is a situation where none of the other users need to
update any of those rows as well. If they do they will still be blocked. You
also run the risk of the app now getting old (pre-changed) data to make it's
decisions. If it wasn't written with this in mind you can get incorrect
behavior or results. Only you can make that call. But to be honest it should
be far simpler and faster to find the culprits than to determine if this
other behavior will affect your results or not. Using the methods mentioned
before and a simple trace you should easily be able to narrow down the
offending app and even to the section of the app (by looking at the code
called) that started the tran in which it never closed.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"AlanPorter" <AlanPorter@discussions.microsoft.com> wrote in message
news:798DC1FB-C412-4441-AA59-FD45FFACA3A4@microsoft.com...
>I mean Row Level Versioning. I don't have any experience with that,
> apparently other connections wouldn't be blocked even if a bad behaved
> .NET
> app stays in a transaction for a very long time. Other connections would
> just
> get the version of the row that was valid before the bad behaved
> transaction
> started.
> This would allow us to bridge the time it will take to get the 10+ .NET
> applications verified/rewritten.
>
> Anyone has gone this path ?