sql server transaction configuration.

hi all, couple really easy questions for the guru out there.

1. is there any way i can totally switch off transactions in sql server so
that even if there are several statements with in a begin and commit\rollbac
k
transaction , without waiting for either the commit or rollback , it just
commits all the changes.

2. If there is how can i swith it back to ON again.

3. Is there any default timeout value for a transaction ( not talking about
deadlock or locking timeouts) after which even if the query is doing inserts
\
updates it rollsback everthing.

please suggest.
thanks

Sameer,

Transactions are used for data modifications whether or not they are
explicitly called or not.  I think the only exception to this would be a
READ ONLY database.

HTH

Jerry
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com...
> hi all, couple really easy questions for the guru out there.
>
> 1. is there any way i can totally switch off transactions in sql server so
> that even if there are several statements with in a begin and
> commit\rollback
> transaction , without waiting for either the commit or rollback , it just
> commits all the changes.
>
> 2. If there is how can i swith it back to ON again.
>
> 3. Is there any default timeout value for a transaction ( not talking
> about
> deadlock or locking timeouts) after which even if the query is doing
> inserts\
> updates it rollsback everthing.
>
> please suggest.
> thanks

Comments Inline
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com...
> hi all, couple really easy questions for the guru out there.
>
> 1. is there any way i can totally switch off transactions in sql server so
> that even if there are several statements with in a begin and
> commit\rollback
> transaction , without waiting for either the commit or rollback , it just
> commits all the changes.
>
No.

> 2. If there is how can i swith it back to ON again.
>
No.

> 3. Is there any default timeout value for a transaction ( not talking
> about
> deadlock or locking timeouts) after which even if the query is doing
> inserts\
> updates it rollsback everthing.
>
No.  You can create a maximum timeout for a resource wait, but this is a bad
thing as it tends to break transactional consistency.  Transaction timeout
is typically controlled via the client application.
> please suggest.
> thanks

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

thanks for your reply Geoff,
this one time when i was working on a sql server, i had couple insert
statements after a begin transaction and as soon as i ran it, it was almost
as if it was commited to the db even without a commint transaction. I could
not even roll it back, could you shed some light on this. I always though du
e
to some reason the dba might have swithed off the trasnaction handling and
made it such that changes are commited even if commit is not ran.

thanks
sameer

"Geoff N. Hiten" wrote:

> Comments Inline
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com... 
> No.
> 
> No.
> 
> No.  You can create a maximum timeout for a resource wait, but this is a b
ad
> thing as it tends to break transactional consistency.  Transaction timeout
> is typically controlled via the client application. 
>
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>

also is there any time out for abandoned transactions ?

"Geoff N. Hiten" wrote:

> Comments Inline
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:BD46BC49-4585-4F43-BB7A- 35164F5B0B2F@microso
ft.com... 
> No.
> 
> No.
> 
> No.  You can create a maximum timeout for a resource wait, but this is a b
ad
> thing as it tends to break transactional consistency.  Transaction timeout
> is typically controlled via the client application. 
>
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>

The SQL will roll back transactions if the connection is broken.

Regards,
John

"sameer" <sameer@discussions.microsoft.com> wrote in message
news:902AC15E-AF5F-44A2-BB5A- E0BED9D71AAA@microso
ft.com...
> also is there any time out for abandoned transactions ?
>
> "Geoff N. Hiten" wrote:
> 

how about abandoned transactions? how long do they stay active in the databa
se.

"John J. Hughes II" wrote:

> The SQL will roll back transactions if the connection is broken.
>
> Regards,
> John
>
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:902AC15E-AF5F-44A2-BB5A- E0BED9D71AAA@microso
ft.com... 
>
>
>

Sameer,

Ocassionaly a transaction will stay open and will prevent the log from
properly being truncated and may block other users to the locked data.  The
DBCC OPENTRAN statement can be used to find these rouge transactions and the
KILL statement can be used to end them.

HTH

Jerry
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:9BB78025-E660-4F7C-BE42- E5FA6FEBA944@microso
ft.com...
> how about abandoned transactions? how long do they stay active in the
> database.
>
> "John J. Hughes II" wrote:
> 

Jerry, but then won't there be a time out for transactions like these?
thanks

"Jerry Spivey" wrote:

> Sameer,
>
> Ocassionaly a transaction will stay open and will prevent the log from
> properly being truncated and may block other users to the locked data.  Th
e
> DBCC OPENTRAN statement can be used to find these rouge transactions and t
he
> KILL statement can be used to end them.
>
> HTH
>
> Jerry
> "sameer" <sameer@discussions.microsoft.com> wrote in message
> news:9BB78025-E660-4F7C-BE42- E5FA6FEBA944@microso
ft.com... 
>
>
>

No.  KILL will need to be used.
"sameer" <sameer@discussions.microsoft.com> wrote in message
news:9B39F23F-7850-4745-B939- D44F6DCCEA30@microso
ft.com...
> Jerry, but then won't there be a time out for transactions like these?
> thanks
>
> "Jerry Spivey" wrote:
>