why does sqlserver not like the value: 2 ? ? ?

Question

Post reply

why does sqlserver not like the value: 2 ? ? ?

BenWard

SSCertifiable

Points: 5903
More actions
May 3, 2012 at 7:35 am

#273567

OK guys this is the weirdest thing I have ever seen in SQL Server.
I have a stored proc 15000 lines long and it wont go past line 1208. It just sits there saying 'executing'...
I have a 3 float variables, one happens to have the value 756, another the value 2 and the 3rd is null.
line 1208 goes a little like this...
select @var3 = @var1 / @var2
in fact that is exacly what it says and the server just stops there.
I have done some testing and I have put in some debug code so it looks like
select @var1
select @var2
select @var3 = @var1 / @var2
@var2 does not get returned to the results grid at all and it just carries on saying 'executing..' and using up a whole cpu core on the server.
I have tried this must be 10 times now and it always hangs in the same place.
This happen last month when I ran this program but I didnt have time to investigate so I rebooted the server and it worked fine.
has anyone come accross this sort of thing before?
@@VERSION:
Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 6.0 (Build 6002: Service Pack 2)
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

BenWard SSCertifiable Points: 5903 More actions · Answer 1

Just restarted SQL Services and it's working again.

Am I loosing my marbles?!

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 2

Evidence gathering is my first port of call.

Does the procedure fail every time it's run or intermittently?

Have you checked the SQL and Windows logs for around the time the issue started/happened?

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

BenWard SSCertifiable Points: 5903 More actions · Answer 3

Hi Robin,

Yea it fails each time.

Can't see anything in the event logs at all but I noticed that this:

http://support.microsoft.com/kb/2679255

Is awaiting installation on the server.

Don't know if that might be related but I'll arrange for these bits to be updated on the server over the weekend.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

cphite SSCarpal Tunnel Points: 4432 More actions · Answer 4

Next time it happens, try running this in another window and see what comes up:

select * from sys.dm_os_waiting_tasks where session_id > 50 and session_id != @@spid

select * from sys.dm_exec_requests where session_id > 50 and session_id != @@spid

This will show you what tasks are currently waiting and also give you an idea of what is blocking them, if anything. You can see the same thing in Activity Monitor - I've always found this to be easier to read

BenWard SSCertifiable Points: 5903 More actions · Answer 5

Cool cheers I'll save that one in my useful scripts folder.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 6

There are other performance tools such as the Profiler you could look at.

How big is the database?

Indexing?

The questions could go on lol...

As a side note, I use the RAISERROR with the NOWAIT option to return debug code to the Messages window immediately.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

BenWard SSCertifiable Points: 5903 More actions · Answer 7

Thanks Robin thats a good tip.

DB is 21GB and indexes are all up to date. I think it was some kind of lock error rather than a performance issue. Restarting SQL Services fixed the error so can't be a problem with the DB structure itself I don't think.

I'll profile it soon and look into re-indexing etc as I think it should be faster than it is.

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 8

A lock error certainly progresses the investigation!

Other procedures / jobs / DB requests occurring simultaneously?

Does the TempDB have space?

I think you'll be in a better position when you've maybe profiled the Server.

One more link before I leave you in peace to your profiling...

(deadlocks) http://support.microsoft.com/kb/832524

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

why does sqlserver not like the value: 2 ? ? ?

Cookies on SQLServerCentral