how much memory does a transaction take?

hello,
i found out that it is useful while testing queries in my company to
start with begin tran, to do a restore if i might forget the where
clause
in an update on day after a long night.
but what does that cost?
the changes i make are only in my scope, to the server seems
to create temp tables of all tables i use.
if i do queryes for example 30 minutes without commit,
how does that affect the server? is that a very bad practice
or is that a normal way of working on a live server?
we do not really have testing systems, so that ist my
problem here..
nice to have some opinions stated here,
thank you and happy new year,
mike> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
It's very bad if your users have to wait 30 min till transaction will be
commited.It should be avoided
http://www.sql-server-performance.com/blocking.asp
<peppi911@.hotmail.com> wrote in message
news:1136460584.490204.3350@.g44g2000cwa.googlegroups.com...
> hello,
>
> i found out that it is useful while testing queries in my company to
> start with begin tran, to do a restore if i might forget the where
> clause
> in an update on day after a long night.
>
> but what does that cost?
> the changes i make are only in my scope, to the server seems
> to create temp tables of all tables i use.
> if i do queryes for example 30 minutes without commit,
> how does that affect the server? is that a very bad practice
> or is that a normal way of working on a live server?
> we do not really have testing systems, so that ist my
> problem here..
> nice to have some opinions stated here,
>
> thank you and happy new year,
> mike
>|||Hi,
but if i start my work with
BEGIN TRAN
...do something 30 min.
ROLLBACK TRAN
i think all the changes i do are in my scope and the records are not
locked,
so i surmised that the server creates a copy for me or whatever, am i
wrong?
you mean
begin tran
select * from customers
and i go to lunch does mean nobody can use this table until i do
rollback?
Well i guess i need to do further investigations here,-)
Thanks, mike|||First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL and also about different
lock types. Shared locks are by default not held until end of transaction. Most other lock types
are, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peppi911@.hotmail.com> wrote in message
news:1136462605.759961.248740@.g43g2000cwa.googlegroups.com...
> Hi,
> but if i start my work with
> BEGIN TRAN
> ...do something 30 min.
> ROLLBACK TRAN
> i think all the changes i do are in my scope and the records are not
> locked,
> so i surmised that the server creates a copy for me or whatever, am i
> wrong?
> you mean
> begin tran
> select * from customers
> and i go to lunch does mean nobody can use this table until i do
> rollback?
> Well i guess i need to do further investigations here,-)
> Thanks, mike
>|||Read the books is the best advice.
Anyway, you can hold the shared lock got by a select until the end of a
transaction if you specify the hint (HOLDLOCK).
--
Bazili
"Tibor Karaszi" wrote:
> First you should read in Books Online about SET TRANSACTION ISOLATION LEVEL and also about different
> lock types. Shared locks are by default not held until end of transaction. Most other lock types
> are, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <peppi911@.hotmail.com> wrote in message
> news:1136462605.759961.248740@.g43g2000cwa.googlegroups.com...
> > Hi,
> > but if i start my work with
> >
> > BEGIN TRAN
> > ...do something 30 min.
> > ROLLBACK TRAN
> >
> > i think all the changes i do are in my scope and the records are not
> > locked,
> > so i surmised that the server creates a copy for me or whatever, am i
> > wrong?
> > you mean
> >
> > begin tran
> > select * from customers
> > and i go to lunch does mean nobody can use this table until i do
> > rollback?
> >
> > Well i guess i need to do further investigations here,-)
> > Thanks, mike
> >
>