How often to backup the Transaction log.

I do a full overwrite backup of the database at 3am, I also do an overwrite
backup of the transaction log at 3:30. Then during the 2 production shifts I
run differential backups every hour on the hour. I do a transaction log
backup every hour on the half-hour. Is this too often, what is a problem
manifests itself after the trans log is overwriten. The processes do not
take long and they are not a strain on the system. I am doing a standby
server to handle server crashes, seems to me that I should have as recent a
backup as possible.
thoughts?
thanks,
jc
jc
It definitely should not be a problem to backup your TLog every hour. I have many systems that back up every 5 minutes. As long as you are not stressing your system then backup as often as you see fit.
Jeff Duncan
MCDBA, MCSE+I

How often to backup the Transaction log.

I do a full overwrite backup of the database at 3am, I also do an overwrite
backup of the transaction log at 3:30. Then during the 2 production shifts I
run differential backups every hour on the hour. I do a transaction log
backup every hour on the half-hour. Is this too often, what is a problem
manifests itself after the trans log is overwriten. The processes do not
take long and they are not a strain on the system. I am doing a standby
server to handle server crashes, seems to me that I should have as recent a
backup as possible.
thoughts?
thanks,
jcjc
It definitely should not be a problem to backup your TLog every hour. I hav
e many systems that back up every 5 minutes. As long as you are not stressi
ng your system then backup as often as you see fit.
Jeff Duncan
MCDBA, MCSE+I

How often to backup the Transaction log.

I do a full overwrite backup of the database at 3am, I also do an overwrite
backup of the transaction log at 3:30. Then during the 2 production shifts I
run differential backups every hour on the hour. I do a transaction log
backup every hour on the half-hour. Is this too often, what is a problem
manifests itself after the trans log is overwriten. The processes do not
take long and they are not a strain on the system. I am doing a standby
server to handle server crashes, seems to me that I should have as recent a
backup as possible.
thoughts?
thanks,
jcj
It definitely should not be a problem to backup your TLog every hour. I have many systems that back up every 5 minutes. As long as you are not stressing your system then backup as often as you see fit
Jeff Dunca
MCDBA, MCSE+Isql

How much space used in each transaction log file

Is there a way to know how much space is free in each transaction log file of the same database?

Example:

A database with 3, 1GB files for transaction log: A, B and C created in this sequence.

From what I have read, since SQL Server 2005 writes to a single transaction log only, I guess if the transaction log is using 2.5GB than A and B are full and B is only half full. Is this correct?

Thanks.Transaction log usage is the same for sql2k/5. It writes sequentially. To determine the used space for tran log, do this:

dbcc sqlperf(logspace)

How much space Transaction Log is used ?

We have a Transaction Log of size 10GB and we would like
to know how much size of it is actually used. This is
because we would like to shrink the TL size.
ThanksThere's more than one way to find this out. One technique is to use dbcc
sqlperf(logspace)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a38801c3eb8c$53b33c70$a001280a@.phx.gbl...

quote:

---

> We have a Transaction Log of size 10GB and we would like
> to know how much size of it is actually used. This is
> because we would like to shrink the TL size.
> Thanks

---

How much space Transaction Log is used ?

We have a Transaction Log of size 10GB and we would like
to know how much size of it is actually used. This is
because we would like to shrink the TL size.
ThanksThere's more than one way to find this out. One technique is to use dbcc
sqlperf(logspace)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a38801c3eb8c$53b33c70$a001280a@.phx.gbl...
> We have a Transaction Log of size 10GB and we would like
> to know how much size of it is actually used. This is
> because we would like to shrink the TL size.
> Thanks

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
> >
>

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.googlegrou ps.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.googlegr oups.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.googlegr oups.com...
>

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. M
ost 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 LEVE
L 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...
>sql

How long has transaction been open?

Does anyone know how to determine how long an open
transaction hase been open? None of the columns in
sysprocesses seem to provide that.
ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_008E_01C3FC84.72B5DE30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Check out DBCC OPENTRAN in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2a1601c3fcad$eaf28550$a501280a@.phx.gbl...
Does anyone know how to determine how long an open
transaction hase been open? None of the columns in
sysprocesses seem to provide that.
Thanks
--=_NextPart_000_008E_01C3FC84.72B5DE30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out DBCC OPENTRAN in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike" wrote in message news:2a1601c3fcad$ea=f28550$a501280a@.phx.gbl...Does anyone know how to determine how long an open transaction hase been open? None of the columns in sysprocesses seem to provide that.Thanks

---

--=_NextPart_000_008E_01C3FC84.72B5DE30--|||> Does anyone know how to determine how long an open
> transaction hase been open? None of the columns in
> sysprocesses seem to provide that.
See DBCC OPENTRAN, it displays information about the oldest active
transaction and the oldest distributed and nondistributed replicated
transactions, if any, within the specified database.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_5fhq.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you both.

How long has transaction been open?

Does anyone know how to determine how long an open
transaction hase been open? None of the columns in
sysprocesses seem to provide that.
ThanksCheck out DBCC OPENTRAN in the BOL.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2a1601c3fcad$eaf28550$a501280a@.phx.gbl...
Does anyone know how to determine how long an open
transaction hase been open? None of the columns in
sysprocesses seem to provide that.
Thanks|||> Does anyone know how to determine how long an open
> transaction hase been open? None of the columns in
> sysprocesses seem to provide that.
See DBCC OPENTRAN, it displays information about the oldest active
transaction and the oldest distributed and nondistributed replicated
transactions, if any, within the specified database.
http://msdn.microsoft.com/library/d...
fhq.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

How long does it take for sp_repldone to execute?

Does the time it takes to execute sp_repldone depend on the size of the transaction log it's working on?

Basically, I am executing sp_repldone to mark all of the transactions as replicated, so I am wondering it sp_repldone goes through each unreplicated transaction and marks it, or does it just go to the last unreplicated transaction and marks only the last one, and all of the previous ones are automatically consideres replicated?

Reason to ask: our transaction log is around 27GB right now, although normally it's approximately 1GB. The sp_repldone command has been executing for the past 30 hours, and since it doesn't give any information as to the status of the execution, I'm wondering if it's doing anything at all... :confused: I also checked the process, and it in not blocked by anything.

Any insight would be greatly appreciated.Um, should you be doing this? According to what I read you should only execute this in an emergency.|||Um, should you be doing this? According to what I read you should only execute this in an emergency.That is a very good point. However, so far I haven't found any other solution to a failing Log Reader that says "The process could not execute 'sp_repldone/sp_replcounters' on 'myserver'." and "The process could not set the last distributed transaction."

The only other option we have is to drop and recreate our replication schema that has around 150 publications with one of them having 130 subscribers. Even if you script it, it a great pain in the a** to set it up. We've aready done that, and I'm not sure I want to go through that again...

:)|||I saw one suggestion that you stop and start sql server agent and if that does not work to stop it, execute sp_replflush and then restart it. Don't know if you've tried that or whether it worked for the other person.

What version of SQL Server are you running?|||I saw one suggestion that you stop and start sql server agent and if that does not work to stop it, execute sp_replflush and then restart it. Don't know if you've tried that or whether it worked for the other person.

What version of SQL Server are you running?
ejustuss, thank you for taking your time to help me. :) It's a 2000 with SP3.

I know, we've tried sp_replflush. We also tried rebooting the server. Actually, we came to a conclusion that sp_repldone is not working. It actually gets stuck at the same point as Log Reader. So the only option we have is to drop our publications :(

Our current replication setup includes ~150 transactional publications with queued updating with each one having 1 subscriber, and one simple transactional publication with a whole bunch of subscribers. My boss thinks that those transactional with queued updating publications might be stalling the Log Reader. We will try switching to merge replication instead and that way the Log Reader will only have to deal with one simple transactional publication that has a bunch of subscribers.|||Sounds complicated. I only have one transactional publication with just 4 articles in it (duplicated on 2 machines) and one (central) subscriber. And one merge publication with about 8 articles replicating between the same 3 machines.

How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?
"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:

> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>
|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com
|||in stored procedure

How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:

> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order an
d
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure

How lock few tables at ones?

In order to prevent dead lock we need to avoid situation that one
Thread run transaction that lock tables A and after lock table B,
While another thread can be lock these tables in opposite order.
In my situation, I work on exists DB, and I must lock customer stuff from
all
Table at one, to ensure that other thread not get lock in another order and
I
Fail to deadlock.
How I can lock few tables for special customer in one statement?"In one statement" - Are you talking about in code (C# or VB.net) or SQL
scripts?
"Mttc" wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff from
> all
> Table at one, to ensure that other thread not get lock in another order and
> I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
>
>|||Mttc wrote:
> In order to prevent dead lock we need to avoid situation that one
> Thread run transaction that lock tables A and after lock table B,
> While another thread can be lock these tables in opposite order.
> In my situation, I work on exists DB, and I must lock customer stuff
> from all
> Table at one, to ensure that other thread not get lock in another
> order and I
> Fail to deadlock.
> How I can lock few tables for special customer in one statement?
Not to state the obvious, but one way to prevetn deadlocks is to access
your tables in the same order whereever possible. Can you let us know
why in the case you describe, one of the transactions can't have its
table order switched?
You don't really want to lock the tables, although you can. It will
really affect concurrency in the database.
To lock a table:
begin tran
update employee with (tablockx)
set emp_id = 'PMA42628M'
where emp_id = 'PMA42628M'
From the other transaction, you could first "test" if the table is
available by issuing something like the following which fails if a lock
can't be quickly attained.
Begin Tran
set lock_timeout 0
select TOP 1 emp_id from employee
if @.@.error != 0
print 'Error'
Else
continue
David Gugick
Imceda Software
www.imceda.com|||in stored procedure

How I can restore to the certain LSN (not Point-In-Time)?

Dear Sir.
How I can restore to the certain LSN (not Point-In-Time)?
Also do you know program for viewing of Transaction Log and BackUp?
Thank you in advance.Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
BOL
9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
Linchi
"RedFox" wrote:
> Dear Sir.
> How I can restore to the certain LSN (not Point-In-Time)?
> Also do you know program for viewing of Transaction Log and BackUp?
> Thank you in advance.|||Dear Linchi Shea,
Thank you for your answer, but I have asked about CERTAIN LSN (= Log
Sequence Number) - NOT MARKED Transaction.
P.S. link is not correct.
"Linchi Shea" wrote:
> Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
> BOL
> 9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
> Linchi
> "RedFox" wrote:
> > Dear Sir.
> >
> > How I can restore to the certain LSN (not Point-In-Time)?
> > Also do you know program for viewing of Transaction Log and BackUp?
> >
> > Thank you in advance.|||The link I posted earlier is for BOL. You can also use this link to the Books
Online at the Microsoft site.
http://msdn2.microsoft.com/en-us/library/ms191459.aspx
> but I have asked about CERTAIN LSN (= Log
> Sequence Number) - NOT MARKED Transaction.
It has an option to allow you to restore to a log sequence number.
Linchi
"RedFox" wrote:
> Dear Linchi Shea,
> Thank you for your answer, but I have asked about CERTAIN LSN (= Log
> Sequence Number) - NOT MARKED Transaction.
> P.S. link is not correct.
> "Linchi Shea" wrote:
> > Have you taken a look at the stopatmark or stopbeforemark option in SQL2005
> > BOL
> > 9ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f7b3de5b-198d-448d-8c71-1cdd9239676c.htm)?
> >
> > Linchi
> >
> > "RedFox" wrote:
> >
> > > Dear Sir.
> > >
> > > How I can restore to the certain LSN (not Point-In-Time)?
> > > Also do you know program for viewing of Transaction Log and BackUp?
> > >
> > > Thank you in advance.

How full backup work ?

that what happens if during a full backup a transaction is started or a
transaction is running?
Data modified will be saved ?
It exists a microsoft document that explain how full backup work ?
ThanksThe full backup will contain ALL of the transactions which ended PRIOR to
the completion of the backup. This is because right before the end of the
backup, it goes back to the log and captures the changes made during the
backup.
--
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
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> that what happens if during a full backup a transaction is started or a
> transaction is running?
> Data modified will be saved ?
> It exists a microsoft document that explain how full backup work ?
> Thanks|||Where I can find documentation that explain in deep "how work full backup" ?
Thanks
"Wayne Snyder" wrote:
> The full backup will contain ALL of the transactions which ended PRIOR to
> the completion of the backup. This is because right before the end of the
> backup, it goes back to the log and captures the changes made during the
> backup.
> --
> 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
> "andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
> news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> > that what happens if during a full backup a transaction is started or a
> > transaction is running?
> > Data modified will be saved ?
> > It exists a microsoft document that explain how full backup work ?
> >
> > Thanks
>
>

How full backup work ?

that what happens if during a full backup a transaction is started or a
transaction is running?
Data modified will be saved ?
It exists a microsoft document that explain how full backup work ?
Thanks
The full backup will contain ALL of the transactions which ended PRIOR to
the completion of the backup. This is because right before the end of the
backup, it goes back to the log and captures the changes made during the
backup.
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
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> that what happens if during a full backup a transaction is started or a
> transaction is running?
> Data modified will be saved ?
> It exists a microsoft document that explain how full backup work ?
> Thanks
|||Where I can find documentation that explain in deep "how work full backup" ?
Thanks
"Wayne Snyder" wrote:

> The full backup will contain ALL of the transactions which ended PRIOR to
> the completion of the backup. This is because right before the end of the
> backup, it goes back to the log and captures the changes made during the
> backup.
> --
> 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
> "andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
> news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
>
>

How full backup work ?

that what happens if during a full backup a transaction is started or a
transaction is running?
Data modified will be saved ?
It exists a microsoft document that explain how full backup work ?
ThanksThe full backup will contain ALL of the transactions which ended PRIOR to
the completion of the backup. This is because right before the end of the
backup, it goes back to the log and captures the changes made during the
backup.
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
"andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
> that what happens if during a full backup a transaction is started or a
> transaction is running?
> Data modified will be saved ?
> It exists a microsoft document that explain how full backup work ?
> Thanks|||Where I can find documentation that explain in deep "how work full backup" ?
Thanks
"Wayne Snyder" wrote:

> The full backup will contain ALL of the transactions which ended PRIOR to
> the completion of the backup. This is because right before the end of the
> backup, it goes back to the log and captures the changes made during the
> backup.
> --
> 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
> "andrea favero" <andreafavero@.discussions.microsoft.com> wrote in message
> news:052B33CA-065C-4A46-9CBB-0B5CEA2DE933@.microsoft.com...
>
>