I just got the lens. I know it doesn't autofocus on my camera body because it doesn't have built in motor but at least it should be usable in manual mode right?
Same here. Got same problem. Pretty sure that the Sigma AF 24-70 F2.8 EX DG has a bug that it won't work with D5000. And it might not work with other non-motor body, e.g., D3000, D3100, etc. However, when used with cheap extension tube to take macro, it works. Damned strange.
I'm assuming you're talking about the autofocus indicator light in the bottom left of the screen.
When I'm using a manual focus lens on my D3000, this still lights up when the picture is in focus under the selected AF sensor. If I'm very out of focus, or the sensor is over something with no contrast to detect, then this light blinks the same way it does in autofocus mode.
The Sigma lens should have an AF/MF selector - if you set it to Manual Focus, your lens should work as usual, because that confirms to the camera body that you're aware that you have to shoot with manual focusing.
If that doesn't work, try flicking the switch back and forth a couple of times; it may be that the sensor isn't working properly
If that doesn't work; try the lens on a different camera body; there might be a fault with the lens.