It relates to spid85 where I saw the network I/O ERROR.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Are you refering to DBCC SQLPERF? Changes were made regarding some of the
> reporting such as latch waits but I don't know of anything that changed wi
th
> network I/O
> John
> "DGreg" wrote:
>Hi
This will be a user connection, you may want to check the machine that is
was trying to connect.
John
"DGreg" wrote:
[vbcol=seagreen]
> It relates to spid85 where I saw the network I/O ERROR.
>
> "John Bell" wrote:
>|||Thanks John for the responses back, I'll have to look deeper into this.
Greg
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This will be a user connection, you may want to check the machine that is
> was trying to connect.
> John
> "DGreg" wrote:
>|||John,
What I'm experiencing is locked transactions. Intermittently, we experience
a transaction/process which will not complete. The process info display in
enterprise manager displays a "Wait Type" of Network I/O. Although, we do
expect to receive this type of message from time to time because of the
application not responding in a timely fashion, network collision, etc., the
process wait will usually either finish or remove itself from the list. The
problem comes forward when the "waiting" process never quits or otherwise
removes itself after any period of time (wait time continues to grow). This
results in subsequent transactions/processes waiting on this particular
error. Consequently, we experience a system wide data outage on the table
with the lock on it as no other processes can access it. We were under the
impression that this type of "latch" or "hang" was corrected in SP4, but
since it's raised it's head again with SP4 applied to the SQL instance, we
are worried about potential outages in the future. Please let me know if we
have any work arounds for these type of process "latches".
Sincerely,
DGreg
"DGreg" wrote:
[vbcol=seagreen]
> Thanks John for the responses back, I'll have to look deeper into this.
> Greg
>
> "John Bell" wrote:
>|||Hi
It is my understanding that SP4 improved the reporting of such problems
rather than curing them see http://support.microsoft.com/kb/906344 although
I
believe that it is more to do with accessing data pages
http://support.microsoft.com/kb/822101/. Assuming that your lastwaittype is
NETWORKIO then you are waiting on the client to process your data. You may
want to look at what is happening on the client such as the specification of
the machine, also you may want to check if you are returning unnecessary
information such as result sets that are too wide, or procedures that should
be consolidated into one. Check out the scope of your transactions to see if
they can be made shorter and if there are unnecessary transactions, tuning
long running queries will also help. You also mentioned that you are seeing
network collissions, therefore this should be addressed.
John
"DGreg" wrote:
[vbcol=seagreen]
> John,
> What I'm experiencing is locked transactions. Intermittently, we experien
ce
> a transaction/process which will not complete. The process info display i
n
> enterprise manager displays a "Wait Type" of Network I/O. Although, we do
> expect to receive this type of message from time to time because of the
> application not responding in a timely fashion, network collision, etc., t
he
> process wait will usually either finish or remove itself from the list. T
he
> problem comes forward when the "waiting" process never quits or otherwise
> removes itself after any period of time (wait time continues to grow). Th
is
> results in subsequent transactions/processes waiting on this particular
> error. Consequently, we experience a system wide data outage on the table
> with the lock on it as no other processes can access it. We were under th
e
> impression that this type of "latch" or "hang" was corrected in SP4, but
> since it's raised it's head again with SP4 applied to the SQL instance, we
> are worried about potential outages in the future. Please let me know if
we
> have any work arounds for these type of process "latches".
> Sincerely,
> DGreg
> "DGreg" wrote:
>
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Tuesday, February 21, 2012
replication-log reader, dbcc reindex and transaction log backup.
We are using SQL 2K standard with sp4 and Push replication. Let me explain
my situation here first.
We have a weekly dbcc reindex happen at 3:05 am on Sunday morning and
finished successfully at 3:42 am. The replication - log reader started
issuing error in the log reader agent history, "The process could not execute
'sp_replcmds' on serverA..." starting at 3:14 am and being recurred every 6
minutes until 3:44 am. At that time, this error became red and stopped. Our
hourly transaction backup job kicked in at 3:45 am and took 10 minutes to
finish and normally it would take less than 20 seconds to complete. Later in
the morning, someone reported that they don't see any changed data replicated
to the subscriber server and indeed it wasn't working. All these activities
were on the same server, publishing server.
I have some questions regarding replication in this siutation.
1. Will the changes make by dbcc reindex be replicated to the subscriber
server? If so, will this explain the situation described above. if this is
the case, what should I do to minimize the effect of dbcc reindex to
log-reader? It is hard to believe someone would get up at 3:00 am Sunday
morning and making data changes which triggered the transactional
replication.
2. The weekend prior, with the same setup except no transaction log
backup (at that time, the backup mode was still set to simple), we didn't
encounter the 'sp_replcmds' error in the log reader agent. Is there any
relationship between the transaction log backup proces and log reader process
or they run independ of each other?
3. Why does the 'could not execute sp_replcmds' error occur and how to cure
this problem? Until this is solved, replication is down.
4. Is there a way I can schedule the Log Reader agent to stop and start at
a specific time?
wingman
1. Replication moves insert, update, and delete statements. That is all.
No other operation is moved through the replication engine in SQL Server
2000. If you reindex, it only occurs locally. When you reindex, you write
a huge volume of changes into the transaction log which does impact the
response time on the log reader since it has to scan through all of that
data. A change doesn't trigger replication. Replication constantly polls
for changes which have occured and moves anything that it finds.
2. There is not relationship between the log reader and a transaction log
backup. Replication only picks up transactions which have been committed.
A transaction log backup will not remove a committed transaction from the
log until it has been successfully written to the distribution database.
So, the Log Reader can have an impact on the size of the transaction log,
because if the log reader isn't running or can't transfer transactions to
the distribution database for some reason, then the tran log continues to
grow.
3. Is the error continuing to be thrown or was it a transient issue occuring
during the reindex operation?
4. The Log Reader agent is just a job. You can start it and stop it
whenever you want. You can even create a job to stop and start it at
particular times.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:555110A9-6925-4308-8572-F5E2653521AA@.microsoft.com...
> We are using SQL 2K standard with sp4 and Push replication. Let me
> explain
> my situation here first.
> We have a weekly dbcc reindex happen at 3:05 am on Sunday morning and
> finished successfully at 3:42 am. The replication - log reader started
> issuing error in the log reader agent history, "The process could not
> execute
> 'sp_replcmds' on serverA..." starting at 3:14 am and being recurred every
> 6
> minutes until 3:44 am. At that time, this error became red and stopped.
> Our
> hourly transaction backup job kicked in at 3:45 am and took 10 minutes to
> finish and normally it would take less than 20 seconds to complete. Later
> in
> the morning, someone reported that they don't see any changed data
> replicated
> to the subscriber server and indeed it wasn't working. All these
> activities
> were on the same server, publishing server.
> I have some questions regarding replication in this siutation.
> 1. Will the changes make by dbcc reindex be replicated to the subscriber
> server? If so, will this explain the situation described above. if this
> is
> the case, what should I do to minimize the effect of dbcc reindex to
> log-reader? It is hard to believe someone would get up at 3:00 am Sunday
> morning and making data changes which triggered the transactional
> replication.
> 2. The weekend prior, with the same setup except no transaction log
> backup (at that time, the backup mode was still set to simple), we didn't
> encounter the 'sp_replcmds' error in the log reader agent. Is there any
> relationship between the transaction log backup proces and log reader
> process
> or they run independ of each other?
> 3. Why does the 'could not execute sp_replcmds' error occur and how to
> cure
> this problem? Until this is solved, replication is down.
> 4. Is there a way I can schedule the Log Reader agent to stop and start
> at
> a specific time?
> wingman
>
my situation here first.
We have a weekly dbcc reindex happen at 3:05 am on Sunday morning and
finished successfully at 3:42 am. The replication - log reader started
issuing error in the log reader agent history, "The process could not execute
'sp_replcmds' on serverA..." starting at 3:14 am and being recurred every 6
minutes until 3:44 am. At that time, this error became red and stopped. Our
hourly transaction backup job kicked in at 3:45 am and took 10 minutes to
finish and normally it would take less than 20 seconds to complete. Later in
the morning, someone reported that they don't see any changed data replicated
to the subscriber server and indeed it wasn't working. All these activities
were on the same server, publishing server.
I have some questions regarding replication in this siutation.
1. Will the changes make by dbcc reindex be replicated to the subscriber
server? If so, will this explain the situation described above. if this is
the case, what should I do to minimize the effect of dbcc reindex to
log-reader? It is hard to believe someone would get up at 3:00 am Sunday
morning and making data changes which triggered the transactional
replication.
2. The weekend prior, with the same setup except no transaction log
backup (at that time, the backup mode was still set to simple), we didn't
encounter the 'sp_replcmds' error in the log reader agent. Is there any
relationship between the transaction log backup proces and log reader process
or they run independ of each other?
3. Why does the 'could not execute sp_replcmds' error occur and how to cure
this problem? Until this is solved, replication is down.
4. Is there a way I can schedule the Log Reader agent to stop and start at
a specific time?
wingman
1. Replication moves insert, update, and delete statements. That is all.
No other operation is moved through the replication engine in SQL Server
2000. If you reindex, it only occurs locally. When you reindex, you write
a huge volume of changes into the transaction log which does impact the
response time on the log reader since it has to scan through all of that
data. A change doesn't trigger replication. Replication constantly polls
for changes which have occured and moves anything that it finds.
2. There is not relationship between the log reader and a transaction log
backup. Replication only picks up transactions which have been committed.
A transaction log backup will not remove a committed transaction from the
log until it has been successfully written to the distribution database.
So, the Log Reader can have an impact on the size of the transaction log,
because if the log reader isn't running or can't transfer transactions to
the distribution database for some reason, then the tran log continues to
grow.
3. Is the error continuing to be thrown or was it a transient issue occuring
during the reindex operation?
4. The Log Reader agent is just a job. You can start it and stop it
whenever you want. You can even create a job to stop and start it at
particular times.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:555110A9-6925-4308-8572-F5E2653521AA@.microsoft.com...
> We are using SQL 2K standard with sp4 and Push replication. Let me
> explain
> my situation here first.
> We have a weekly dbcc reindex happen at 3:05 am on Sunday morning and
> finished successfully at 3:42 am. The replication - log reader started
> issuing error in the log reader agent history, "The process could not
> execute
> 'sp_replcmds' on serverA..." starting at 3:14 am and being recurred every
> 6
> minutes until 3:44 am. At that time, this error became red and stopped.
> Our
> hourly transaction backup job kicked in at 3:45 am and took 10 minutes to
> finish and normally it would take less than 20 seconds to complete. Later
> in
> the morning, someone reported that they don't see any changed data
> replicated
> to the subscriber server and indeed it wasn't working. All these
> activities
> were on the same server, publishing server.
> I have some questions regarding replication in this siutation.
> 1. Will the changes make by dbcc reindex be replicated to the subscriber
> server? If so, will this explain the situation described above. if this
> is
> the case, what should I do to minimize the effect of dbcc reindex to
> log-reader? It is hard to believe someone would get up at 3:00 am Sunday
> morning and making data changes which triggered the transactional
> replication.
> 2. The weekend prior, with the same setup except no transaction log
> backup (at that time, the backup mode was still set to simple), we didn't
> encounter the 'sp_replcmds' error in the log reader agent. Is there any
> relationship between the transaction log backup proces and log reader
> process
> or they run independ of each other?
> 3. Why does the 'could not execute sp_replcmds' error occur and how to
> cure
> this problem? Until this is solved, replication is down.
> 4. Is there a way I can schedule the Log Reader agent to stop and start
> at
> a specific time?
> wingman
>
Subscribe to:
Posts (Atom)