Blocking in distribution database...

Hello...
I am noticing that everytime the the Agent history clean
up job runs... it causes contention and blocking in the
distribution database.
Has anyone come across this? If so, how did you resolve
this. I am using SQL Server 2000 and every server in my
topology uses SQL Server 2000 as well.
Please advise...
Thanks,
Brett
are you running datamirror? Datamirror does not clean up efficiently.
Blocking in the distribution database is caused by 2 factors
1) too many push agents running simultaneously. Try to stagger and schedule
them. If you can migrate to pull subscriptions.
2) too much replication metadata pooling in your distribution database due
to subscribers who are frequently off line. If you are using anonymous
subscribers this exacerbates the problem. Consider using named subscribers.
"Brett" <anonymous@.discussions.microsoft.com> wrote in message
news:1ae2001c41e7a$fc3ac590$a101280a@.phx.gbl...
> Hello...
> I am noticing that everytime the the Agent history clean
> up job runs... it causes contention and blocking in the
> distribution database.
> Has anyone come across this? If so, how did you resolve
> this. I am using SQL Server 2000 and every server in my
> topology uses SQL Server 2000 as well.
> Please advise...
> Thanks,
> Brett
>

Blocking

I am experiencing blocking problems on SQL Server 2000, SP3a. I have
read the posts and set up a job SQL agent to report on these
occurences I save the results to a table before executing an sp to
kill the offending process id. I am puzzled as to how a process that
does no updating can be guilty of blocking. Typically, I think what
is happening is a process that does no updates is blocking processes
that are trying to do updates. Can someone explain this to me? What
is the best way for me to resolve this?

Thanks!Hal (hforman1@.cfl.rr.com) writes:
> I am experiencing blocking problems on SQL Server 2000, SP3a. I have
> read the posts and set up a job SQL agent to report on these
> occurences I save the results to a table before executing an sp to
> kill the offending process id. I am puzzled as to how a process that
> does no updating can be guilty of blocking. Typically, I think what
> is happening is a process that does no updates is blocking processes
> that are trying to do updates. Can someone explain this to me? What
> is the best way for me to resolve this?

The fact that a reader can block a writer is not strange at all. Say
that you have a report function that performs some serious calculation
and has to scans several big tables. You can get big blocking issues
in this way.

What is the best in your case, is difficult to tell since I don't
know about the blocking situation. If indeed the problem is with
reports, when having a dedicated report server may be an idea.

But it could also be as simple that a few indexes can speed up the
blocking queries.

The action anyway is to get more information about the blocking queries,
and analyse them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Black Windows when starting SQL Server Agent

Hello guys,
I just wanted to ask you a question regarding a funny
behavior on my server. Out of nowhere, when I start the
SQL Server Agent, a command window appears, with the path
to sqlagent.exe, but nothing else happens. The Agent runs
fine while that window is open, but when I close it, it
stops. This was not happening before. I wonder if the
agent is corrupted or something like that. I tried copying
the sqlagent.exe file from another computer, where this
behavior is not showing and it still happens. I wonder if
another file is corrupted.
Any comments and/or suggestions will be appreciated.
Thanks.What version / service pack of SQL Server are you running ..?
--
HTH
Ryan Waight, MCDBA, MCSE
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> Hello guys,
> I just wanted to ask you a question regarding a funny
> behavior on my server. Out of nowhere, when I start the
> SQL Server Agent, a command window appears, with the path
> to sqlagent.exe, but nothing else happens. The Agent runs
> fine while that window is open, but when I close it, it
> stops. This was not happening before. I wonder if the
> agent is corrupted or something like that. I tried copying
> the sqlagent.exe file from another computer, where this
> behavior is not showing and it still happens. I wonder if
> another file is corrupted.
> Any comments and/or suggestions will be appreciated.
> Thanks.|||I'm running SQL Server 2000, with Service Pack 3, but the
problem started before I applied SP3. I applied it to see
if that would fix the problem, but it didn't.
Thanks
>--Original Message--
>What version / service pack of SQL Server are you
running ..?
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> Hello guys,
>> I just wanted to ask you a question regarding a funny
>> behavior on my server. Out of nowhere, when I start the
>> SQL Server Agent, a command window appears, with the
path
>> to sqlagent.exe, but nothing else happens. The Agent
runs
>> fine while that window is open, but when I close it, it
>> stops. This was not happening before. I wonder if the
>> agent is corrupted or something like that. I tried
copying
>> the sqlagent.exe file from another computer, where this
>> behavior is not showing and it still happens. I wonder
if
>> another file is corrupted.
>> Any comments and/or suggestions will be appreciated.
>> Thanks.
>
>.
>|||Francisco,
In the services configuration panel, open sqlserveragent service. In the
LogOn tab, uncheck "Allow service to interact with desktop" checkbox which
is available only when running the service undes localsystem account.
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> Hello guys,
> I just wanted to ask you a question regarding a funny
> behavior on my server. Out of nowhere, when I start the
> SQL Server Agent, a command window appears, with the path
> to sqlagent.exe, but nothing else happens. The Agent runs
> fine while that window is open, but when I close it, it
> stops. This was not happening before. I wonder if the
> agent is corrupted or something like that. I tried copying
> the sqlagent.exe file from another computer, where this
> behavior is not showing and it still happens. I wonder if
> another file is corrupted.
> Any comments and/or suggestions will be appreciated.
> Thanks.|||Are you seing any messages in the SQL Agents ErrorLog.
Right Click 'SQL Server Agent' and Display ErrorLog
--
HTH
Ryan Waight, MCDBA, MCSE
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
> I'm running SQL Server 2000, with Service Pack 3, but the
> problem started before I applied SP3. I applied it to see
> if that would fix the problem, but it didn't.
> Thanks
> >--Original Message--
> >What version / service pack of SQL Server are you
> running ..?
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
> message
> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> >> Hello guys,
> >>
> >> I just wanted to ask you a question regarding a funny
> >> behavior on my server. Out of nowhere, when I start the
> >> SQL Server Agent, a command window appears, with the
> path
> >> to sqlagent.exe, but nothing else happens. The Agent
> runs
> >> fine while that window is open, but when I close it, it
> >> stops. This was not happening before. I wonder if the
> >> agent is corrupted or something like that. I tried
> copying
> >> the sqlagent.exe file from another computer, where this
> >> behavior is not showing and it still happens. I wonder
> if
> >> another file is corrupted.
> >> Any comments and/or suggestions will be appreciated.
> >> Thanks.
> >
> >
> >.
> >|||No errors in the errorlog. Only loading information for
the service. The service runs fine, but I'm just concerned
about the command window not closing. If I close it, the
service stops.
Thanks.
>--Original Message--
>Are you seing any messages in the SQL Agents ErrorLog.
>Right Click 'SQL Server Agent' and Display ErrorLog
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
>> I'm running SQL Server 2000, with Service Pack 3, but
the
>> problem started before I applied SP3. I applied it to
see
>> if that would fix the problem, but it didn't.
>> Thanks
>> >--Original Message--
>> >What version / service pack of SQL Server are you
>> running ..?
>> >
>> >--
>> >HTH
>> >Ryan Waight, MCDBA, MCSE
>> >
>> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
>> message
>> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> >> Hello guys,
>> >>
>> >> I just wanted to ask you a question regarding a funny
>> >> behavior on my server. Out of nowhere, when I start
the
>> >> SQL Server Agent, a command window appears, with the
>> path
>> >> to sqlagent.exe, but nothing else happens. The Agent
>> runs
>> >> fine while that window is open, but when I close it,
it
>> >> stops. This was not happening before. I wonder if the
>> >> agent is corrupted or something like that. I tried
>> copying
>> >> the sqlagent.exe file from another computer, where
this
>> >> behavior is not showing and it still happens. I
wonder
>> if
>> >> another file is corrupted.
>> >> Any comments and/or suggestions will be appreciated.
>> >> Thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||Did you check the other recommendation? The service config?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Francisco Elizondo" <drcoco@.softhome.net> wrote in message
news:0bcd01c3a54c$739f40a0$a601280a@.phx.gbl...
> No errors in the errorlog. Only loading information for
> the service. The service runs fine, but I'm just concerned
> about the command window not closing. If I close it, the
> service stops.
> Thanks.
> >--Original Message--
> >Are you seing any messages in the SQL Agents ErrorLog.
> >
> >Right Click 'SQL Server Agent' and Display ErrorLog
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
> message
> >news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
> >> I'm running SQL Server 2000, with Service Pack 3, but
> the
> >> problem started before I applied SP3. I applied it to
> see
> >> if that would fix the problem, but it didn't.
> >>
> >> Thanks
> >> >--Original Message--
> >> >What version / service pack of SQL Server are you
> >> running ..?
> >> >
> >> >--
> >> >HTH
> >> >Ryan Waight, MCDBA, MCSE
> >> >
> >> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
> >> message
> >> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
> >> >> Hello guys,
> >> >>
> >> >> I just wanted to ask you a question regarding a funny
> >> >> behavior on my server. Out of nowhere, when I start
> the
> >> >> SQL Server Agent, a command window appears, with the
> >> path
> >> >> to sqlagent.exe, but nothing else happens. The Agent
> >> runs
> >> >> fine while that window is open, but when I close it,
> it
> >> >> stops. This was not happening before. I wonder if the
> >> >> agent is corrupted or something like that. I tried
> >> copying
> >> >> the sqlagent.exe file from another computer, where
> this
> >> >> behavior is not showing and it still happens. I
> wonder
> >> if
> >> >> another file is corrupted.
> >> >> Any comments and/or suggestions will be appreciated.
> >> >> Thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Well, it seems like that did the trick. Thanks a lot for
all the help. I will keep you updated if anything else
happens. Sorry to bother you if my dumb questions. :-D
Francisco
>--Original Message--
>Francisco,
>In the services configuration panel, open sqlserveragent
service. In the
>LogOn tab, uncheck "Allow service to interact with
desktop" checkbox which
>is available only when running the service undes
localsystem account.
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> Hello guys,
>> I just wanted to ask you a question regarding a funny
>> behavior on my server. Out of nowhere, when I start the
>> SQL Server Agent, a command window appears, with the
path
>> to sqlagent.exe, but nothing else happens. The Agent
runs
>> fine while that window is open, but when I close it, it
>> stops. This was not happening before. I wonder if the
>> agent is corrupted or something like that. I tried
copying
>> the sqlagent.exe file from another computer, where this
>> behavior is not showing and it still happens. I wonder
if
>> another file is corrupted.
>> Any comments and/or suggestions will be appreciated.
>> Thanks.
>
>.
>|||Yeps, tried the service config, and it did work. This is
the first time something like this happened and I was
scared that it had to do with the sql server install.
Thanks again for all your help.
>--Original Message--
>Did you check the other recommendation? The service
config?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Francisco Elizondo" <drcoco@.softhome.net> wrote in
message
>news:0bcd01c3a54c$739f40a0$a601280a@.phx.gbl...
>> No errors in the errorlog. Only loading information for
>> the service. The service runs fine, but I'm just
concerned
>> about the command window not closing. If I close it, the
>> service stops.
>> Thanks.
>> >--Original Message--
>> >Are you seing any messages in the SQL Agents ErrorLog.
>> >
>> >Right Click 'SQL Server Agent' and Display ErrorLog
>> >
>> >--
>> >HTH
>> >Ryan Waight, MCDBA, MCSE
>> >
>> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
>> message
>> >news:0b3e01c3a546$526fd3a0$a601280a@.phx.gbl...
>> >> I'm running SQL Server 2000, with Service Pack 3, but
>> the
>> >> problem started before I applied SP3. I applied it to
>> see
>> >> if that would fix the problem, but it didn't.
>> >>
>> >> Thanks
>> >> >--Original Message--
>> >> >What version / service pack of SQL Server are you
>> >> running ..?
>> >> >
>> >> >--
>> >> >HTH
>> >> >Ryan Waight, MCDBA, MCSE
>> >> >
>> >> >"Francisco Elizondo" <drcoco@.softhome.net> wrote in
>> >> message
>> >> >news:0c5b01c3a53f$77c2be80$a301280a@.phx.gbl...
>> >> >> Hello guys,
>> >> >>
>> >> >> I just wanted to ask you a question regarding a
funny
>> >> >> behavior on my server. Out of nowhere, when I
start
>> the
>> >> >> SQL Server Agent, a command window appears, with
the
>> >> path
>> >> >> to sqlagent.exe, but nothing else happens. The
Agent
>> >> runs
>> >> >> fine while that window is open, but when I close
it,
>> it
>> >> >> stops. This was not happening before. I wonder if
the
>> >> >> agent is corrupted or something like that. I tried
>> >> copying
>> >> >> the sqlagent.exe file from another computer, where
>> this
>> >> >> behavior is not showing and it still happens. I
>> wonder
>> >> if
>> >> >> another file is corrupted.
>> >> >> Any comments and/or suggestions will be
appreciated.
>> >> >> Thanks.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>