Re: Stored procedure never finishes
From: Randy Danielson (no_freakin_spam_at_sickofit.com)
Date: 05/10/04
- Next message: Tom Moreau: "Re: Querying with joins across mulitple data sources"
- Previous message: Randy Danielson: "Re: Is there anything like DoEvents in a stored proc?"
- In reply to: Greg Linwood: "Re: Stored procedure never finishes"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 10 May 2004 13:12:53 -0700
Thanks for the reply.
I have found that if I don't use temp tables then the stored proc works.
So instead of using temp tables I am simulating temp tables by creating
a table if it doesn't exist else delete all the records from the table.
So I guess this would point to tempdb. Not really sure what to do with
it, but since rebooting the server temporarily fixes the problem would
suggest that some clean up is happening. I thought that stopping and
restarting the sql server would have done the same clean up, but
apprantly not.
Do you know what this clean up would be that happens during a reboot?
Thanks
Randy
In article <ui7yCTpNEHA.628@TK2MSFTNGP11.phx.gbl>, "Greg Linwood"
<g_linwoodQhotmail.com> says...
> Hi Randy
>
> The information in your post seems to suggest a few wierd things. The fact
> that stopping / starting doesn't make a difference indicates a potential
> logic problem. The information that providing the date range helps indicates
> that you may have a run away query (this can't be fixed by restarting the
> services).
>
> Perhaps when you run the query without the date range, your server simply
> runs out of resources? Have you checked that the connection that the query
> is running on is holding locks? This would indicate that it's definitely
> still active. You do this by issuing "exec sp_lock [spid]" with spid being
> the connection identifier for the connection running the proc (obtained by
> running select @@spid before running the proc). Perhaps the query's being
> blocked by another process? You check that by running "exec sp_who2 [spid]".
>
> You should also check the execution plan of the query to see if it's
> scanning large tables & consider creating indexes that might help speed the
> query up. The Index Tuning Wizard might help you there.
>
> These are just a few ideas.
>
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "Randy D" <no_freakin_spam@sickofit.com> wrote in message
> news:MPG.1b07f34f310bc9c998968a@news.supernews.com...
> > Hello everyone,
> >
> > I have a stored proc that won't finish. I am testing it directly from
> > query analyzer. If we reboot the server then the stored procedure will
> > finish, but only for the first day. I can only test the stored proc on
> > the weekends or in the evening when everyone is not using the system
> > because it is fairly resource intensive.
> >
> > When the stored procedure hangs SQL server is not consuming any
> > processor cycles, but the timer in query analyzer continues to count.
> >
> > At first I thought it was not enough free space in the tempdb and
> > database where the stored proc is running. We increased the size
> > significantly with no effect.
> >
> > Stopping and restarting sql agent and sql server does not make a
> > difference either. What is happening during a reboot?
> >
> > Also if I run the stored proc with a date range in the where clause it
> > will finish properly.
> >
> > SQL Server 2K
- Next message: Tom Moreau: "Re: Querying with joins across mulitple data sources"
- Previous message: Randy Danielson: "Re: Is there anything like DoEvents in a stored proc?"
- In reply to: Greg Linwood: "Re: Stored procedure never finishes"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
