OutOfMemoryException with SQL Server Mobile

Hi,

I'm getting always an OutOfMemoryException after my program runs for a while. It's a smartphone application running on .NET 2.0 CF and using the SQL Server Mobile 2005. Sometimes it's even not a OutOfMemoryException but a SqlCeException. But it's always when the phone is having little free memory left.

I have also created a few custom controls (I even implemented the Disposable interface) to dispose all resources (Fonts, Bitmaps). I'm using all Pens and Brushes always in "using" blocks, to have them disposed immediately after having used them. The program does not have any static objects or lists of objects that are hold in memory for a long time.

I really don't understand why I get this exception. I see it always happen after doing a lot queries on the SQL Server Mobile. Is this tool using a lot memory? Can I flush somehow the memory of the SQL Server? Should I run also manually sometimes the GarbageCollector? Does this thing not run automatically?

Can anybody, perhaps of MSFT help me? I'm also willing to share the full source code with MSFT, if required.

You will probably get a faster answer on the Sql Server Mobile forum.

Moving this thread to that forum.

|||

Here are a few suggestions (from an earlier thread)

1) Try to use SqlCeResultSet than DataSet

2) Please dispose the managed objects explicitly instead of waiting for garbage collector to pick them up. Please also note that SqlCeCommand object can be reused and they need to be disposed explicitly

In addtion you can email me your project and I'll be glad to look into this issue for you.

Mark dot Ihimoyan at microsoft dot com

OUTER JOIN table limit?

I came across this statement from ASP.NET forum : "..There is a limit to the level for OUTER JOIN ANSI SQL limit is four after that you may get strange results. ..." . I did a little research but without getting clear answer from the SQL92 standard itself. I am wondering whether I can get help about this in SQL Server 2005 implementation here.

I put this question in another way, How many tables can we use in OUTER JOIN(or INNER JOIN) in SQL Server 2005?

Thanks.

There is no limit in the ANSI SQL standard or SQL Server. In fact, the ANSI SQL standard doesn't talk about such limits anywhere. They provide specifications on the syntax and how it should work etc. Note that a particular database implementation can have limits imposed. SQL Server 2005 for example has a maximum limit of 256 table references in a SELECT statement. So you can end up in some situation where the query optimizer cannot produce a plan due to insufficient resources in the system if the query is too complex and contains large number of table references. See below example for SQL Server 2005 which allows more than 4 outer joins:

with t(i)
as (
select 1
)
select *
from t as t1
left join t as t2
left join t as t3
left join t as t4
left join t as t5
left join t as t6
left join t as t7
left join t as t8
left join t as t9
left join t as t10
on t10.i = t9.i
on t9.i = t8.i
on t8.i = t7.i
on t7.i = t6.i
on t6.i = t5.i
on t5.i = t4.i
on t4.i = t3.i
on t3.i = t2.i
on t2.i = t1.i

|||

as far as sql server is concerned there is no limitation.

it could have been a limitation from data access tier

e.g. dataset

|||

Hello:

I was pointed to this "Three-Way Joins and Beyond" in SQL Pperformance Tuning by Peter Gulutzan and Trudy Pelzer.

Can I get some explaination for this statement: "You can expect the DBMS optimizer to start going wrong if five or more joins are in the query (until recently Microsoft's admitted limit was four). "

Is there anything I miss here?

This book was published on September 10, 2002.

Thank you.

|||

That article makes several incorrect assumptions and looks like the authors are not well informed about SQL Server. For example, the support for recognizing transitive predicates has been in the product since SQL Server 7.0. See link below:

http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part9/sqc13.mspx?mfr=true

I didn't go through all the chapters but the first page itself has several errors and doesn't apply to SQL Server. Performance of joins involving large number of tables can be a problem depending on the resources and the query plan. This has also been improved considerably in the product for every release starting from SQL 6x. If you are looking for querying tips for SQL Server, you may want to look at some of the new Inside SQL Server 2005 series for example.

|||

Hi Umachandar:

Thank you for answering this question. I used multiple outer joins to extract a dataset from my database. That table limit statement could pose a serious problem to my query if it were true for SQL Server 2000 or 2005.

outer join

I need help with a outer join.

I have a table called raw_data_info ..
raw_data_id raw_data_name
1 net income
2 net assets

I have another table called raw_data
raw_data_id report_date amount
1 2/28/2000 50

I need to write a sql statement that will return the below

raw_data_name amount
net income 50
net assets

I want a blank for net assets.

Is this possible?It is possible:

select a.raw_data_name, b.amount
from
raw_data_info a, outer raw_data b
where
a.raw_data_id = b._raw_data_id

you just have to use the outer on the table that you want to get nulls from :)|||I am sorry. I left one column out. I didn't think it mattered, but I see it does now. I am doing this in Access.

The tables should be -- raw_data_info
raw_data_id -- raw_data_name
1 -- net income
2 -- net assets

I have another table called raw_data
raw_data_id -- report_date -- cmpy_num -- amount
1 -- 2/28/2000 -- 9999 -- 50
1 -- 2/28/2000 -- 8888 -- 100

I tired (but it didn't work) --

SELECT I.mdy_raw_data_name, D.amount
FROM raw_data_info AS I LEFT JOIN raw_data AS D ON I.raw_data_id=D.raw_data_id
WHERE (report_date=#2/28/1999# Or report_date Is Null)

Is there any way to return --
raw_data_name -- amount -- cmpy_num
net income -- 50 -- 9999
net assets -- -- 9999
net income -- 100 -- 8888
net assets -- -- 8888|||select I.mdy_raw_data_name
, D.amount
from raw_data_info AS I
left outer
join raw_data AS D
on (
I.raw_data_id = D.raw_data_id
and iif(isnull(D.report_date)
,#2/28/1999#
,D.report_date) = #2/28/1999#
)|||Thank you so much!!sql

OT: What news reader do you use

I have been out of the Usenet groups for some time. I have been hiding
on the DotNetNuke forum on ASP.Net.
I am impressed with the google web based reader but was wondering what
others are using these days.
BertOutlook Express
--
Keith
"bert" <bertcord@.gmail.com> wrote in message
news:1107354025.448962.275710@.z14g2000cwz.googlegroups.com...
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||I use Mozilla Thunderbird 1.0.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Outlook Express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"bert" <bertcord@.gmail.com> wrote in message
news:1107354025.448962.275710@.z14g2000cwz.googlegroups.com...
>I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Outlook Express and Google
"bert" <bertcord@.gmail.com> wrote in message
news:1107354025.448962.275710@.z14g2000cwz.googlegroups.com...
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||"bert" <bertcord@.gmail.com> wrote in news:1107354025.448962.275710
@.z14g2000cwz.googlegroups.com:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
Xnews.|||Hi Bert,
I use Agent. (Version 1.91, to be specific)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you like the Google Web interface than you may want to try
www.sqlmonster.com. In my opinion it is more convenient than Google.
--
Message posted via http://www.sqlmonster.com|||I am probably one of the few users that uses Netscape 4.79.
Gert-Jan
Hugo Kornelis wrote:
> Hi Bert,
> I use Agent. (Version 1.91, to be specific)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||bert,
Why not look through the headers of all the posts in this group? You
will then get a pretty good idea what people use. What you will notice
is that quite a few people post through the microsoft website.
For example on my postings, you will usually see something like this in
the headers:
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
For Tibor you will see this:
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
For Celko you will see he uses Google:
User-Agent: G2/0.2
For website users you will see this:
X-Newsreader: Microsoft CDO for Windows 2000
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Gnus.
--
Galen Boyer

OT: What news reader do you use

I have been out of the Usenet groups for some time. I have been hiding
on the DotNetNuke forum on ASP.Net.
I am impressed with the google web based reader but was wondering what
others are using these days.
Bert
I use Mozilla Thunderbird 1.0.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
|||"bert" <bertcord@.gmail.com> wrote in news:1107354025.448962.275710
@.z14g2000cwz.googlegroups.com:

> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
Xnews.
|||Hi Bert,
I use Agent. (Version 1.91, to be specific)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||If you like the Google Web interface than you may want to try
www.sqlmonster.com. In my opinion it is more convenient than Google.
Message posted via http://www.sqlmonster.com
|||I am probably one of the few users that uses Netscape 4.79.
Gert-Jan
Hugo Kornelis wrote:
> Hi Bert,
> I use Agent. (Version 1.91, to be specific)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||bert,
Why not look through the headers of all the posts in this group? You
will then get a pretty good idea what people use. What you will notice
is that quite a few people post through the microsoft website.
For example on my postings, you will usually see something like this in
the headers:
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
For Tibor you will see this:
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
For Celko you will see he uses Google:
User-Agent: G2/0.2
For website users you will see this:
X-Newsreader: Microsoft CDO for Windows 2000
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
|||Gnus.
Galen Boyer

OT: What news reader do you use

I have been out of the Usenet groups for some time. I have been hiding
on the DotNetNuke forum on ASP.Net.
I am impressed with the google web based reader but was wondering what
others are using these days.
BertI use Mozilla Thunderbird 1.0.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||"bert" <bertcord@.gmail.com> wrote in news:1107354025.448962.275710
@.z14g2000cwz.googlegroups.com:

> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>
Xnews.|||Hi Bert,
I use Agent. (Version 1.91, to be specific)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you like the Google Web interface than you may want to try
www.droptable.com. In my opinion it is more convenient than Google.
Message posted via http://www.droptable.com|||I am probably one of the few users that uses netscape 4.79.
Gert-Jan
Hugo Kornelis wrote:
> Hi Bert,
> I use Agent. (Version 1.91, to be specific)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||bert,
Why not look through the headers of all the posts in this group? You
will then get a pretty good idea what people use. What you will notice
is that quite a few people post through the microsoft website.
For example on my postings, you will usually see something like this in
the headers:
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
For Tibor you will see this:
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
For Celko you will see he uses Google:
User-Agent: G2/0.2
For website users you will see this:
X-Newsreader: Microsoft CDO for Windows 2000
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
bert wrote:
> I have been out of the Usenet groups for some time. I have been hiding
> on the DotNetNuke forum on ASP.Net.
> I am impressed with the google web based reader but was wondering what
> others are using these days.
> Bert
>|||Gnus.
--
Galen Boyer

OT: Need Project Tracking software

Hi, sorry for the OT question but the readers opinions will matter more
to me.
I team lead a small development team of .net and sql programmers.
There is another team in our group like ours. We are tracking our
project requests and statuses in Excel as it is shared with people
ourside our teams who also need update abilities.
We're looking for a good project tracking software for teams like ours.
It can be web based preferrably.
PLEASE PLEASE do not tell me to have us write it ourselves. :-) We
can but need to focus on our dev projects.
can you readers suggest some of the 3rd party project tracking and
request applications that you use today that you would recommend?
Thanks!
> can you readers suggest some of the 3rd party project tracking and
> request applications that you use today that you would recommend?
>
www.spudcity.com/traxtime/traxtime.htm
small app, dead easy to use.
JAmie|||We use TRAC which is an integrated source control, bug tracker and
project management tool. It is free and open source.
http://projects.edgewall.com/trac
--Brian
Antonio wrote:
> Hi, sorry for the OT question but the readers opinions will matter more
> to me.
> I team lead a small development team of .net and sql programmers.
> There is another team in our group like ours. We are tracking our
> project requests and statuses in Excel as it is shared with people
> ourside our teams who also need update abilities.
> We're looking for a good project tracking software for teams like ours.
> It can be web based preferrably.
> PLEASE PLEASE do not tell me to have us write it ourselves. :-) We
> can but need to focus on our dev projects.
> can you readers suggest some of the 3rd party project tracking and
> request applications that you use today that you would recommend?
> Thanks!
>|||Of course no SW can help you if there are team members who won't use it.
Even a simple text file is great to track the progress of a project, if you
successfully persuade others to use it.
ML
http://milambda.blogspot.com/

Orphaned Sessions and locks

Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig
CB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:

> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests was
> to update a row in the database and break the network connection before the
> transaction could be commited. This locks the row in the database for about
> 5 minutes before Sql Server realises that the communication has been broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>
|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig

Orphaned Sessions and locks

Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests was
> to update a row in the database and break the network connection before the
> transaction could be commited. This locks the row in the database for about
> 5 minutes before Sql Server realises that the communication has been broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
--
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig

Orphaned Sessions and locks

Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:

> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests wa
s
> to update a row in the database and break the network connection before th
e
> transaction could be commited. This locks the row in the database for abo
ut
> 5 minutes before Sql Server realises that the communication has been broke
n,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig