Non-Yielding on Scheduler 1

I'm receiving the following error on SQL Server 2000 SP4 (no additional
hotfixes after SP4 have been installed.)
Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
Scheduler 1.
Following this message I receive the follow message
Error: 17883, Severity: 1, State: 0
Then logins to the server fail and SQL stop responding. Any assistance in
solving this problem will be appreciated.Check the section titled Error 17881 and Error 17883
in the following article:
http://support.microsoft.com/?id=319892
You would want to search support.microsoft.com on 17883 for
related articles but most of the issues have been addressed
in SP4 (most, not all)
For a good understanding of UMS, check the following
article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_02252004.asp
-Sue
On Fri, 8 Sep 2006 08:27:01 -0700, Sid
<Sid@.discussions.microsoft.com> wrote:
>I'm receiving the following error on SQL Server 2000 SP4 (no additional
>hotfixes after SP4 have been installed.)
>Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
>Scheduler 1.
>Following this message I receive the follow message
>Error: 17883, Severity: 1, State: 0
>Then logins to the server fail and SQL stop responding. Any assistance in
>solving this problem will be appreciated.

Non-Yielding on Scheduler 1

I'm receiving the following error on SQL Server 2000 SP4 (no additional
hotfixes after SP4 have been installed.)
Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
Scheduler 1.
Following this message I receive the follow message
Error: 17883, Severity: 1, State: 0
Then logins to the server fail and SQL stop responding. Any assistance in
solving this problem will be appreciated.Check the section titled Error 17881 and Error 17883
in the following article:
http://support.microsoft.com/?id=319892
You would want to search support.microsoft.com on 17883 for
related articles but most of the issues have been addressed
in SP4 (most, not all)
For a good understanding of UMS, check the following
article:
http://msdn.microsoft.com/library/d...ev_02252004.asp
-Sue
On Fri, 8 Sep 2006 08:27:01 -0700, Sid
<Sid@.discussions.microsoft.com> wrote:

>I'm receiving the following error on SQL Server 2000 SP4 (no additional
>hotfixes after SP4 have been installed.)
>Process 78:6 (e70) UMS Context 0x06485810 appears to be non-yielding on
>Scheduler 1.
>Following this message I receive the follow message
>Error: 17883, Severity: 1, State: 0
>Then logins to the server fail and SQL stop responding. Any assistance in
>solving this problem will be appreciated.

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-Dan
What's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>
|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.co m...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

No triggers, constraints

I have several sql server databases that were recently moved to a new
server. In the process of migrating the databases, any triggers and
constraints attached to tables were removed on accident. I need to add
these objects back into the databases, however I'm worried that I will
have problems with referential integrity since there is a chance
someone may have deleted or updated a record that should have cascaded
changes to other tables but did not since the triggers and constraints
were missing. Is there an easy way to check for problems of this
nature in the databases?

Also - I am planning on generating a script from the database on the
old server that scripts the triggers and constraints, and then use
Query Analyser to run this script against the database on the new
server. Is this the best way to add these objects back into databases
that are already populated with data and running on a live server?

In in a pretty bad position, but at least these are databases that are
not accessed too frequently, so I'm hoping any referential integrity
problems will be minor.
Any help or advice would be greatly appreciated.<jason_s_ford@.hotmail.com> wrote in message
news:1097705795.080513.155750@.c13g2000cwb.googlegr oups.com...
>I have several sql server databases that were recently moved to a new
> server. In the process of migrating the databases, any triggers and
> constraints attached to tables were removed on accident. I need to add
> these objects back into the databases, however I'm worried that I will
> have problems with referential integrity since there is a chance
> someone may have deleted or updated a record that should have cascaded
> changes to other tables but did not since the triggers and constraints
> were missing. Is there an easy way to check for problems of this
> nature in the databases?

Not really, since in order to check this you would need to know the
primary/foreign keys that should be there anyway. So you might as well just
add them back again, and see if you get any errors. Triggers are a bit more
of a problem, but if you're only using them for RI, then hopefully adding
the foreign keys will pick up any problems. If you have more complex
business rules in triggers, then there are no real shortcuts except to work
through your logic and see if any data is incorrect - hopefully you don't
have too many...

> Also - I am planning on generating a script from the database on the
> old server that scripts the triggers and constraints, and then use
> Query Analyser to run this script against the database on the new
> server. Is this the best way to add these objects back into databases
> that are already populated with data and running on a live server?

That's fine. If the script is large, make sure you run it in chunks to avoid
getting too many errors back in one go. And of course try it out on a test
copy of the database first.

> In in a pretty bad position, but at least these are databases that are
> not accessed too frequently, so I'm hoping any referential integrity
> problems will be minor.
> Any help or advice would be greatly appreciated.

For the future, you might want to review how you move databases to new
servers (backup/restore is usually an easy option, and you can't lose
anything that way), and perhaps look at some schema comparison tools, which
can help you find and fix schema issues. This one is quite good, but there
are plenty of others:

http://red-gate.com/SQL_Compare.htm

Simon