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
Showing posts with label log. Show all posts
Showing posts with label log. Show all posts
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 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
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
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 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
>
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
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 too much data for SQL?
I need to log data collected from instruments in a laboratory at a
very high rate. I need to log timestamps and values measured from
various devices at a rate as high as once a second.
This means my table will grow to tens or hundreds of gigabytes within
a year. I want to know if SQL server will be able to handle this much
data. In addition, is it better to separate this data into different
tables? Does a table become hard to search (search by the timestamp
field I am logging) once it gets to a certain size?
very high rate. I need to log timestamps and values measured from
various devices at a rate as high as once a second.
This means my table will grow to tens or hundreds of gigabytes within
a year. I want to know if SQL server will be able to handle this much
data. In addition, is it better to separate this data into different
tables? Does a table become hard to search (search by the timestamp
field I am logging) once it gets to a certain size?
Does anyone have experience or advice aboutt his problem? Is there
possibly another product out there which is better suited for logging
time-based data like this than SQL Server?
Thanks"NewbieNewsGrouper" <newbienewsgrouper@.hotmail.com> wrote in message
news:1e253672.0406161118.692433da@.posting.google.c om...
> I need to log data collected from instruments in a laboratory at a
> very high rate. I need to log timestamps and values measured from
> various devices at a rate as high as once a second.
> This means my table will grow to tens or hundreds of gigabytes within
> a year. I want to know if SQL server will be able to handle this much
> data. In addition, is it better to separate this data into different
> tables? Does a table become hard to search (search by the timestamp
> field I am logging) once it gets to a certain size?
> Does anyone have experience or advice aboutt his problem? Is there
> possibly another product out there which is better suited for logging
> time-based data like this than SQL Server?
> Thanks
I don't have any personal experience of applications like the one you're
describing, but there are certainly SQL Server databases which are handling
those volumes of data and transactions:
http://www.microsoft.com/sql/techin...scalability.asp
http://www.tpc.org/tpcc/results/tpc...erf_results.asp
It is possible to partition data using partitioned views - you can find more
details in Books Online.
Simon|||NewbieNewsGrouper (newbienewsgrouper@.hotmail.com) writes:
> I need to log data collected from instruments in a laboratory at a
> very high rate. I need to log timestamps and values measured from
> various devices at a rate as high as once a second.
> This means my table will grow to tens or hundreds of gigabytes within
> a year. I want to know if SQL server will be able to handle this much
> data.
It will. And once a second is not going to put SQL Server to the test.
With one caveat: you need to have a reasonable table and index structure.
Now, how that table and index structure should look like, I cannot tell,
because I don't know your application. It may also depend on you will
use that data. Maybe once the data has been collected, you should
move it over to Analysis Services where you can build cubes, dimensions
and whatever.
So the answer is, yes SQL Server can do it, but you have to design
carefully.
> In addition, is it better to separate this data into different
> tables? Does a table become hard to search (search by the timestamp
> field I am logging) once it gets to a certain size?
Again, that depends on your indexing. But depending on your requirements,
partitioning may be a good idea. For instance, if many queries will be
against today's test, it can be helpful to have them in a separate
table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I once supported a similar application which recorded lab measurement data
to SQL Server. The application architecture was such that data was
initially inserted to staging tables and then imported into a separate
schema for reporting and analysis. It is common to segregate operational
and reporting data so that you can optimize your schema (including indexes)
for the different requirements.
As Simon and Erland said, the key to performance with large tables is
appropriate indexing. SQL Server can certainly handle VLDBs but be aware
that performance is ultimately constrained by your hardware speed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"NewbieNewsGrouper" <newbienewsgrouper@.hotmail.com> wrote in message
news:1e253672.0406161118.692433da@.posting.google.c om...
> I need to log data collected from instruments in a laboratory at a
> very high rate. I need to log timestamps and values measured from
> various devices at a rate as high as once a second.
> This means my table will grow to tens or hundreds of gigabytes within
> a year. I want to know if SQL server will be able to handle this much
> data. In addition, is it better to separate this data into different
> tables? Does a table become hard to search (search by the timestamp
> field I am logging) once it gets to a certain size?
> Does anyone have experience or advice aboutt his problem? Is there
> possibly another product out there which is better suited for logging
> time-based data like this than SQL Server?
> Thanks|||It is usualy the standard to have your historic data sent to a seprate
database. At my company, we have a huge database with millions of
transactions. Right now, the database is well over 350GB (note hoever
this is on a million dollar 64 processor machine processing millions
of records a day).
Each evening, a DTS job runs which copies specific data (based on
timestamps) out of the production database, and into a datawarehouse.
This server can then be accessed offline. The benifit to this
alterntive database is 1) its not getting the busy hits that the
prodution system is getting. 2) You can have the DTS transfer the data
in a way that is benificial to any reports you may want to run.
(submit the data in a certain order, or into tables other than as are
structured in your production enviorment).
As far as size goes, You can pretty much go as big as you want,
assuming you have the disk space. If you are going to be collecting
data for over a year, I assume this data will be difficult to
reproduce, so you will want to back it up. The datawearhouse is an
execlent tool for this.
Hope this helps.
Mark|||I am currently engaged in pulling operatong system metrics from BMC
Patrol agents on over a 1000 sperate servers into one SQL database. I
have found a dramatic improvment in DB performance when inserting data
into a staging table and then aggregating data into reporting tables.
I currently process over 100 million records daily using a fully
loaded HP DL760 attached to an IBM FasTt SAN.|||In article <422d5636.0406271659.25c1a96f@.posting.google.com>, atyoung75
@.yahoo.com says...
> I am currently engaged in pulling operatong system metrics from BMC
> Patrol agents on over a 1000 sperate servers into one SQL database. I
> have found a dramatic improvment in DB performance when inserting data
> into a staging table and then aggregating data into reporting tables.
> I currently process over 100 million records daily using a fully
> loaded HP DL760 attached to an IBM FasTt SAN.
It's only to much data if you can't get the results you want.
--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)
Friday, March 23, 2012
Is this indication of Deadlock Occurs?
Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec
0xA9CA60C0) Value:0x802d1c0c
Cost0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
Johan
Looks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec0xA9CA60C0) Value:0x802d1c0c
> Cost0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
Basically I need some confirmation, LOG entry below:
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec0xA9CA60C0)
Value:0x802d1c0c
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
sql
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec
0xA9CA60C0) Value:0x802d1c0cCost
0/270F)2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
Johan
Looks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec
0xA9CA60C0) Value:0x802d1c0c> Cost
0/270F)> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>
|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
Basically I need some confirmation, LOG entry below:
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec
0xA9CA60C0)Value:0x802d1c0c
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
sql
Is this indication of Deadlock Occurs?
Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec:(0xA9CA60C0) Value:0x802d1c0c
Cost:(0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec:(0xA9CA60C0) Value:0x802d1c0c
> Cost:(0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec:(0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec:(0xA9CA60C0) Value:0x802d1c0c
Cost:(0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec:(0xA9CA60C0) Value:0x802d1c0c
> Cost:(0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communication
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec:(0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
Is this indication of Deadlock Occurs?
Hi All,
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec0xA9CA60C0) Value:0x802d1c0c
Cost0/270F)
2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec0xA9CA60C0) Value:0x802d1c0c
> Cost0/270F)
> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communicati
on
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec0xA9CA60C0)
Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
I see the following in the SQL (error) log and am curious as to
why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
SQL Server 2000 SP4. This server uses the Intel with 4 processors.
2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
ECID:33
Ec
0xA9CA60C0) Value:0x802d1c0cCost
0/270F)2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
2005-11-13 12:52:06.71 spid4 ... (similar as above)
2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
2005-11-13 12:52:11.71 spid4 ... (similar as above)
2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
2005-11-13 12:52:16.71 spid4 ... (similar as above)
As you can see, it happens every 5 seconds, and somehow
stop by itself. I also realize that before the shows that,
I do enable DBCC TRACEON (3605,1204,-1).
The questions is:
1. does it normal situation?
2. does it means that locking occurs, but no deadlock occurs?
3. or does it means that locking occurs, and deadlock happens,
and Lock Manager does terminate one/more SPID?
4. what is 'ResType:ExchangeId'?
Really need your help.
Regards,
JohanLooks like deadlock did you look through the profile who the culprit
SQL is
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||If you run sp_who2 while this is going on you will see the spid that is
blocking your transaction.
You can then run dbcc inputbuffer (spid #) to get more insight.
burt_king@.yahoo.com
"Johan" wrote:
> Hi All,
> I see the following in the SQL (error) log and am curious as to
> why it shows up (repeatedly). Server OS is Windows 2003 Enterprise,
> SQL Server 2000 SP4. This server uses the Intel with 4 processors.
> 2005-11-13 12:52:01.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:01.71 spid4 ResType:ExchangeId Stype:'AND' SPID:93
> ECID:33
> Ec
0xA9CA60C0) Value:0x802d1c0c> Cost
0/270F)> 2005-11-13 12:52:06.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:06.71 spid4 ... (similar as above)
> 2005-11-13 12:52:11.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:11.71 spid4 ... (similar as above)
> 2005-11-13 12:52:16.71 spid4 Victim Resource Owner:
> 2005-11-13 12:52:16.71 spid4 ... (similar as above)
> As you can see, it happens every 5 seconds, and somehow
> stop by itself. I also realize that before the shows that,
> I do enable DBCC TRACEON (3605,1204,-1).
> The questions is:
> 1. does it normal situation?
> 2. does it means that locking occurs, but no deadlock occurs?
> 3. or does it means that locking occurs, and deadlock happens,
> and Lock Manager does terminate one/more SPID?
> 4. what is 'ResType:ExchangeId'?
> Really need your help.
> Regards,
> Johan
>
>|||Since it happened sporadically and quite fast so I don't have the chance to
run SQL Profiler.
BTW, if I have the chance to run SQL Profiler, what 'Event Classes' to
capture the trace?
Thanks
"shiv_koirala@.yahoo.com" wrote:
> Looks like deadlock did you look through the profile who the culprit
> SQL is
> --
> Regards ,
> C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
> http://www.geocities.com/dotnetinterviews/
> My Interview Blog
> http://spaces.msn.com/members/dotnetinterviews/
>|||Since it happened sporadically and quite fast so I don't have the chance to
run sp_who2.
I also do some searching, that if deadlock really occured, then this message
will show up in ERRORLOG
--
Your transaction (process ID #52) was deadlocked on {lock | communicati
on
buffer | thread} resources with another process and has been chosen as the
deadlock victim. Rerun your transaction.
--
Basically I need some confirmation, LOG entry below:
--
ResType:ExchangeId Stype:'AND' SPID:93 ECID:33 Ec
0xA9CA60C0)Value:0x802d1c0c
--
1. does it means that locking occurs, but no deadlock occurs?
2. if it happened quite frequently, will it degrade the overall DB
performance?
Thanks,
Johan
"burt_king" wrote:
> If you run sp_who2 while this is going on you will see the spid that is
> blocking your transaction.
> You can then run dbcc inputbuffer (spid #) to get more insight.
>
> --
> burt_king@.yahoo.com
>
Monday, March 19, 2012
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:
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:
>
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:
>
Monday, March 12, 2012
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
Friday, March 9, 2012
Is there any way to relocate the data and log folders?
Is there anyway to move the data and log folders to another drive without having to reinstall SQL 2005. I have limited space available on the drive where the program files are installed and need to move these folders to another drive with adequate space for growth. Thanks
If you have to just move the standard folder (where new databases are created) you can do it through the GUI (just have a look at the server properties of the server). If you don′t have a GUI because you are using Express you can tweak the registry:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/c04546243a680213
If you want to move the database files which are already exsiting, you can move the files with the ALTER DATABASE command.
Sorry, for the multiple options, but you didn′t specify your problem / concern in detail :-)
HTH, Jens Suessmeyer.
|||Jens,
Thanks for the quick response and sorry for not providing more detail. Basically I would like to relocate the MSSQL folder under the Program Files\MSSQL.1 folder to another drive that will accomodate the growth of these files. I have already moved the mdf and log files for the database files using the GUI. Thanks again.
|||So your problem is solved, right ?|||Yes, Thanks|||So, it would be nice if you could track it with "solved" in the group system.
Cheers, jens.
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 to know the last access time of a table?
I have some junk tables in my database, but I am not sure if they are really not in use.
Does sql server log the last time that a table gets accessed? when I say access I mean any actions toward the table, including select, update, insert, etc.
thanks in advance.
I am unsure about your answer, but you can log it manually to a table by generating stored procedures for selecting/updating/etc.
|||thanks for the suggestion. I thought about that too but I can't possibly rewrite all codes across the application for this. also it may be too resource intensive. shouldn't there be a way to check the last access in sql server? apparently in the file-based system you can do so.
Is there any way to know any user Connect / Disconnect from the SQL Server?
Hi All,
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
Thanks !
Please do not crosspost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||sorry!
Thanks !
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.g bl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for your reply!
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
??:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>
|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
Thanks !
Please do not crosspost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||sorry!
Thanks !
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.g bl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for your reply!
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
??:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>
|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Hi All,
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
Thanks !
Please do not crosspost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||sorry!
Thanks !
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.g bl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for your reply!
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
??:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>
|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
Thanks !
Please do not crosspost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||sorry!
Thanks !
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.g bl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for your reply!
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
??:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>
|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Hi All,
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
Thanks !
Please do not crosspost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||sorry!
Thanks !
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.g bl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for your reply!
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
??:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>
|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
Thanks !
Please do not crosspost.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
|||sorry!
Thanks !
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.g bl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>
|||Thanks for your reply!
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
??:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>
|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Hi All,
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
--
Thanks !Please do not crosspost.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>|||sorry!
--
Thanks !
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l¥ó·s»D:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> Hi All,
>> Is there any way to know any user Connect / Disconnect from the SQL
>> Server?
>> Such as: In any kind of "Method" OR "Event Log"
>>
>> --
>> Thanks !
>>
>|||This is a multi-part message in MIME format.
--070603060003080705000406
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From SQL Enterprise manager you can enable logging for all logins (both
failed & successful) in the Server Properties dialog box (Security tab,
Security group box, Audit level radio button group). This will cause
SQL Server to write messages to the SQL errorlog every time a user
connects to the SQL server (whether it's a successful or failed login
attempt). But that won't tell you anything about logouts (i.e.
disconnects).
Probably the best way to monitor this stuff is with SQL Profiler. You
can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
write its trace data to a file or a SQL table so that you can query
against that data at a later time.
That would be about as close to an "event log" containing both logout &
login information that you can get your hands on. There's no way,
without altering the SQL Server binaries, to hook into the login/logout
event of a SQL server.
BTW, you ought to try to restrict your post to one or, at most, two
newsgroups.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SOHO wrote:
>Hi All,
>Is there any way to know any user Connect / Disconnect from the SQL Server?
>Such as: In any kind of "Method" OR "Event Log"
>
>
--070603060003080705000406
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>From SQL Enterprise manager you can enable logging for all logins
(both failed & successful) in the Server Properties dialog box
(Security tab, Security group box, Audit level radio button group).
This will cause SQL Server to write messages to the SQL errorlog every
time a user connects to the SQL server (whether it's a successful or
failed login attempt). But that won't tell you anything about logouts
(i.e. disconnects).<br>
<br>
Probably the best way to monitor this stuff is with SQL Profiler. You
can enable the 'Security Audit|Audit Login', '</tt><tt>Security Audit|</tt><tt>Audit
Login Failed' & '</tt><tt>Security Audit|</tt><tt>Audit Logout'
events. You can get profiler to write its trace data to a file or a
SQL table so that you can query against that data at a later time.<br>
<br>
That would be about as close to an "event log" containing both logout
& login information that you can get your hands on. There's no
way, without altering the SQL Server binaries, to hook into the
login/logout event of a SQL server.<br>
<br>
BTW, you ought to try to restrict your post to one or, at most, two
newsgroups.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
SOHO wrote:
<blockquote cite="midu2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">Hi All,
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
</pre>
</blockquote>
</body>
</html>
--070603060003080705000406--|||Thanks for your reply!
--
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
'?:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>>Hi All,
>>Is there any way to know any user Connect / Disconnect from the SQL
>>Server?
>>Such as: In any kind of "Method" OR "Event Log"
>>
>>
>|||Use profiler to monitor user connection is too expensive.
"SOHO" wrote:
> Thanks for your reply!
> --
> Thanks !
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
> '?:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> > From SQL Enterprise manager you can enable logging for all logins (both
> > failed & successful) in the Server Properties dialog box (Security tab,
> > Security group box, Audit level radio button group). This will cause
> > SQL Server to write messages to the SQL errorlog every time a user
> > connects to the SQL server (whether it's a successful or failed login
> > attempt). But that won't tell you anything about logouts (i.e.
> > disconnects).
> >
> > Probably the best way to monitor this stuff is with SQL Profiler. You
> > can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> > Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> > write its trace data to a file or a SQL table so that you can query
> > against that data at a later time.
> >
> > That would be about as close to an "event log" containing both logout &
> > login information that you can get your hands on. There's no way,
> > without altering the SQL Server binaries, to hook into the login/logout
> > event of a SQL server.
> >
> > BTW, you ought to try to restrict your post to one or, at most, two
> > newsgroups.
> >
> > --
> > *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> > *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> > *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> > http://www.mallesons.com
> >
> >
> >
> > SOHO wrote:
> >
> >>Hi All,
> >>
> >>Is there any way to know any user Connect / Disconnect from the SQL
> >>Server?
> >>Such as: In any kind of "Method" OR "Event Log"
> >>
> >>
> >>
> >>
> >
>
>|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
--
Thanks !Please do not crosspost.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>|||You can switch on the logging for valid / invalid /all logons for the
registry. Its located under the properties of the Server in the EM, Tab
Security.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>|||sorry!
--
Thanks !
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> ¼¶¼g©ó¶l¥ó·s»D:OoNXjDmcFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Please do not crosspost.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "SOHO" <hkwin2000@.hotmail.com> schrieb im Newsbeitrag
> news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> Hi All,
>> Is there any way to know any user Connect / Disconnect from the SQL
>> Server?
>> Such as: In any kind of "Method" OR "Event Log"
>>
>> --
>> Thanks !
>>
>|||This is a multi-part message in MIME format.
--070603060003080705000406
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From SQL Enterprise manager you can enable logging for all logins (both
failed & successful) in the Server Properties dialog box (Security tab,
Security group box, Audit level radio button group). This will cause
SQL Server to write messages to the SQL errorlog every time a user
connects to the SQL server (whether it's a successful or failed login
attempt). But that won't tell you anything about logouts (i.e.
disconnects).
Probably the best way to monitor this stuff is with SQL Profiler. You
can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
write its trace data to a file or a SQL table so that you can query
against that data at a later time.
That would be about as close to an "event log" containing both logout &
login information that you can get your hands on. There's no way,
without altering the SQL Server binaries, to hook into the login/logout
event of a SQL server.
BTW, you ought to try to restrict your post to one or, at most, two
newsgroups.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SOHO wrote:
>Hi All,
>Is there any way to know any user Connect / Disconnect from the SQL Server?
>Such as: In any kind of "Method" OR "Event Log"
>
>
--070603060003080705000406
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>From SQL Enterprise manager you can enable logging for all logins
(both failed & successful) in the Server Properties dialog box
(Security tab, Security group box, Audit level radio button group).
This will cause SQL Server to write messages to the SQL errorlog every
time a user connects to the SQL server (whether it's a successful or
failed login attempt). But that won't tell you anything about logouts
(i.e. disconnects).<br>
<br>
Probably the best way to monitor this stuff is with SQL Profiler. You
can enable the 'Security Audit|Audit Login', '</tt><tt>Security Audit|</tt><tt>Audit
Login Failed' & '</tt><tt>Security Audit|</tt><tt>Audit Logout'
events. You can get profiler to write its trace data to a file or a
SQL table so that you can query against that data at a later time.<br>
<br>
That would be about as close to an "event log" containing both logout
& login information that you can get your hands on. There's no
way, without altering the SQL Server binaries, to hook into the
login/logout event of a SQL server.<br>
<br>
BTW, you ought to try to restrict your post to one or, at most, two
newsgroups.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
SOHO wrote:
<blockquote cite="midu2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">Hi All,
Is there any way to know any user Connect / Disconnect from the SQL Server?
Such as: In any kind of "Method" OR "Event Log"
</pre>
</blockquote>
</body>
</html>
--070603060003080705000406--|||Thanks for your reply!
--
Thanks !
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
'?:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> From SQL Enterprise manager you can enable logging for all logins (both
> failed & successful) in the Server Properties dialog box (Security tab,
> Security group box, Audit level radio button group). This will cause
> SQL Server to write messages to the SQL errorlog every time a user
> connects to the SQL server (whether it's a successful or failed login
> attempt). But that won't tell you anything about logouts (i.e.
> disconnects).
> Probably the best way to monitor this stuff is with SQL Profiler. You
> can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> write its trace data to a file or a SQL table so that you can query
> against that data at a later time.
> That would be about as close to an "event log" containing both logout &
> login information that you can get your hands on. There's no way,
> without altering the SQL Server binaries, to hook into the login/logout
> event of a SQL server.
> BTW, you ought to try to restrict your post to one or, at most, two
> newsgroups.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> SOHO wrote:
>>Hi All,
>>Is there any way to know any user Connect / Disconnect from the SQL
>>Server?
>>Such as: In any kind of "Method" OR "Event Log"
>>
>>
>|||Use profiler to monitor user connection is too expensive.
"SOHO" wrote:
> Thanks for your reply!
> --
> Thanks !
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com>
> '?:%23STaOPmcFHA.2736@.TK2MSFTNGP12.phx.gbl...
> > From SQL Enterprise manager you can enable logging for all logins (both
> > failed & successful) in the Server Properties dialog box (Security tab,
> > Security group box, Audit level radio button group). This will cause
> > SQL Server to write messages to the SQL errorlog every time a user
> > connects to the SQL server (whether it's a successful or failed login
> > attempt). But that won't tell you anything about logouts (i.e.
> > disconnects).
> >
> > Probably the best way to monitor this stuff is with SQL Profiler. You
> > can enable the 'Security Audit|Audit Login', 'Security Audit|Audit Login
> > Failed' & 'Security Audit|Audit Logout' events. You can get profiler to
> > write its trace data to a file or a SQL table so that you can query
> > against that data at a later time.
> >
> > That would be about as close to an "event log" containing both logout &
> > login information that you can get your hands on. There's no way,
> > without altering the SQL Server binaries, to hook into the login/logout
> > event of a SQL server.
> >
> > BTW, you ought to try to restrict your post to one or, at most, two
> > newsgroups.
> >
> > --
> > *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> > *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> > *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> > http://www.mallesons.com
> >
> >
> >
> > SOHO wrote:
> >
> >>Hi All,
> >>
> >>Is there any way to know any user Connect / Disconnect from the SQL
> >>Server?
> >>Such as: In any kind of "Method" OR "Event Log"
> >>
> >>
> >>
> >>
> >
>
>|||SQL Server Profiler does not work for you?
Or use sp_who stored procedure.
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:u2Grt$lcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> Is there any way to know any user Connect / Disconnect from the SQL
> Server?
> Such as: In any kind of "Method" OR "Event Log"
>
> --
> Thanks !
>
>
Subscribe to:
Posts (Atom)