Is Truncating Log commiting data the the actual DB?

Newbie question running SQL 2k
My last entry in the .mdb file was January and everything else has been
sitting in the Transaction log files. Everything works fine. I'm just
wondering if Truncating the logs mean I'm going to commit the data from the
log files to the .mdb file. So if I truncate and look in the .mdb file,
will I see all my entries up to the most current period?
Thanks!AFAIK you will not see the data in the datafile until it is commited. And SQ
L
Server will not truncate stuff that is not commited or saved (for example
with a log backup).
Truncating the log means, that you truncate the unused or unneccessary data
from the logfile that is not neccessary any more.
regards
Andreas
"Phillip Vong" wrote:

> Newbie question running SQL 2k
> My last entry in the .mdb file was January and everything else has been
> sitting in the Transaction log files. Everything works fine. I'm just
> wondering if Truncating the logs mean I'm going to commit the data from th
e
> log files to the .mdb file. So if I truncate and look in the .mdb file,
> will I see all my entries up to the most current period?
> Thanks!
>
>|||Phillip,
The data in the transaction log files is committed to the database files not
long after the transaction is written to the log. There are a number of
factors to consider, but if nobody has changed anything since January it all
should be in the database by now.
Truncating the log is throwing away the transaction records that could be
used to recover in case of a failure. Actually, you almost never should
truncate. If you do not want the logs, change to the SIMPLE recovery model.
And make sure that you backup the database regularly.
The behavior of transaction logs is discussed under the backup and restore
strategy portions of the Books Online, so you need to familiarize yourself
with those. It is not too complicated.
RLF
"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:eSU5ZElYHHA.3256@.TK2MSFTNGP04.phx.gbl...
> Newbie question running SQL 2k
> My last entry in the .mdb file was January and everything else has been
> sitting in the Transaction log files. Everything works fine. I'm just
> wondering if Truncating the logs mean I'm going to commit the data from
> the log files to the .mdb file. So if I truncate and look in the .mdb
> file, will I see all my entries up to the most current period?
> Thanks!
>|||Russell, thanks for the update.
Can you tell me why the data in my tranaction log files has not committed to
the db files? You said "Shortly", but the last entry was 1/18/2007 in the
db file. Things are added to this db everyday by my user. Why are they all
staying in the transaction log file and not being committed. Can I force it
to commit some how? There are 1027 records in the log files and I want to
commit them to the db file. Will changing db to SIMPLE force it to commit
the data? I'm not going to lose what I have in the Log files am I?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23Gnin2lYHHA.992@.TK2MSFTNGP02.phx.gbl...
> Phillip,
> The data in the transaction log files is committed to the database files
> not long after the transaction is written to the log. There are a number
> of factors to consider, but if nobody has changed anything since January
> it all should be in the database by now.
> Truncating the log is throwing away the transaction records that could be
> used to recover in case of a failure. Actually, you almost never should
> truncate. If you do not want the logs, change to the SIMPLE recovery
> model. And make sure that you backup the database regularly.
> The behavior of transaction logs is discussed under the backup and restore
> strategy portions of the Books Online, so you need to familiarize yourself
> with those. It is not too complicated.
> RLF
> "Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
> news:eSU5ZElYHHA.3256@.TK2MSFTNGP04.phx.gbl...
>|||"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:uWxxX%23mYHHA.3984@.TK2MSFTNGP02.phx.gbl...
> Russell, thanks for the update.
> Can you tell me why the data in my tranaction log files has not committed
> to the db files? You said "Shortly", but the last entry was 1/18/2007 in
> the db file. Things are added to this db everyday by my user. Why are
> they all staying in the transaction log file and not being committed. Can
> I force it to commit some how? There are 1027 records in the log files and
> I want to commit them to the db file. Will changing db to SIMPLE force it
> to commit the data? I'm not going to lose what I have in the Log files am
> I?
Umm, how are you determining that things are NOT being committed?
If you do a select against inserted data, if you're seeing it, (and not
using read uncommitted) the data is definitely committed.
If you're basing your assumptions on FILE dates, then two things:
1) The file date on the file is the last time the file was OPENED or the
SIZE of it was changed.
So it sounds like you're looking at the file size of the MDF and assuming
nothing is committed since the data hasn't changed. This isn't really true.
2) If the size of the transaction log is GROWING, this means you're not
doing transaction log backups most likely. Which means your recovery plans
probably wont' be good.

>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Is Truncating Log commiting data the the actual DB?

Newbie question running SQL 2k
My last entry in the .mdb file was January and everything else has been
sitting in the Transaction log files. Everything works fine. I'm just
wondering if Truncating the logs mean I'm going to commit the data from the
log files to the .mdb file. So if I truncate and look in the .mdb file,
will I see all my entries up to the most current period?
Thanks!
AFAIK you will not see the data in the datafile until it is commited. And SQL
Server will not truncate stuff that is not commited or saved (for example
with a log backup).
Truncating the log means, that you truncate the unused or unneccessary data
from the logfile that is not neccessary any more.
regards
Andreas
"Phillip Vong" wrote:

> Newbie question running SQL 2k
> My last entry in the .mdb file was January and everything else has been
> sitting in the Transaction log files. Everything works fine. I'm just
> wondering if Truncating the logs mean I'm going to commit the data from the
> log files to the .mdb file. So if I truncate and look in the .mdb file,
> will I see all my entries up to the most current period?
> Thanks!
>
>
|||Phillip,
The data in the transaction log files is committed to the database files not
long after the transaction is written to the log. There are a number of
factors to consider, but if nobody has changed anything since January it all
should be in the database by now.
Truncating the log is throwing away the transaction records that could be
used to recover in case of a failure. Actually, you almost never should
truncate. If you do not want the logs, change to the SIMPLE recovery model.
And make sure that you backup the database regularly.
The behavior of transaction logs is discussed under the backup and restore
strategy portions of the Books Online, so you need to familiarize yourself
with those. It is not too complicated.
RLF
"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:eSU5ZElYHHA.3256@.TK2MSFTNGP04.phx.gbl...
> Newbie question running SQL 2k
> My last entry in the .mdb file was January and everything else has been
> sitting in the Transaction log files. Everything works fine. I'm just
> wondering if Truncating the logs mean I'm going to commit the data from
> the log files to the .mdb file. So if I truncate and look in the .mdb
> file, will I see all my entries up to the most current period?
> Thanks!
>
|||Russell, thanks for the update.
Can you tell me why the data in my tranaction log files has not committed to
the db files? You said "Shortly", but the last entry was 1/18/2007 in the
db file. Things are added to this db everyday by my user. Why are they all
staying in the transaction log file and not being committed. Can I force it
to commit some how? There are 1027 records in the log files and I want to
commit them to the db file. Will changing db to SIMPLE force it to commit
the data? I'm not going to lose what I have in the Log files am I?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23Gnin2lYHHA.992@.TK2MSFTNGP02.phx.gbl...
> Phillip,
> The data in the transaction log files is committed to the database files
> not long after the transaction is written to the log. There are a number
> of factors to consider, but if nobody has changed anything since January
> it all should be in the database by now.
> Truncating the log is throwing away the transaction records that could be
> used to recover in case of a failure. Actually, you almost never should
> truncate. If you do not want the logs, change to the SIMPLE recovery
> model. And make sure that you backup the database regularly.
> The behavior of transaction logs is discussed under the backup and restore
> strategy portions of the Books Online, so you need to familiarize yourself
> with those. It is not too complicated.
> RLF
> "Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
> news:eSU5ZElYHHA.3256@.TK2MSFTNGP04.phx.gbl...
>
|||"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:uWxxX%23mYHHA.3984@.TK2MSFTNGP02.phx.gbl...
> Russell, thanks for the update.
> Can you tell me why the data in my tranaction log files has not committed
> to the db files? You said "Shortly", but the last entry was 1/18/2007 in
> the db file. Things are added to this db everyday by my user. Why are
> they all staying in the transaction log file and not being committed. Can
> I force it to commit some how? There are 1027 records in the log files and
> I want to commit them to the db file. Will changing db to SIMPLE force it
> to commit the data? I'm not going to lose what I have in the Log files am
> I?
Umm, how are you determining that things are NOT being committed?
If you do a select against inserted data, if you're seeing it, (and not
using read uncommitted) the data is definitely committed.
If you're basing your assumptions on FILE dates, then two things:
1) The file date on the file is the last time the file was OPENED or the
SIZE of it was changed.
So it sounds like you're looking at the file size of the MDF and assuming
nothing is committed since the data hasn't changed. This isn't really true.
2) If the size of the transaction log is GROWING, this means you're not
doing transaction log backups most likely. Which means your recovery plans
probably wont' be good.

>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Is Truncating Log commiting data the the actual DB?

Newbie question running SQL 2k
My last entry in the .mdb file was January and everything else has been
sitting in the Transaction log files. Everything works fine. I'm just
wondering if Truncating the logs mean I'm going to commit the data from the
log files to the .mdb file. So if I truncate and look in the .mdb file,
will I see all my entries up to the most current period?
Thanks!AFAIK you will not see the data in the datafile until it is commited. And SQL
Server will not truncate stuff that is not commited or saved (for example
with a log backup).
Truncating the log means, that you truncate the unused or unneccessary data
from the logfile that is not neccessary any more.
regards
Andreas
"Phillip Vong" wrote:
> Newbie question running SQL 2k
> My last entry in the .mdb file was January and everything else has been
> sitting in the Transaction log files. Everything works fine. I'm just
> wondering if Truncating the logs mean I'm going to commit the data from the
> log files to the .mdb file. So if I truncate and look in the .mdb file,
> will I see all my entries up to the most current period?
> Thanks!
>
>|||Phillip,
The data in the transaction log files is committed to the database files not
long after the transaction is written to the log. There are a number of
factors to consider, but if nobody has changed anything since January it all
should be in the database by now.
Truncating the log is throwing away the transaction records that could be
used to recover in case of a failure. Actually, you almost never should
truncate. If you do not want the logs, change to the SIMPLE recovery model.
And make sure that you backup the database regularly.
The behavior of transaction logs is discussed under the backup and restore
strategy portions of the Books Online, so you need to familiarize yourself
with those. It is not too complicated.
RLF
"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:eSU5ZElYHHA.3256@.TK2MSFTNGP04.phx.gbl...
> Newbie question running SQL 2k
> My last entry in the .mdb file was January and everything else has been
> sitting in the Transaction log files. Everything works fine. I'm just
> wondering if Truncating the logs mean I'm going to commit the data from
> the log files to the .mdb file. So if I truncate and look in the .mdb
> file, will I see all my entries up to the most current period?
> Thanks!
>|||Russell, thanks for the update.
Can you tell me why the data in my tranaction log files has not committed to
the db files? You said "Shortly", but the last entry was 1/18/2007 in the
db file. Things are added to this db everyday by my user. Why are they all
staying in the transaction log file and not being committed. Can I force it
to commit some how? There are 1027 records in the log files and I want to
commit them to the db file. Will changing db to SIMPLE force it to commit
the data? I'm not going to lose what I have in the Log files am I?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23Gnin2lYHHA.992@.TK2MSFTNGP02.phx.gbl...
> Phillip,
> The data in the transaction log files is committed to the database files
> not long after the transaction is written to the log. There are a number
> of factors to consider, but if nobody has changed anything since January
> it all should be in the database by now.
> Truncating the log is throwing away the transaction records that could be
> used to recover in case of a failure. Actually, you almost never should
> truncate. If you do not want the logs, change to the SIMPLE recovery
> model. And make sure that you backup the database regularly.
> The behavior of transaction logs is discussed under the backup and restore
> strategy portions of the Books Online, so you need to familiarize yourself
> with those. It is not too complicated.
> RLF
> "Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
> news:eSU5ZElYHHA.3256@.TK2MSFTNGP04.phx.gbl...
>> Newbie question running SQL 2k
>> My last entry in the .mdb file was January and everything else has been
>> sitting in the Transaction log files. Everything works fine. I'm just
>> wondering if Truncating the logs mean I'm going to commit the data from
>> the log files to the .mdb file. So if I truncate and look in the .mdb
>> file, will I see all my entries up to the most current period?
>> Thanks!
>>
>|||"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
news:uWxxX%23mYHHA.3984@.TK2MSFTNGP02.phx.gbl...
> Russell, thanks for the update.
> Can you tell me why the data in my tranaction log files has not committed
> to the db files? You said "Shortly", but the last entry was 1/18/2007 in
> the db file. Things are added to this db everyday by my user. Why are
> they all staying in the transaction log file and not being committed. Can
> I force it to commit some how? There are 1027 records in the log files and
> I want to commit them to the db file. Will changing db to SIMPLE force it
> to commit the data? I'm not going to lose what I have in the Log files am
> I?
Umm, how are you determining that things are NOT being committed?
If you do a select against inserted data, if you're seeing it, (and not
using read uncommitted) the data is definitely committed.
If you're basing your assumptions on FILE dates, then two things:
1) The file date on the file is the last time the file was OPENED or the
SIZE of it was changed.
So it sounds like you're looking at the file size of the MDF and assuming
nothing is committed since the data hasn't changed. This isn't really true.
2) If the size of the transaction log is GROWING, this means you're not
doing transaction log backups most likely. Which means your recovery plans
probably wont' be good.
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

is transaction safe within store procedure?

Hello,
Is it safe to do this in a store procedure? Please share your comments or
suggestions. Thanks!
create procedure PerformAtomicDataCheck
@.ObjId varchar(100),
as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
// need to perform an atomic data operation here, might run into error
etc...
// if there is fatal error, would it leave the transaction around?
COMMIT TRANSACTIONFirst off if you are only doing a single operation (One insert, update or
delete ) regardless of the number of rows affected it will be an atomic
operation without adding BEGIN TRAN or changing the Isolation level. If you
do issue a Begin Tran it is up to you to either commit it or roll it back.
The only exception is if you use SET XACT_ABORT. If you get an error inside
a transaction and it is severe enough then you may not be able to address it
in the sp itself and must clean it up in the section that called the sp.
Errors above 15 severity usually abort the batch but do not commit or
rollback open transactions.
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eCZxo$BLFHA.2468@.tk2msftngp13.phx.gbl...
> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>|||Within SP, if you start a transation using Begin Transaction, you must eithe
r
execute Rollback, or COmmit, or you will leave an open transaction on your
server, along with all the locks it hasa created...
"Zeng" wrote:

> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>
>|||Transaction control can be used if it is necessary like you are going to do
more thatn one operation in the same Procedure. So, either all of its data
modifications are performed, or none of them is performed. Refer (ACID) BOL.
You can check for error at the end of the procedure
IF @.@.Error > 0
ROLLBACK TRANSACTION
Else
Commit Transaction
Thanks
Baiju
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eCZxo$BLFHA.2468@.tk2msftngp13.phx.gbl...
> Hello,
> Is it safe to do this in a store procedure? Please share your comments or
> suggestions. Thanks!
> create procedure PerformAtomicDataCheck
> @.ObjId varchar(100),
> as
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANSACTION
> // need to perform an atomic data operation here, might run into error
> etc...
> // if there is fatal error, would it leave the transaction around?
> COMMIT TRANSACTION
>|||Baiju wrote:
> Transaction control can be used if it is necessary like you are going
> to do more thatn one operation in the same Procedure. So, either all
> of its data modifications are performed, or none of them is
> performed. Refer (ACID) BOL.
> You can check for error at the end of the procedure
> IF @.@.Error > 0
> ROLLBACK TRANSACTION
> Else
> Commit Transaction
> Thanks
> Baiju
>
To be clear, you need to check @.@.ERROR after every SQL statement since
it's value is reset after each successful call.
David Gugick
Imceda Software
www.imceda.com

Is transaction name has to be unique?

Hi

I remember that once I had a problem when using a cursor in a sp and when several instances of the sp were running I had a problem when the first sp in the sequence deallocated the cursor and all the other who run in parallel had errors...
Well, this is not the problem now, but my question is, if I have a sp that has begin tran t1, and several instances of the sp are running in parallel, and each of course has begin tran t1, should I expect the same collision effect like with the cursor? Is every tran has to be with unique name? Or maybe the server knows how to manage this and when one tran has started and another sp tried to start another with the same name it makes it wait until the first one committed or rolled back?

Thanks,
Inon.I would think that this would be a particularly bad idea, though I have no specific experience in this area (transaction numbers). As I always understood it, the idea behind an explicitly identified transaction was to be able to roll back that specific transaction, particularly in an asynchronous environment. You have to ask yourself, what is the value in re-using the same transaction identifier? If you are just going to re-use the same identifier, then why bother with an identifier at all?

Regards,

hmscott

Is transaction log file reuseable after data file is Restored?

We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.
No. When you restore the FULL Database backup, it will write over the
existing log file. Whatever transactions that were in the transaction log
when the backup was taken and committed will roll forward, all those that
had not committed will roll back. This is called recovery. You will end up
with a database in a state it was in when the backup was taken.
Sincerely,
Anthony Thomas

"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.
|||Thanks Anthony,
So we're saying that committed transactions in the log file of a database
set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
Explorer I guess!)
ie these are transactions that have occurred subsequent to the backup being
taken. The MDF file is lost and restored from backup and this perfectly
good record of the subsequent transactions is not able to be used?
Why keep them then?
cheers,
Paul.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> No. When you restore the FULL Database backup, it will write over the
> existing log file. Whatever transactions that were in the transaction log
> when the backup was taken and committed will roll forward, all those that
> had not committed will roll back. This is called recovery. You will end
up
> with a database in a state it was in when the backup was taken.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> We have a database that is set to Simple recovery mode and is backed up
> every night. The data files and sql server and OS files are on the same
> disk and the log file is on a different disk. (There are no log file
> backups.)
> During the day the disk controller on the disk with the data files has
> failed and has subsequently been replaced and is being restored from tape.
> My question is: can the log file on the other (healty) disk be reliably
used
> to "replay" it's committed transactions into the restored database?
> thanks,
> Paul Ritchie.
>
|||"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.

> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
[vbcol=seagreen]
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
that[vbcol=seagreen]
end[vbcol=seagreen]
> up
tape.
> used
>
|||It is not for the COMMITTED transactions that the log files are used for.
Regardless of RECOVERY mode, it is the in process, non-committed, open
transactions that the log files are used for, without them, then there would
be no ROLLBACK during the reocvery process. This is the part of the ACID
properties that keeps the database consistent: if I pull money from one
account, it BETTER show up in another, viz., completely committed.
Sincerely,
Anthony Thomas

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:PT7wd.1397$DQ3.1285@.twister.nyroc.rr.com...
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.

> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
[vbcol=seagreen]
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
that[vbcol=seagreen]
end[vbcol=seagreen]
> up
tape.
> used
>

Is transaction log file reuseable after data file is Restored?

We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.No. When you restore the FULL Database backup, it will write over the
existing log file. Whatever transactions that were in the transaction log
when the backup was taken and committed will roll forward, all those that
had not committed will roll back. This is called recovery. You will end up
with a database in a state it was in when the backup was taken.
Sincerely,
Anthony Thomas
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
We have a database that is set to Simple recovery mode and is backed up
every night. The data files and sql server and OS files are on the same
disk and the log file is on a different disk. (There are no log file
backups.)
During the day the disk controller on the disk with the data files has
failed and has subsequently been replaced and is being restored from tape.
My question is: can the log file on the other (healty) disk be reliably used
to "replay" it's committed transactions into the restored database?
thanks,
Paul Ritchie.|||Thanks Anthony,
So we're saying that committed transactions in the log file of a database
set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
Explorer I guess!)
ie these are transactions that have occurred subsequent to the backup being
taken. The MDF file is lost and restored from backup and this perfectly
good record of the subsequent transactions is not able to be used?
Why keep them then?
cheers,
Paul.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> No. When you restore the FULL Database backup, it will write over the
> existing log file. Whatever transactions that were in the transaction log
> when the backup was taken and committed will roll forward, all those that
> had not committed will roll back. This is called recovery. You will end
up
> with a database in a state it was in when the backup was taken.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> We have a database that is set to Simple recovery mode and is backed up
> every night. The data files and sql server and OS files are on the same
> disk and the log file is on a different disk. (There are no log file
> backups.)
> During the day the disk controller on the disk with the data files has
> failed and has subsequently been replaced and is being restored from tape.
> My question is: can the log file on the other (healty) disk be reliably
used
> to "replay" it's committed transactions into the restored database?
> thanks,
> Paul Ritchie.
>|||"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.
> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> > No. When you restore the FULL Database backup, it will write over the
> > existing log file. Whatever transactions that were in the transaction
log
> > when the backup was taken and committed will roll forward, all those
that
> > had not committed will roll back. This is called recovery. You will
end
> up
> > with a database in a state it was in when the backup was taken.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> > news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > We have a database that is set to Simple recovery mode and is backed up
> > every night. The data files and sql server and OS files are on the same
> > disk and the log file is on a different disk. (There are no log file
> > backups.)
> >
> > During the day the disk controller on the disk with the data files has
> > failed and has subsequently been replaced and is being restored from
tape.
> >
> > My question is: can the log file on the other (healty) disk be reliably
> used
> > to "replay" it's committed transactions into the restored database?
> >
> > thanks,
> > Paul Ritchie.
> >
> >
>|||It is not for the COMMITTED transactions that the log files are used for.
Regardless of RECOVERY mode, it is the in process, non-committed, open
transactions that the log files are used for, without them, then there would
be no ROLLBACK during the reocvery process. This is the part of the ACID
properties that keeps the database consistent: if I pull money from one
account, it BETTER show up in another, viz., completely committed.
Sincerely,
Anthony Thomas
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:PT7wd.1397$DQ3.1285@.twister.nyroc.rr.com...
"Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
news:uYhlBvu4EHA.2196@.TK2MSFTNGP14.phx.gbl...
> Thanks Anthony,
> So we're saying that committed transactions in the log file of a database
> set to 'Simple' recovery are of no use to anyone? (Except Lumigent Log
> Explorer I guess!)
>
Not even them. Committed transactions are deleted from the log file upon a
checkpoint in SIMPLE RECOVERY mode.
So, within minutes (or less) the log file no logner has that transaction
anymore.
> ie these are transactions that have occurred subsequent to the backup
being
> taken. The MDF file is lost and restored from backup and this perfectly
> good record of the subsequent transactions is not able to be used?
> Why keep them then?
In Simple mode they aren't kept.
In FULL they are and can be used.
> cheers,
> Paul.
> "AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
> news:%23FLU0ha4EHA.936@.TK2MSFTNGP12.phx.gbl...
> > No. When you restore the FULL Database backup, it will write over the
> > existing log file. Whatever transactions that were in the transaction
log
> > when the backup was taken and committed will roll forward, all those
that
> > had not committed will roll back. This is called recovery. You will
end
> up
> > with a database in a state it was in when the backup was taken.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> > --
> >
> > "Paul Ritchie" <pritREMOVEchie@.xtREMOVEra.co.nzREMOVE> wrote in message
> > news:uqpOBmY4EHA.3368@.TK2MSFTNGP10.phx.gbl...
> > We have a database that is set to Simple recovery mode and is backed up
> > every night. The data files and sql server and OS files are on the same
> > disk and the log file is on a different disk. (There are no log file
> > backups.)
> >
> > During the day the disk controller on the disk with the data files has
> > failed and has subsequently been replaced and is being restored from
tape.
> >
> > My question is: can the log file on the other (healty) disk be reliably
> used
> > to "replay" it's committed transactions into the restored database?
> >
> > thanks,
> > Paul Ritchie.
> >
> >
>sql

is this possible

hey all,
i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.
How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record
can i do this in one stored procedure?
thanks,
rodcharWhy not use an IDENTITY column?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||Again provide DDL...
Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))
Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId
BTW, this does have issues in a multi-user environment. If two people were t
o
execute this function at exactly the same time, they'll get the same answer
and
thus a collision. A better way would be to make a small table that stores th
e
last value used.
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same
answer and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
..which would have the same issue -- what would stop two readers from
getting the value simultaneously?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||The problem is that this is an existing table in production.
"Adam Machanic" wrote:

> Why not use an IDENTITY column?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> 2
>
>|||Just increase Isolation Leel to Repeatable Read (or Serializeable) to preven
t
this issue from arising...
"Thomas" wrote:

> Again provide DDL...
> Create Table (RecordId Int, SequenceId Int
> , Constraint Primary Key (RecordId, SequenceId))
> Insert Table(RecordId, SequenceId)
> Select RecordId, Max(SequenceId) + 1
> From Table
> Group By RecordId
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same answe
r and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
>
> Thomas
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
>
>|||If you can withstand the entire table being locked during the insert process
,
this would also be a viable choice.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to prev
ent
> this issue from arising...
> "Thomas" wrote:
>|||If you use a small table that stores the next value, you can lock the table
and
increment the "next" value. In essence, serializing the retrieval of the nex
t id
value. However, it does mean you may get gaps.
Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OI6wTkcQFHA.1884@.TK2MSFTNGP15.phx.gbl...
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> to
> answer and
> the
> ...which would have the same issue -- what would stop two readers from
> getting the value simultaneously?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to
prevent
> this issue from arising...
How would that prevent issues?
QA Window 1:
--
use tempdb
go
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
QA Window 2:
--
use tempdb
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
Serializable blocks only if writes have taken place.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
> "Thomas" wrote:
>
were to
answer and
stores the
is a 2|||Adam,
Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Wait here while you run Qry WIndow 2 --
Commit Tran
-- ******************************
--Query Window 2
--
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- ---
"Adam Machanic" wrote:

> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> prevent
> How would that prevent issues?
> QA Window 1:
> --
> use tempdb
> go
> create table x(id int)
> go
> insert x values (1)
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> QA Window 2:
> --
> use tempdb
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> Serializable blocks only if writes have taken place.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> were to
> answer and
> stores the
> is a 2
>
>

Is this a bug in SQL 2000/2005?

Running the script below the error number is not generated and the
transaction is rolled back ...
Should be like this, or it should nicely give the error number and let me
decide what to do?
This is very usefull when using EXECUTE() or sp_executesql().
Here is hwo you can reproduce the problem:
CREATE TABLE xTest (IDCol int, xText varchar(100))
INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
SELECT @.@.ERROR --should be 207
DROP TABLE xTestSome errors terminates the batch. See the error handling articles at www.sommarskog.se for details.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Florin Lazar" <FlorinLazar@.discussions.microsoft.com> wrote in message
news:A436FCDD-B959-42C6-965F-4D40B47F6434@.microsoft.com...
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest|||The batch does not compile, and thus it is never executed. So the line
"SELECT @.@.ERROR" will never be executed.
Gert-Jan
Florin Lazar wrote:
> Running the script below the error number is not generated and the
> transaction is rolled back ...
> Should be like this, or it should nicely give the error number and let me
> decide what to do?
> This is very usefull when using EXECUTE() or sp_executesql().
> Here is hwo you can reproduce the problem:
> CREATE TABLE xTest (IDCol int, xText varchar(100))
> INSERT INTO xTest(IDCol,xText1) VALUES (1,'aaa')
> SELECT @.@.ERROR --should be 207
> DROP TABLE xTest

Is there such thing as "incremental" backup for SQL server?

I'm not looking for transaction log backups as substitute becouse it can
still be larger then just changed data.
My issue with differential backups is that they keep growing since last full
backup and I want only changed data since last full or differential backup to
be backed up and shipped to offisite location. Currently the only option is
ever growing differential backup or transaction log shipping which can be
quite large during operations which are modifiying small subset of data.
Diff and translog backups are "incremental" backups, which contain
changes since the last full backup, and last differential backup,
respectively. For a complete discussion of SQL Server backups, see
http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
However, if you are looking to minimize the size of the backups
altogether, I believe you will have to look at different technolgies...
we are just now switching over to a Net App and snap manager which will
eliminate the need for regular backups, but honestly I leave that to
the other DBA here, not my specialty :-)
ChanKaiShi wrote:
> I'm not looking for transaction log backups as substitute becouse it can
> still be larger then just changed data.
> My issue with differential backups is that they keep growing since last full
> backup and I want only changed data since last full or differential backup to
> be backed up and shipped to offisite location. Currently the only option is
> ever growing differential backup or transaction log shipping which can be
> quite large during operations which are modifiying small subset of data.
|||Is is possible that you are not backing up the transaction logs enough.
For example, I have managed a 100 gig db that was highly transactional
and I had to backup the logs on a 15-30 minute basis during the day in
order to keep the log size small. If I waited 12 hours and did 1
transaction log backup, it was sometimes 15 gigs. I used differential
backups for a while,but they were almost as big as the fulls, so I
switched to purely fulls and transaction logs.
kata...@.gmail.com wrote:[vbcol=seagreen]
> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
|||I need incremenatal backup which will hold only changes since last
incremental backup not differential one which has all changes ever done since
last full backup.
"kataoki@.gmail.com" wrote:

> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
>
|||>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
As previously stated, transaction log backups are incremental backups.
Transaction log backups contain only changes made since the last transaction
log backup (or full backup) and are not cumulative. You can implement
transaction log backups with only full database backups; differential
database backups are optional.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...[vbcol=seagreen]
>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
> "kataoki@.gmail.com" wrote:
|||transactional log backups are not incremental becouse incremental will log
only changes since last full or incremental while transaction log will log
all the transactions made in database even if all of them were on single row.
Secondly I'm not sure I'm understanding properly but full database backups
are not supposed to reset transaction log sequence, right? Becouse I'm
planning to use log shipping for external database server and my
understanding that I can do full database log backups while log shipping
process is being run.
"Dan Guzman" wrote:

> As previously stated, transaction log backups are incremental backups.
> Transaction log backups contain only changes made since the last transaction
> log backup (or full backup) and are not cumulative. You can implement
> transaction log backups with only full database backups; differential
> database backups are optional.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...
>
|||> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
I guess it depends how often you backup your log ;-) If backup file size is
your concern, you might also look into third party tools that compress
backups during the backup process.

> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right?
Yes, full backups do not truncate the log. In the FULL or BULK_LOGGED
model, committed transactions are removed from the log only by BACKUP LOG.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:415F6F11-50E8-45BA-ACEC-7720AE88D2B4@.microsoft.com...[vbcol=seagreen]
> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right? Becouse I'm
> planning to use log shipping for external database server and my
> understanding that I can do full database log backups while log shipping
> process is being run.
> "Dan Guzman" wrote:

Is there such thing as "incremental" backup for SQL server?

I'm not looking for transaction log backups as substitute becouse it can
still be larger then just changed data.
My issue with differential backups is that they keep growing since last full
backup and I want only changed data since last full or differential backup t
o
be backed up and shipped to offisite location. Currently the only option is
ever growing differential backup or transaction log shipping which can be
quite large during operations which are modifiying small subset of data.Diff and translog backups are "incremental" backups, which contain
changes since the last full backup, and last differential backup,
respectively. For a complete discussion of SQL Server backups, see
http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
However, if you are looking to minimize the size of the backups
altogether, I believe you will have to look at different technolgies...
we are just now switching over to a Net App and snap manager which will
eliminate the need for regular backups, but honestly I leave that to
the other DBA here, not my specialty :-)
ChanKaiShi wrote:
> I'm not looking for transaction log backups as substitute becouse it can
> still be larger then just changed data.
> My issue with differential backups is that they keep growing since last fu
ll
> backup and I want only changed data since last full or differential backup
to
> be backed up and shipped to offisite location. Currently the only option i
s
> ever growing differential backup or transaction log shipping which can be
> quite large during operations which are modifiying small subset of data.|||Is is possible that you are not backing up the transaction logs enough.
For example, I have managed a 100 gig db that was highly transactional
and I had to backup the logs on a 15-30 minute basis during the day in
order to keep the log size small. If I waited 12 hours and did 1
transaction log backup, it was sometimes 15 gigs. I used differential
backups for a while,but they were almost as big as the fulls, so I
switched to purely fulls and transaction logs.
kata...@.gmail.com wrote:[vbcol=seagreen]
> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:|||I need incremenatal backup which will hold only changes since last
incremental backup not differential one which has all changes ever done sinc
e
last full backup.
"kataoki@.gmail.com" wrote:

> Diff and translog backups are "incremental" backups, which contain
> changes since the last full backup, and last differential backup,
> respectively. For a complete discussion of SQL Server backups, see
> http://www.microsoft.com/technet/pr...s/c11ppcsq.mspx
> However, if you are looking to minimize the size of the backups
> altogether, I believe you will have to look at different technolgies...
> we are just now switching over to a Net App and snap manager which will
> eliminate the need for regular backups, but honestly I leave that to
> the other DBA here, not my specialty :-)
>
> ChanKaiShi wrote:
>|||>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
As previously stated, transaction log backups are incremental backups.
Transaction log backups contain only changes made since the last transaction
log backup (or full backup) and are not cumulative. You can implement
transaction log backups with only full database backups; differential
database backups are optional.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...[vbcol=seagreen]
>I need incremenatal backup which will hold only changes since last
> incremental backup not differential one which has all changes ever done
> since
> last full backup.
> "kataoki@.gmail.com" wrote:
>|||transactional log backups are not incremental becouse incremental will log
only changes since last full or incremental while transaction log will log
all the transactions made in database even if all of them were on single row
.
Secondly I'm not sure I'm understanding properly but full database backups
are not supposed to reset transaction log sequence, right? Becouse I'm
planning to use log shipping for external database server and my
understanding that I can do full database log backups while log shipping
process is being run.
"Dan Guzman" wrote:

> As previously stated, transaction log backups are incremental backups.
> Transaction log backups contain only changes made since the last transacti
on
> log backup (or full backup) and are not cumulative. You can implement
> transaction log backups with only full database backups; differential
> database backups are optional.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
> news:8536816F-B656-4273-B6A2-DD57277411BD@.microsoft.com...
>|||> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
I guess it depends how often you backup your log ;-) If backup file size is
your concern, you might also look into third party tools that compress
backups during the backup process.

> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right?
Yes, full backups do not truncate the log. In the FULL or BULK_LOGGED
model, committed transactions are removed from the log only by BACKUP LOG.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChanKaiShi" <ChanKaiShi@.discussions.microsoft.com> wrote in message
news:415F6F11-50E8-45BA-ACEC-7720AE88D2B4@.microsoft.com...[vbcol=seagreen]
> transactional log backups are not incremental becouse incremental will log
> only changes since last full or incremental while transaction log will log
> all the transactions made in database even if all of them were on single
> row.
> Secondly I'm not sure I'm understanding properly but full database backups
> are not supposed to reset transaction log sequence, right? Becouse I'm
> planning to use log shipping for external database server and my
> understanding that I can do full database log backups while log shipping
> process is being run.
> "Dan Guzman" wrote:
>

is there documenation on format of transaction log entries

I searched the forums and SQL 2005 book online to see if Microsoft provides documentation on the format of transaction log entries. I could not find any...

Will Microsoft provides this documentation? It will also be nice if Microsoft will provide utilities to help us read content of transaction logs (online or backup file)? This will be quite helpful for DBAs.

Oracle provides a FREE utility called log miner... This tool is quite helpful. I know there are vendors that sell utilities to process transaction logs ( Log Explorer). I would like more flexibility.

If Microsoft does not provide documentation or utilities in SQL 2005, what would be the reasons? These things can help DBAs, thus will help Microsoft sell SQL 2005.

Thanks.

Microsoft does not provide the format of its files to everyone. Microsoft does work with third party vendors to provide additional functionality (as you mentioned).

Please work with the third party vendors to get more features and the flexibility that you want.

Thank you,
Mark Wistrom

Is there any way we can handle cross database transaction on mirrored database?

Is there any way we can handle cross database transaction on mirrored
database'
THanks
DNBNot really. Database fail over individually. So if you have a cross database
transaction in-flight
and now one of the database fails over, then the in-flight transaction will
fail. Whether subsequent
transactions fails depends on whether the app has logic to do auto-redirect
for the failed database.
Since only one of the database failed over, you will end up in a distributed
transaction in this
case, unless you also "force-fail" the other databases when one db fails. T
here's no support for
failing several databases automatically (if one fails) so you would have to
roll your own for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DNB" <ii@.ii.com> wrote in message news:e2tvV4FLIHA.1168@.TK2MSFTNGP02.phx.gbl...een">
> Is there any way we can handle cross database transaction on mirrored data
base'
> THanks
> DNB
>

Is there any way we can handle cross database transaction on mirrored database?

Is there any way we can handle cross database transaction on mirrored
database'
THanks
DNBNot really. Database fail over individually. So if you have a cross database transaction in-flight
and now one of the database fails over, then the in-flight transaction will fail. Whether subsequent
transactions fails depends on whether the app has logic to do auto-redirect for the failed database.
Since only one of the database failed over, you will end up in a distributed transaction in this
case, unless you also ´"force-fail" the other databases when one db fails. There's no support for
failing several databases automatically (if one fails) so you would have to roll your own for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"DNB" <ii@.ii.com> wrote in message news:e2tvV4FLIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Is there any way we can handle cross database transaction on mirrored database'
> THanks
> DNB
>

Is there any way to read the transaction log?

Hi there,

I need to recover certain transactions that I made on the server.

Those transactions were in scripts and I lost the files.

Is there a way to read the transaction log so that I can see what transactions were executed?

TIA

I would have a look at the product LogExplorer as it should do what you need.

|||The transaction log is a binary structure and is not designed to give this sort of information. As Glen points out, there are third-party companies who have licensed the file layouts from us and are able to reproduce the original statements from the logs. Lumigent's Log Explorer is one of these.

Is there any way to read the transaction log?

Hi there,

I need to recover certain transactions that I made on the server.

Those transactions were in scripts and I lost the files.

Is there a way to read the transaction log so that I can see what transactions were executed?

TIA

I would have a look at the product LogExplorer as it should do what you need.

|||The transaction log is a binary structure and is not designed to give this sort of information. As Glen points out, there are third-party companies who have licensed the file layouts from us and are able to reproduce the original statements from the logs. Lumigent's Log Explorer is one of these.

Is there any way i can Roll back in sql server without using transactions ?

Hi,

i wanna know how to use rollback in sql server without actually using transaction...

is there any way i can do that?

thanx in advance

nope,

rollback will work only when transaction has been issued. Or take a daily backup of database so that you can retrieve it with previous days status

rgds

ramu

|||

::i wanna know how to use rollback in sql server without actually using transaction...

What sense would it make? The ability to separately commit/rollback changes is the definition of a transaction. You with is like drinking water without getting in touch with h20.

If you need transactional control, use a transaction. And rolling back changes you did IS transactional control.

|||

Hey thanks for ur replies.

me gonna implement transactions!!

thanx again