Friday, March 30, 2012

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

No comments:

Post a Comment