Is there a way to process multiple SQL scripts in a single transaction using
osql.exe ?
Ideally, I'd like to be able to start transaction, execute multiple sql
scripts and commit transaction when all of them succeeded or rollback when
there was a failure.
I appreciate your help.
I would copy and paste them together.
-jens S=FC=DFmeyer.
|||"Marek" <nospam@.nowhere.com> wrote in
news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
> Is there a way to process multiple SQL scripts in a single transaction
> using osql.exe ?
> Ideally, I'd like to be able to start transaction, execute multiple
> sql scripts and commit transaction when all of them succeeded or
> rollback when there was a failure.
> I appreciate your help.
As far as I know this is not possible in osql, but if you try sqlcmd from
SQL Server 2005, I do believe that you can. However, I have not tried this.
SqlCmd is also available in SQL Server 2005 Express, which is free.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16:
> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd
> from SQL Server 2005, I do believe that you can. However, I have not
> tried this. SqlCmd is also available in SQL Server 2005 Express, which
> is free.
And as Tibor so kindly answered in another group: You can download SqlCmd
here:
http://www.microsoft.com/downloads/d...D09C1D60-A13C-
4479-9B91-9E8B9D835CDC&displaylang=en
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||> As far as I know this is not possible in osql
I guess you could try the :r option to have OSQL read sub-scripts from a mother script. The
sub-scripts cannot have GO. A test is needed to verify that new connections will not be opened for
subscripts, use Profiler for that. Also, :r is not documented for OSQL, but I think it is for
SQLCMD. However, I don't think that error handling will be the easiest thing to accomplish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16...
> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd from
> SQL Server 2005, I do believe that you can. However, I have not tried this.
> SqlCmd is also available in SQL Server 2005 Express, which is free.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Monday, March 12, 2012
Monday, February 20, 2012
Orphaned transaction in sql 2005
Once in a while we end up with an orphaned transaction in sql 2005 around
the same application and it holds locks and the only way to get rid of it is
kill it.
So what causes it ? According to the app owner, they close the connection
and I have to say they do because the app is called so often and 99% of the
time it works fine and then once in 2 or 3 months, it leaves the orphaned
transaction.
The driver is a Java driver.. But I've seen this behavior on non Java
drivers as well.
So does SQL have any way of automatically killing orphaned transactions
Thank youRomeo,
The problem is that the SQL Server does not know it is an orphaned
transaction. If it knew, it would drop the transaction.
My anecdotal experience has been, if the machine that initiated the orphaned
transaction is logged off the domain, then logged back in the orphaned
transaction will ofter terminate. Apparently (real non-technical here) the
login back into the domain lets the SQL Server say to itself, "Hey, this
transaction must be orphaned because the machine I am doing it for just
logged in." But, I don't think I have ever seen this documented. (And it
is still a pain in the neck to track down the user and get them to log out,
then log back in.)
For orphaned DTC transactions, the SPID is -2, which you can find and kill.
This is documented in sp_who.
http://msdn2.microsoft.com/en-us/library/ms174313.aspx
RLF
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you|||> So does SQL have any way of automatically killing orphaned transactions
SQL Server will rollback an open transaction when the client disconnects so
I suspect that network connection is still open. Get the value of
client_tcp_port from sys,dm_exec_connections and check against a netstat
list on the client machine. If you see that the port is in use according to
netstat, then the connection is still open as far as the network layer and
SQL Server are concerned.
I don't know anything about your application other than you are using a java
driver with a SQL 2005 back end. Most drivers support some form of
connection pooling which means that a connection isn't actually closed when
the application closes the connection; the connection is simply returned to
the pool and is available for reuse. If a transaction is open when a pooled
connection is closed, it could remain open until the connection is either
reused or removed from the pool. The exact behavior depends on a number of
factors, such as the specific driver and how the transaction was initiated
(server vs. client API).
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
It may be than all is fine as long as the expected code path is executed.
Make sure that the application cleans up (e;g; WHILE @.@.TRANCOUNT > 0
ROLLBACK and close connection) following exceptions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you
the same application and it holds locks and the only way to get rid of it is
kill it.
So what causes it ? According to the app owner, they close the connection
and I have to say they do because the app is called so often and 99% of the
time it works fine and then once in 2 or 3 months, it leaves the orphaned
transaction.
The driver is a Java driver.. But I've seen this behavior on non Java
drivers as well.
So does SQL have any way of automatically killing orphaned transactions
Thank youRomeo,
The problem is that the SQL Server does not know it is an orphaned
transaction. If it knew, it would drop the transaction.
My anecdotal experience has been, if the machine that initiated the orphaned
transaction is logged off the domain, then logged back in the orphaned
transaction will ofter terminate. Apparently (real non-technical here) the
login back into the domain lets the SQL Server say to itself, "Hey, this
transaction must be orphaned because the machine I am doing it for just
logged in." But, I don't think I have ever seen this documented. (And it
is still a pain in the neck to track down the user and get them to log out,
then log back in.)
For orphaned DTC transactions, the SPID is -2, which you can find and kill.
This is documented in sp_who.
http://msdn2.microsoft.com/en-us/library/ms174313.aspx
RLF
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you|||> So does SQL have any way of automatically killing orphaned transactions
SQL Server will rollback an open transaction when the client disconnects so
I suspect that network connection is still open. Get the value of
client_tcp_port from sys,dm_exec_connections and check against a netstat
list on the client machine. If you see that the port is in use according to
netstat, then the connection is still open as far as the network layer and
SQL Server are concerned.
I don't know anything about your application other than you are using a java
driver with a SQL 2005 back end. Most drivers support some form of
connection pooling which means that a connection isn't actually closed when
the application closes the connection; the connection is simply returned to
the pool and is available for reuse. If a transaction is open when a pooled
connection is closed, it could remain open until the connection is either
reused or removed from the pool. The exact behavior depends on a number of
factors, such as the specific driver and how the transaction was initiated
(server vs. client API).
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
It may be than all is fine as long as the expected code path is executed.
Make sure that the application cleans up (e;g; WHILE @.@.TRANCOUNT > 0
ROLLBACK and close connection) following exceptions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Romeo" <romeo@.juliet.com> wrote in message
news:e2mfGWVZIHA.208@.TK2MSFTNGP02.phx.gbl...
> Once in a while we end up with an orphaned transaction in sql 2005 around
> the same application and it holds locks and the only way to get rid of it
> is kill it.
> So what causes it ? According to the app owner, they close the connection
> and I have to say they do because the app is called so often and 99% of
> the time it works fine and then once in 2 or 3 months, it leaves the
> orphaned transaction.
> The driver is a Java driver.. But I've seen this behavior on non Java
> drivers as well.
> So does SQL have any way of automatically killing orphaned transactions
> Thank you
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
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
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
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
Subscribe to:
Posts (Atom)