Is Truncating Log commiting data the the actual DB?

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

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

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

Is Truncating Log commiting data the the actual DB?

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

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

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

Is Truncating Log commiting data the the actual DB?

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

Is transaction log file reuseable after data file is Restored?

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

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

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

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

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

Is transaction log file reuseable after data file is Restored?

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

is this possible?

hi

I would like to display agregated data, as well as my image, but it seems that this requires me to put my image file field in my group by statement too, but sql does not seem to allow sorting of images. Is what I am trying to possible? Or do I have to do both things separate?

here is the code:

select tbltimesheets.weekno, sum(tblentries.hrsnorm) as hrsnorm, sum(tblentries.hrspot) as hrspot, sum(tblentries.hrsnpot) as hrsnpot, tblusers.username, images.imagefile from
tbltimesheets
inner join tblentries on tbltimesheets.tskey = tblEntries.TSKey
inner join tblusers on tbltimesheets.userkey = tblusers.userkey
inner join images on images.userkey = tblusers.userkey
group by tbltimesheets.weekno, tblusers.username, images.imagefileYou have to ddo the thing separate. YOu can use some smart SQL for this, though, working, and still do it in one query.

Or, instead summing, you could use another method (like one returning the first image in the group).

Is this possible without using Cursors?

Hello!
I have a stored proc that checks the file existance in one location and
copy them to another location. The stored proc reads the record one by
one and builds the DOS COPY command. In the end, it exccutes the DOS
command using xp_cmdshell. See below the code.
The stored proc is working great but I had to use the CURSOR for reading
the records. I was wondering if I can avoid using it? Is this possible?
how? Thanks in advance for your help!
CREATE TABLE [dbo].[FILE_PATH] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Source] [varchar] (150) NULL ,
[Destination] [char] (150) NULL ,
[Environment] [char] (25) NULL ,
[Filename] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file1')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file2')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
CREATE proc CopyFiles
as
set nocount on
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
declare @.source_dir varchar(200)
declare @.filename varchar(30)
declare @.environment varchar(200)
declare @.environementTmp varchar(200)
declare @.errortext varchar(200)
select
@.environementTmp =
case @.@.servername
when 'server A' then 'Development'
when 'server B' then 'UAT'
when 'server C' then 'Production'
end
create table #files(filename sysname NULL)
declare filecursor cursor for
select source, destination, environment, [filename]
from file_path
open filecursor
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
while (@.@.fetch_status = 0)
begin
if @.environementTmp = @.environment
begin
set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
+ ' /b'
insert #files exec master..xp_cmdshell @.source_dir
if (select filename from #files where filename is not null) = @.filename
begin
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
' +'"'+ rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
end
else
begin
set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
exist!!!'
exec master.dbo.xp_sendmail
@.recipients = 'abc123',
@.copy_recipients = 'abc123'
@.subject = @.errortext,
@.message = @.errortext
end
end
truncate table #files
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
end
close filecursor
deallocate filecursor
GO
*** Sent via Developersdex http://www.examnotes.net ***Test Test wrote:
> Hello!
> I have a stored proc that checks the file existance in one location
> and copy them to another location. The stored proc reads the record
> one by one and builds the DOS COPY command. In the end, it exccutes
> the DOS command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for
> reading the records. I was wondering if I can avoid using it? Is this
> possible? how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5') go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) =
> @.filename begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
Something tells me more time is spent executing xp_cmdshell than the
time used for the cursor, so you're probably fine. Cursors are good for
this type of operation IMO since it makes the code relatively clear. You
should always use local, read-only forward-only cursors, for this type
of read-only processing so update your declare statement accordingly.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||We had a similar problem - we solved this by using COM objects.
We created our own custom set of COM objects, but WSH.FileSystemObject does
the same thing.
Essentially:-
Call master.dbo.sp_OACreate to create the com object (returns @.lpObject)
Then create a UDF to do the file exists check
Call master.dbo.sp_OAMethod for file exists passing filename returning file
exists
Then destroy the object using master.dbo.sp_OADestory
Then you'd do
UPDATE
tableWithFileNamesIn
SET
FileExists = dbo.fnFileExists( @.lpObject , FullPathToFile ) AS
FileExists
Assuming: tableWithFileNamesIn( FullPathToFile VARCHAR(260) , FileExists
BIT )
http://msdn.microsoft.com/library/d.../>
sotutor.asp
If the process isn't going to run as system admin, create a role in master
called COMCreator, and add all the sp_OA... (except for OAStop) to that
role and add the process user account to COMCreator.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uB58v9A0FHA.268@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a stored proc that checks the file existance in one location and
> copy them to another location. The stored proc reads the record one by
> one and builds the DOS COPY command. In the end, it exccutes the DOS
> command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for reading
> the records. I was wondering if I can avoid using it? Is this possible?
> how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) = @.filename
> begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

is this possible in SSIS?

I got a OLE DB source pointing to 1 table

and 1 flat file destination.

currently this is how i export data from 1 table to 1 flat file.

To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.

anyone can help me?

Unfortunately not because the metadata i.e. the columns involved in the transform need to be the same. So you can't have 1 data flow in a loop that is reconfigured for different tables and destinations.

If the source is SQL you could use BCP to produce the flat files but this then really isn't SSIS. Although you could run the bcp from within SSIS.

|||

i was wondering if there can be a conditional loop in between the OLE DB source and the flat file.....

like if the table name is A then go to File destination A

if table name is B then go to File destination B etc....

hope someone understands what i m saying.

|||

You can direct rows to 1 of multiple destinations based on characteristics of the row. This is done usnig the Conditional Split transform. You should look into that and see whether it will do what you require.

I don't know why you used the word "loop". There is no notion of looping in a data-flow.

-Jamie

|||

no tats not what i want....

I trying to backup many tables into many text files using 1 SSIS package project. Is that possible?

trying to reduce the no. of SIS packages file i need to maintain.

|||

Why not have 1 package containing many data-flows?

-Jamie

|||

I got a OLE DB source pointing to 1 table
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.

To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.


anyone can help me by posting a screenshot of how this can be done in SSIS....the data flows diagram i m not very sure...cos i just started using SSIS in SQL Server 2005.

any guides to SSIS will also be appreciated. Thanks!

i tried using 1 ole db source + file A

and 2 ole db source + file B separated in the Diagram but when i execute it , it doesnt run :(

|||

Ah ok. You can't do this, as Simon explained earlier!

-Jamie

|||

ok Jamie, lets look at it the other manner

I got a table on database server -> export to text file -> import to my local database....

I m doing this task several times...

can this be run consecutively in a data flow diagram? i tried but its not working.....cos of concurrency issues i guess.

can some expert enlighten me?

|||

You can run them all in the same data-flow (in which case there will be as many source and destination adapters as there are tables you are moving data from) or concurrently in seperate data-flows.

-Jamie

|||Be aware that even if you have 20 sources and 20 destinations they may not all run at once. SSIS has a process that determines the threads to use and the amount of concurrency. If running on a 1 proc machine you will get very different results that running on a 4 way machine.|||hmm so simon...what do u recommend?|||

Brohans,

In this scenario its really hard to make a recommendation. These are your options where you have N tables that you have to move data from:

1) Have 1 data-flow that contains N source adapters going to N destination adapters

2) Have N data-flows, 1 for each table. Run them all in the same package

3) Have N packages

Its generally accepted that option #1 will be quicker when N is fairly small (e.g. 4 or 5 tables. I wouldn't like to speculate as to what will be quicker when (e.g.) N>25, I would guess at option #2 but that's only a guess. Option #3 probably isn't a goer. The amount of hardware will come into play here whatever you do. Perhaps this will help: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx

To be honest, the only person that can answer this is yourself. Test and measure,Test and measure, Test and measure...

And let us know how it goes cos this could be really interesting.

-Jamie

|||

great, my boss says he wants the individual packages which i just make to be used in

creating 1 entire database......like program them in sequence so that the data gets into

just nicely into tables which has foreign keys constraints....

i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......

SSIS is really a pain ...arghhhhhhh

|||

brohans wrote:

great, my boss says he wants the individual packages which i just make to be used in

creating 1 entire database......like program them in sequence so that the data gets into

just nicely into tables which has foreign keys constraints....

If you want to execute things in a defined order then put everything in seperate data-flows and make sure they execute in that defined order using precedence constaints. There is no need to have more than 1 package.

brohans wrote:

i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......

The file path can be made dynamic through the use of expressions. For example, put an expression on the connection string of the flat file connection manager.

brohans wrote:

SSIS is really a pain ...arghhhhhhh

Why is it a pain? So far you haven't got a requirement that cannot be achieved.

Is it SSIS that is a pain or the fact that you're still learning how to use it? Its a hugely powerful tool but because of that there is a learning curve - I am confident you'll like it when you know how to fully leverage it. Like any technology it takes time to learn it properly.

-Jamie

Is there any way to tell when a database file grew

We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.
Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>
|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a[vbcol=seagreen]
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David
|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the
> Quite possible, depending on the other IO activity at the time.
> David
>
|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David
|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>
|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O[vbcol=seagreen]
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5
>
|||Growing in advance is much better. Grow doesn't block operations for which space is available
(operations that uses current space (on same page, extent etc)) or space free in the db file (as it
is when you grow in advance). Grow does block operations that requires new space which isn't
available (which is the case when all extents are used and you do, say, an insert on a table for
which all current extents are used and all pages on those extents are full, and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>

Is there any way to tell when a database file grew

We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
to[vbcol=seagreen]
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
only[vbcol=seagreen]
5[vbcol=seagreen]
>|||Growing in advance is much better. Grow doesn't block operations for which s
pace is available
(operations that uses current space (on same page, extent etc)) or space fre
e in the db file (as it
is when you grow in advance). Grow does block operations that requires new s
pace which isn't
available (which is the case when all extents are used and you do, say, an i
nsert on a table for
which all current extents are used and all pages on those extents are full,
and also other similar
operations that are initiated while the grow operation is running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...[vbco
l=seagreen]
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> the
>[/vbcol]

Is there any way to tell when a database file grew

We have all the files in our database set to autogrow by 10%. During the
day we encountered a 5 minute time period in which SQL activity seemed to
come to a halt. I suspect that during that time period, one of the database
files grew.
Is there any way that I could tell this by looking at a catalog table?
Thanks in advance.Only if you had a trace running at that time with the autogrow event
selected. It is rare that 10% is appropriate for autogrowth setting. On a
larger file size this can take a long time to grow. It should be set to a
fixed amount that will grow in 15 seconds or less. That way you avoid most
timeouts and issues like you have. But it is always best to keep a lot of
free space in the files at all times to avoid autogrow altogether. Make
sure you don't have auto shrink on or a job to shrink them.
--
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> We have all the files in our database set to autogrow by 10%. During the
> day we encountered a 5 minute time period in which SQL activity seemed to
> come to a halt. I suspect that during that time period, one of the
> database
> files grew.
> Is there any way that I could tell this by looking at a catalog table?
> Thanks in advance.
>|||Thanks for the reply Andrew. The file is currently 70GB so therefore the
growthsize is approximately 7GB.
Would an autogrow extent of that size cause a "standstill" for about 5
minutes?
Thanks,
Tom
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O6ZWb2n3FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Only if you had a trace running at that time with the autogrow event
> selected. It is rare that 10% is appropriate for autogrowth setting. On
a
> larger file size this can take a long time to grow. It should be set to a
> fixed amount that will grow in 15 seconds or less. That way you avoid most
> timeouts and issues like you have. But it is always best to keep a lot of
> free space in the files at all times to avoid autogrow altogether. Make
> sure you don't have auto shrink on or a job to shrink them.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:OnKI7sn3FHA.3400@.tk2msftngp13.phx.gbl...
> > We have all the files in our database set to autogrow by 10%. During
the
> > day we encountered a 5 minute time period in which SQL activity seemed
to
> > come to a halt. I suspect that during that time period, one of the
> > database
> > files grew.
> >
> > Is there any way that I could tell this by looking at a catalog table?
> >
> > Thanks in advance.
> >
> >
>|||"TJT" <TJT@.nospam.com> wrote in message
news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> Thanks for the reply Andrew. The file is currently 70GB so therefore the
> growthsize is approximately 7GB.
> Would an autogrow extent of that size cause a "standstill" for about 5
> minutes?
>
Quite possible, depending on the other IO activity at the time.
David|||Would I experience the same problem if I tried to expand the file while
there is free space in the file? In other words - does this problem only
occur when there is 0 free space available?
Thanks,
Tom
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> "TJT" <TJT@.nospam.com> wrote in message
> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> > Thanks for the reply Andrew. The file is currently 70GB so therefore
the
> > growthsize is approximately 7GB.
> >
> > Would an autogrow extent of that size cause a "standstill" for about 5
> > minutes?
> >
> Quite possible, depending on the other IO activity at the time.
> David
>|||"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
>
The amount of free space doesn't really matter. What matters is what else
is going on when the file expands. Ideally you could expand the files at an
off-peak time when users wouldn't be affected and no other workloads are
contending for the volume on which the data file resides.
David|||You can get two issues from an auto grow. The first and most prevalent is
that when Autogrow kickis in that means there is no more space in the file
for the current transaction. That transaction waits until the growth is
completed before it continues. The transaction can actually timeout before
the growth is complete and cause the growth to roll back. Now you are back
to square one again. The other issue may be that if you have very poor I/O
you can affect others that require I/O as well.
--
Andrew J. Kelly SQL MVP
"TJT" <TJT@.nospam.com> wrote in message
news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
>> "TJT" <TJT@.nospam.com> wrote in message
>> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
>> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> the
>> > growthsize is approximately 7GB.
>> >
>> > Would an autogrow extent of that size cause a "standstill" for about 5
>> > minutes?
>> >
>> Quite possible, depending on the other IO activity at the time.
>> David
>>
>|||Thanks so much guys - I really appreciate the help!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Ojo7XIp3FHA.3400@.tk2msftngp13.phx.gbl...
> You can get two issues from an auto grow. The first and most prevalent is
> that when Autogrow kickis in that means there is no more space in the file
> for the current transaction. That transaction waits until the growth is
> completed before it continues. The transaction can actually timeout before
> the growth is complete and cause the growth to roll back. Now you are back
> to square one again. The other issue may be that if you have very poor
I/O
> you can affect others that require I/O as well.
> --
> Andrew J. Kelly SQL MVP
>
> "TJT" <TJT@.nospam.com> wrote in message
> news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> > Would I experience the same problem if I tried to expand the file while
> > there is free space in the file? In other words - does this problem
only
> > occur when there is 0 free space available?
> >
> > Thanks,
> > Tom
> >
> >
> > "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
> >>
> >> "TJT" <TJT@.nospam.com> wrote in message
> >> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
> >> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> > the
> >> > growthsize is approximately 7GB.
> >> >
> >> > Would an autogrow extent of that size cause a "standstill" for about
5
> >> > minutes?
> >> >
> >>
> >> Quite possible, depending on the other IO activity at the time.
> >>
> >> David
> >>
> >>
> >
> >
>|||Growing in advance is much better. Grow doesn't block operations for which space is available
(operations that uses current space (on same page, extent etc)) or space free in the db file (as it
is when you grow in advance). Grow does block operations that requires new space which isn't
available (which is the case when all extents are used and you do, say, an insert on a table for
which all current extents are used and all pages on those extents are full, and also other similar
operations that are initiated while the grow operation is running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJT" <TJT@.nospam.com> wrote in message news:%239U$1Uo3FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Would I experience the same problem if I tried to expand the file while
> there is free space in the file? In other words - does this problem only
> occur when there is 0 free space available?
> Thanks,
> Tom
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23OlYpLo3FHA.3296@.TK2MSFTNGP09.phx.gbl...
>> "TJT" <TJT@.nospam.com> wrote in message
>> news:e1KTM$n3FHA.268@.TK2MSFTNGP10.phx.gbl...
>> > Thanks for the reply Andrew. The file is currently 70GB so therefore
> the
>> > growthsize is approximately 7GB.
>> >
>> > Would an autogrow extent of that size cause a "standstill" for about 5
>> > minutes?
>> >
>> Quite possible, depending on the other IO activity at the time.
>> David
>>
>

Is there any way to deploy an SSIS Package (File Mode) to an App Server without SQL Instance?

We would like to deploy SSIS packages as an ETL Tool to an appserver that does not have SQL Server 2005 installed. Is this possible, or does it HAVE to be executed on the server with SQL 2005 installed?

Kevin
SQL Server (the database engine) doesn't have to be installed. However, you will need to install the SSIS full client (from the SQL Server installation media) and have a valid SQL Server license for that machine.|||

What Phil said is true that you can do this, however, there are other considerations on why you may not want to.

One main reason is if you are moving large amount of data through SSIS. If you have it on a seperate machine:

[Source Machine] - - - large amount of data - - > [SSIS machine] - - - large amoutn of data - - > [destination machine]

VS

[Source Machine] - - - large amount of data - - > [SSIS/Desitination machine]

As you can see, you will be moving large sets of data accross your network twice.

Is there any requirement to install the client component of RS?

Hi all,
I only install the client component of reporting service,
at the beginning, when it is install the support file, it
always failed with only an 'error' message. No other
messages.
I check the log file. There is
Internet Information Services 5.0 (IIS) or later is either
not installed, or it is configured in a way that is
incompatible with a Reporting Services installation.
ASP.NET version 1.1 is not installed or is not registered
with your Web server. ASP.Net is required for the Report
Server and the Report Manager components.
and in the stlog file, I notice
<Prerequisite Name="IsIISInstalled" Checked="Metabase"
Item="/" Found="Can not open metabase" Expected="No error"
Result="Fail" />
To only install the client component, should I install IIS
and ASP1.1 and how do I check that every thing is OK to
install it.
Thanks.
Lindayou need Visual studio.Net 2003 and .Net framework 1.1
IIS is not required
"YUKON2005" <anonymous@.discussions.microsoft.com> a écrit dans le message de
news:38d201c471e9$c199d760$a501280a@.phx.gbl...
> Hi all,
> I only install the client component of reporting service,
> at the beginning, when it is install the support file, it
> always failed with only an 'error' message. No other
> messages.
> I check the log file. There is
> Internet Information Services 5.0 (IIS) or later is either
> not installed, or it is configured in a way that is
> incompatible with a Reporting Services installation.
> ASP.NET version 1.1 is not installed or is not registered
> with your Web server. ASP.Net is required for the Report
> Server and the Report Manager components.
> and in the stlog file, I notice
> <Prerequisite Name="IsIISInstalled" Checked="Metabase"
> Item="/" Found="Can not open metabase" Expected="No error"
> Result="Fail" />
> To only install the client component, should I install IIS
> and ASP1.1 and how do I check that every thing is OK to
> install it.
> Thanks.
> Linda
>|||Thank you!
I will check it.
:)

Is there any impact on server of increasing LOG File size

Thanks to all participants.

I am using SQL Server 2000 with replication object for two location. Log size on publisher go upto 25 times of data file size, I mean 80 MB Data files has maintains 2 GB log file and it is same for all five co's working on same windows 2000 advanced server board.

Since last week server randamly get disconnected from user applications and at that time few tables are not openable at server.

Can any one give a reason ? Why this type misbehaviou done by SQL Server 2000?

Thanks.If you aren't using the log file for recovery purposes, I'd suggest you set the database recovery mode to simple. That will allow SQL Server to jettision the log data once it has been replicated.

SQL Server has a lot of tunable options. It guesses correctly on the appropriate settings for them nearly all of the time (probably more than 99.95% of the time in my experience), but occaisionally it gets something mis-adjusted.

This kind of problem is probably unavoidable (although it can still be reduced), and in those few instances you need a bit of expert help to figure out what is wrong and how to fix it.

-PatP|||Thanks for your suggession. I shrink log file upt to 100 MB and set the database in simple recovery mode by 02-01-2004. After that for last three days nothing happen with user application. Is there any co-relation between problem and log size or any thing more will be findout anywhere else.

Where I can find the reson for orginal disconnection problem, which one not occured for last three working day.

Thanks

R.Mall|||PatP Sir,

I am waiting for your reply regarding following.

Thanks for your suggession. I shrink log file upt to 100 MB and set the database in simple recovery mode by 02-01-2004. After that for last three days nothing happen with user application. Is there any co-relation between problem and log size or any thing more will be findout anywhere else.

Where I can find the reson for orginal disconnection problem, which one not occured for last three working day.

Thanks

R.Mall|||There may be some connection, but I don't know of any easy way to determine if there is a connection or not. Without a lot more information about your configuration (NT, SQL, WAN network, client machine configurations, etc), I'm at a loss to help you more.

The problem lies in the apparent complexity of your configuration. If you have multiple SQL Server databases/instances, many clients, different application loads, etc... The number of items that could interact rapidly gets beyond what I can diagnose remotely.

-PatP|||PatP Sir,

Please look over my setup-

Hardware , OS and Application
Servers-Two
Intel Original server board - 7210TP
CPU HT 2.8GHz, P-IV
HDD - 80GB SATA
1GB Ram - DDR
ATX Cabinet
OS-Windows 2000 Advanced server with service pack IV.
Database - MSSQL Server 2000 with SP3.
Replication - Merge
No.of Database- 6th (1 Master and 5 cos)
Replicated no.of tables ( 42*5 ) + 12 = 222
Publisher - One
Subscriber - One (Pull Subscription)
Transaction - Medium.

Clients-45+ (10 at subscriber site and 35+ at publisher site)
Intel Alex ATX motherboard
CPU Celeron 400
LAN Card PCI 32Bit
128MB Ram
40GB HDD
OS-Windows-98
Application form - Exe and DLL (One Exe + 70+ DLL)
No. of Application - One.
No.Of modules - 11
Application Base Power Builder 7.0

ROUTER
ZYXEL-ISDN Dialup
100IH
Connectivity ISDN Dialup
If you need any more information, so let me know, I will send you immediately.

Thanks

R.Mall|||Dear PatP Sir,

Please see the details.|||All I can do is offer guesses, based on the information that you've provided. To really do a good job, it would take something on the order of 500 pages of information, and probably 40 hours of effort on my part. That isn't something I'm willing to do at this point in time.

A single ISDN line can't always support ten users plus replication on that scale. While it can sometimes do the job, there are othertimes that it will definitely be a bottleneck for performance. This can be mitigated by how you configure the servers to use the connection, but it is still potentially the largest potential problem that I see.

A busy SQL Server would consider 1 Gb of RAM as an hors d'oeurve. I routinely configure busy servers with 8 Gb, then let someone try to talk me down. For 45 users, I might drop as low as 2 Gb, but not without considerable discussion first. I really don't think you have enough RAM in the servers.

Powerbuilder's Datawindows are very easy for developers to use, but they can be "noisy" from a database traffic perspective. As one example, if a datawindow has many rows and the default update method is used after a single row is changed, the entire contents of the database can be written back to the server. Depending on how frequently you've got your subscriber set to "pull" a merge, there can be a lot of data queued up (even with SIMPLE recovery)!

Your problem is probably complex. I don't have the time or resources to do a good job helping you fix it remotely. You really need to get either a dedicated team to help you solve this remotely, or someone to come on site (which would be much better in my opinion). I don't think that casual help via a forum is going to be enough to help you solve this problem.

-PatP|||Dear PatP Sir,

Thanks for your valuable commend on my issue, I will see as per your guidelines to that issue.

I Think you have analyze methods for analyze the problem but due to lack of time and information you can't to it.

Can you help me in another way? Just tell me analyzer methods and help material site or hard copy book, so I will do it myself.

Thanks

R.Mall|||First order of business, get VERY familiar with the NT Performance Analyzer. For this kind of problem, it can provide at least 90% of the information you need to diagnose the problem.

Just as an observation, I wouldn't recommend using a machine to monitor its own performance if I could use a second machine to do it. Running the Performance Monitor can be rather demanding, especially if you are monitoring many counters at once.

Use Performance Monitor to monitor the server at a time when performance is acceptable, and at times when the performance is not acceptable. At first, you'll want to record all of the Performance counters that seem vaguely relevant.

The whole point of this process is to determine where the "weak link" is in your system. It shouldn't take a lot of measurements to trace the problem down to just a few counters (certainly less than ten, probably only three). At first you'll need to track all of the memory, disk, cpu, and network counters... Very shortly you should be able to identify which counters change drastically as performance degrades. Those are the counters that you need to watch carefully going forward.

In the begining, what I'd recommend is to save the performance counters into a file. Run a few (three to five) basline monitors at different times of the day, including at least one with no users online at all. When your server performance starts to degrade, run another stored trace. The worse the performance gets, the more important getting these traces will be.

When you have several traces stored, you'll need to start comparing them. Look for common trace information that is very similar when performance is good as they are when performance is bad. These are "background noise" that you can safely filter out.

As you begin to see which traces change drastically when performance changes, you'll start to understand where the bottleneck is in your system. This is the area where you need to focus your attention, but be forewarned that there can be more than one bottleneck, and sometimes fixing one exposes a completely different one.

As an example of this, I think that your servers are severly RAM starved. I think that they need at least twice as much memory, and quite possibly more than that. However, when you fix that problem, you might well find that something else is also a bottleneck, but due to the RAM problems it was never exposed.

To put this in a "real world" context, if you have an truck that's in bad shape and you need to move something very heavy, you might decide to work on the truck first because you know it can't do the job in its present condition. You tune up the engine (new spark plugs and rotar), only to discover that you need a carburator overhaul too that was masked by the need for the tune up. Once you get the engine running well, you discover there isn't enough transmission fluid. As you fix one problem, it exposes another problem that has been there all along, but that wasn't serious enough to be an issue.

Don't let my comments make you depressed. Your problem is definitely something that can be fixed. I just don't want you to think you'll go in with a "magic bullet", fire one shot and the world will be lovely again. This is a problem that probably took a while to become an issue, and it will take some time and effort to fix. Actually, in my mind performance monitoring and management are part of the ongoing job... The issues are always there, they just don't always get the attention that they should!

-PatP|||PatP sir,

Lot of thanks to you for your valuable suggession, I will do it accordingly and hope I will resolve it with the help of you.

I would like to thanks more to you for past time advice also , when I was in loop of Replication 4 month ago.

Thanks

R.Mall|||Amount of activity that can be generated by the number of users you indicated can not possibly be a source of concern regarding you hardware configuration. If you're experiancing "disconnects" it's probably due to clien timeouts which are a direct result of host CPU being too busy to process any consequent requests. This is a direct affect of under-utilization of indexing capabilities (this statement is dictated purely by the amount of info you provided, and may be easily refuted if the presence of indexes is indicated). Merge replication does not pose this kind of behavior with 45+ concurrent connections. In fact it doesn't manifest itself like this even with 450+ concurrent connections with 20+ runnable processes (I bet you have less than that).

I think you need to analyze execution plans for most frequently invoked statements and concentrate your attention on optimizing them before you burry yourself in perfmon counters.