Invalid Token

A client's SQL 7.0 installation seems to be
deteriorating. We have several stored procs that run on
as job scheduled. It started with one stored proc that
has been running for months with no problems all of the
sudden occasionally "sticking". By sticking I mean the
stored proc never completes. The stored proc is question
run another stored proc as part of the process. It
appears to be sticking on the call of the embedded stored
proc. If I run the embedded stored proc seperately, it
never sticks. If I run the entire process manually, it
will stick "some" of the time. If I cancel the stored
proc when it does stick I get a "Invalid Token" error
from SQL.
I have made several changes to the way the procs are
called and have included jobs to back up the DB and run
maintenance before the calls - and that has "helped" but
not solved the problem.
Now there are other jobs that are "sticking". One is an
import routine that has not been changed in 5 years nor
has it ever stuck before this week.
I think these are all symptoms of a bigger database
problem and not problems in the stored procs or jobs at
all.
Any ideas?What does DBCC CheckDB tell you?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Patrick" <anonymous@.discussions.microsoft.com> wrote in message
news:32e201c3b041$c3151f10$a601280a@.phx.gbl...
> A client's SQL 7.0 installation seems to be
> deteriorating. We have several stored procs that run on
> as job scheduled. It started with one stored proc that
> has been running for months with no problems all of the
> sudden occasionally "sticking". By sticking I mean the
> stored proc never completes. The stored proc is question
> run another stored proc as part of the process. It
> appears to be sticking on the call of the embedded stored
> proc. If I run the embedded stored proc seperately, it
> never sticks. If I run the entire process manually, it
> will stick "some" of the time. If I cancel the stored
> proc when it does stick I get a "Invalid Token" error
> from SQL.
> I have made several changes to the way the procs are
> called and have included jobs to back up the DB and run
> maintenance before the calls - and that has "helped" but
> not solved the problem.
> Now there are other jobs that are "sticking". One is an
> import routine that has not been changed in 5 years nor
> has it ever stuck before this week.
> I think these are all symptoms of a bigger database
> problem and not problems in the stored procs or jobs at
> all.
> Any ideas?
>|||CHECKDB found 0 allocation errors and 0 consistency errors
in database
>--Original Message--
>What does DBCC CheckDB tell you?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com|||Good, then there is nothing structurally wrong with your databases. The
'Invalid Token' message is likely a side effect of killing the process.
It may be that growth has changed the data so that SQL is generating
different query plans and they are now encountering locking and blocking
issues. I would check each query in the procedures and see what SQL thinks
the query plan is. You may find that modifying the index structure will
improve performance and decrease locking. There are also instances
(semi-rare) where the WITH RECOMPILE option may help. These almost always
involving a LIKE comparison that returns significantly different
percentages of the total rows in the table on subsequent calls to the proc.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Patrick" <anonymous@.discussions.microsoft.com> wrote in message
news:01c501c3b04d$17c6f0f0$a101280a@.phx.gbl...
> CHECKDB found 0 allocation errors and 0 consistency errors
> in database
> >--Original Message--
> >What does DBCC CheckDB tell you?
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
>|||Thanks for your help - but I am still stuck.
Here is what I have now - I am tracing the SP - my trace
is set to trace Locks and SP starts and completions.
I run a SP that hangs sometimes (an End of Day routine
that is VERY long of course) and I am having trouble
getting it to fail on my test DB. If I cannot get it to
fail, I move some statements around in the SP a bit and
recreate the proc, and then it might hang. I finally was
able to get a good trace on the SP when it had hung (this
SP normally takes less than 10 secs to complete).
The trace confused me because it shows that all the
statements started and completed, including the last
statement call (the main SP call). However, in query
analyser, the query is still running.
The trace also shows no locks.
These are the last two statements in the trace -
sys_sp_eod is the stored proc that I am executing in QA
that is not ever completing. Any more ideas on how to
trace this down?
+SP:StmtCompleted MS SQL Query Analyzer
WMS_ADMIN 0 10 0 0
2276 15 12:52:52.253
+SP:Completed sys_sp_EOD MS SQL Query Analyzer
WMS_ADMIN 0
2276 15 12:52:52.253|||Does your procedure mail out results using SQL Mail or SQL Agent Mail? If
so, it is probably stuck on a hung MAPI interface call.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Patrick" <anonymous@.discussions.microsoft.com> wrote in message
news:35dd01c3b05a$487e7c60$a601280a@.phx.gbl...
> Thanks for your help - but I am still stuck.
> Here is what I have now - I am tracing the SP - my trace
> is set to trace Locks and SP starts and completions.
> I run a SP that hangs sometimes (an End of Day routine
> that is VERY long of course) and I am having trouble
> getting it to fail on my test DB. If I cannot get it to
> fail, I move some statements around in the SP a bit and
> recreate the proc, and then it might hang. I finally was
> able to get a good trace on the SP when it had hung (this
> SP normally takes less than 10 secs to complete).
> The trace confused me because it shows that all the
> statements started and completed, including the last
> statement call (the main SP call). However, in query
> analyser, the query is still running.
> The trace also shows no locks.
>
> These are the last two statements in the trace -
> sys_sp_eod is the stored proc that I am executing in QA
> that is not ever completing. Any more ideas on how to
> trace this down?
> +SP:StmtCompleted MS SQL Query Analyzer
> WMS_ADMIN 0 10 0 0
> 2276 15 12:52:52.253
> +SP:Completed sys_sp_EOD MS SQL Query Analyzer
> WMS_ADMIN 0
> 2276 15 12:52:52.253
>|||No but maybe this is a clue? I have always suspected
something was amiss in our Receiving tables (this is a WMS
system) because of the SP is seems to get hung in and the
fact that the import SP is the new call that has been
hanging since yesterday - and it uses the receiving
tables. Here is something I found in the trace:
Event Class Text Application Name NT User
Name SQL User Name CPU Reads Writes Duration
Connection ID SPID Start Time
+Missing Column Statistics NO STATS:([Rcv_Doc].
[ORDER_DATE]) MS SQL Query Analyzer WMS_ADMIN
0 2276 15
13:49:59.770
+Missing Column Statistics NO STATS:([Rcv_Doc].
[ORDER_DATE]) MS SQL Query Analyzer WMS_ADMIN
0 2276 15
13:49:59.770
+Missing Column Statistics NO STATS:([RCV_DOC_DETAIL].
[LINE_NO]) MS SQL Query Analyzer WMS_ADMIN
0 2276 15
13:50:00.037
One of the first things I tried last week was running a
backup, statistics and the DB optimizations on the DB
before running EOD - and that seemed to help.
Are those warning above something that may be causing this?
>--Original Message--
>Does your procedure mail out results using SQL Mail or
SQL Agent Mail? If
>so, it is probably stuck on a hung MAPI interface call.
>|||Oh and Order_date is not part of any index..?
I ran DBCC Checktable and reindex - no help.
I read somewhere when doing a google seach that someone
fixed a similar problem by exporting the data, recreating
the tables, and reimporting. I hate to do that because
even if it solves the problem, in my mind it is not
fixed. Do you think that would help?
>+Missing Column Statistics NO STATS:([Rcv_Doc].
>[ORDER_DATE]) MS SQL Query Analyzer WMS_ADMIN
> 0 2276 15
> 13:49:59.770|||Well recreating those table did not help.
I also added an index on order_date just for kicks and it
made the error go away - but same ultimate result - the SP
hanging. I removed the index and the warning came back.
Interestingly enough, the other warning on Line_no for the
other table has not been warning since the tables were
recreated. There IS an index on that col as it is part of
the PK.|||Maybe it is fixed' I went back and did add the WITH
RECOMPILE option and it has now run about 20 times
straight. That is not 100% guranteed indicator which is
what is making this SO frustrating - it has worked after
other changes for a little while.
But I am hoping that fixes it. Have you seen it cause
exactly what I describe if you do not have that option
set? The queries do not use the LIKE clause and this
basic EOD routine has been in production for years..|||There are other issues with SP plan reuse, especially with SQL 7.0. Most of
the time reuse is a good thing. Sometimes it is not, especially with result
sets that vary widely in size. If the WITH RECOMPILE works and solves this
problem, then move on to the next one.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Patrick" <anonymous@.discussions.microsoft.com> wrote in message
news:094001c3b070$97f61b20$a001280a@.phx.gbl...
> Maybe it is fixed' I went back and did add the WITH
> RECOMPILE option and it has now run about 20 times
> straight. That is not 100% guranteed indicator which is
> what is making this SO frustrating - it has worked after
> other changes for a little while.
> But I am hoping that fixes it. Have you seen it cause
> exactly what I describe if you do not have that option
> set? The queries do not use the LIKE clause and this
> basic EOD routine has been in production for years..
>sql