Stored procedure never finishes

From: Randy D (no_freakin_spam_at_sickofit.com)
Date: 05/09/04 

Date: Sun, 9 May 2004 08:51:35 -0700

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: Use query or stored procedure
    ... >>OR write a stored procedure in SQL Server and pass the parameters to it? ... > should change, you can still keep your stored proc interface the same, ... your finished applicaiton to Access. ...
    (borland.public.delphi.database.ado)
  • RE: ODBC Error with Sybase from Win2K Server (but works from WinXP!)
    ... the error occured BEFORE the Sybase stored proc ... created the tempDB..tempTable in the SELECT INTO part of the stored procedure. ... > Our company only allow the use of Merant 3.60 32-bit Sybase ODBC Driver ... Read from the server has timed out. ...
    (microsoft.public.sqlserver.odbc)
  • Re: very strange stored procedure behavior
    ... The execution plan for the sp you get with "Show Execution Plan" ... >im having trouble with a stored procedure. ... >to run the query directly (outside the stored proc) and got a much faster ... >that sql server compiles stored procedures and keeps that execution plan im ...
    (microsoft.public.sqlserver.programming)
  • Re: Pls recommend a replacement for Microsoft Query
    ... SQL Server certainly does. ... The best approach is to create a stored procedure, ... on the server and call the stored proc from Excel. ... use MS Query to call the proc (even create it if you have the ...
    (microsoft.public.excel)
  • Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... When you profile your server, if you find that there is a ... performance issue with a stored proc, ... >Have you profiled both SQL Server and your application ... You should run SQL Server ...
    (microsoft.public.sqlserver.server)
Loading