Re: Stored procedure never finishes

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 05/10/04 

Date: Mon, 10 May 2004 23:43:24 +1000

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
> Win2K sp5
> 4 processors
> 4 GB ram
> 25 GB free disk space
>
> Thanks in advance for any help.
>
> Randy

Relevant Pages

  • Re: Stored procedure never finishes
    ... I have found that if I don't use temp tables then the stored proc works. ... > that you may have a run away query (this can't be fixed by restarting the ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • RE: No Foreign Keys retrieved
    ... I'm not really sure but you could try creating a stored proc in sql server ... My query runs fine but none of the foreign key ... The foreign key values are FirmID, InHouseCounsel, DepartmentPayingID, ...
    (microsoft.public.access.modulesdaovba)
  • Re: execution plan for single stored procedure from profiler
    ... we get all execution tree data not just for ... the execution of the stored proc. ... We won't know the SPID and is very ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: AMO question
    ... could be related to the fact that it is in a stored proc. ... in production I'm running it in a SQL Server job as an Analysis ... Services Query Step. ... That brings on the question...how can I call that without a transaction, ...
    (microsoft.public.sqlserver.olap)
  • Re: execution plan for single stored procedure from profiler
    ... we get all execution tree data not just for ... the execution of the stored proc. ... We won't know the SPID and is very ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)