Blowing my mind!

Ok gurus here is somethign that I cant explane. I am haveing serious eratic responses from my SQL 7.0 Server. I can take a stored procedure and run it in a query analyzer window and it works fine, I go to rerun the same code in that window a second time and get no return set. The temp table is null. open a new window and it works fine. This code rand like clock work two weeks ago and there have been no updates to the code. I am on an old server wich has given me many hardware head aches in the past....am I having a hardware falure or is this a SQL issue?
JimRE:
I am haveing serious eratic responses from my SQL 7.0 Server. I can take a stored procedure and run it in a query analyzer window and it works fine, I go to rerun the same code in that window a second time and get no return set. The temp table is null. open a new window and it works fine. This code ran like clock work two weeks ago and there have been no updates to the code. I am on an old server wich has given me many hardware head aches in the past....

Q1 Am I having a hardware falure or is this a SQL issue? Jim

A1 From the information / background given, it may be a resource stressed sql server, sick / dying hardware, connectivity issues, possibly workstation issues, or worse, some combination of ills. It may be helpful to characterize the issue further in multiple ways, e.g.(test for the issue from other workstations, monitor with profiler, check server array and other hardware health, etc., etc., etc.).|||Originally posted by DBA
A1 From the information / background given, it may be a resource stressed sql server, sick / dying hardware, connectivity issues, possibly workstation issues, or worse, some combination of ills. It may be helpful to characterize the issue further in multiple ways, e.g.(test for the issue from other workstations, monitor with profiler, check server array and other hardware health, etc., etc., etc.).

the network admin isnt willing to test the box, he says it sql
the problem persists on multiple workstations, this is quad processer with 4 gig of ram and the resorce monitor says that the processors and mem are not near maxed out.........I think I may have some courupt indexes. I had a maintenance plan boom on me in the middle of this db and I think that hurt my indexes. Is that something that can happen?
Jim

PS Fixed the above mentioned stored procedure now just haveing horendious respons time
Jim|||RE:
the network admin isnt willing to test the box, he says it sql
My that's helpful, what if it is actually both?
RE:
Q1 the problem persists on multiple workstations, this is quad processer with 4 gig of ram and the resorce monitor says that the processors and mem are not near maxed out.........I think I may have some courupt indexes. I had a maintenance plan boom on me in the middle of this db and I think that hurt my indexes. Is that something that can happen? Jim PS Fixed the above mentioned stored procedure now just haveing horendious respons time Jim

A1 Consider running dbcc checkdb; them repair (or drop / recreate) any corrupt indicies found. Corrupting indicies would depend on the nature of "boom". Corrupt indexes may account for long response times.|||Originally posted by DBA
My that's helpful, what if it is actually both?


A1 Consider running dbcc checkdb; them repair (or drop / recreate) any corrupt indicies found. Corrupting indicies would depend on the nature of "boom". Corrupt indexes may account for long response times.

Running dbcc checkdb now...can you give me a guess to how long it will run on a 60ish gig db?
Jim|||Originally posted by JDionne
Q1 Running dbcc checkdb now...can you give me a guess to how long it will run on a 60ish gig db?
Jim

A1 Wouldn't want to hazard a guess (depends on to many variables specific to the environment such as disk access rates, how much corruption exists, number of indexes, etc., etc., etc.).

It is possible to get an estimate of how much tempdb space may be required to checkalloc and chekctables via:

dbcc checkdb With EstimateOnly

Hopefully some of this was helpful (any hardware / corruption or other issues identified)?|||Originally posted by DBA
A1 Wouldn't want to hazard a guess (depends on to many variables specific to the environment such as disk access rates, how much corruption exists, number of indexes, etc., etc., etc.).

It is possible to get an estimate of how much tempdb space may be required to checkalloc and chekctables via:

dbcc checkdb With EstimateOnly

Hopefully some of this was helpful (any hardware / corruption or other issues identified)?

as of now the checkdb showed no errors, and there supposidly is no hardware problems...we have rebuilt a few tables and indexes and It seems to be a bit better...rewriten known code a diff way and that has seemed to help a bit to. We also found a corupt data load file and we are hopeing to get that fixed soon. Thanx for your help...It has been invaluable.
Jim