I created a simple .Net web service which runs a very simple query (which should normally be very quick). The query is something like this:
SELECT v.email
FROM dbo.Reporting r
INNER JOIN dbo.Reporting_EmailList el ON r.reportID = el.reportID
INNER JOIN OtherDB.dbo.V_ActiveDir v ON el.userGUID = v.objectGUID
WHERE r.reportID = @reportID
V_ActiveDir is a view for active directory data. This query essentially gets me a list of email addresses in which the report should be emailed out to. My Reporting table currently only has 3 records. I was trying to add fields to the Reporting table but it was taking forever, which is how I found out that this query was hanging in a sql process. Earlier in the day this script generated the following error:
Timeout expired. The timeout period elapsed prior to completion
of the operation or the server is not responding.
... which explains the hanging process I think. We tried to kill it but now its in a rollback state and I think we may have to reboot sql server (which in my mind is weird that a select query would try to rollback). Is there any way with either sql server or .net to prevent this from occuring again? Or a way I can remove this process in sql... is it possible something is hanging in the thread pool? I can still select from the Reporting table so theres no table lock, only when I try to alter this table does it just spin its wheels.
I don't have much experience with IIS as I don't have access to it, but if there is a suggestions I can pass it along.
Edit: could this be cause because in my catch statement I don't check to see if the SqlConnection is open, and if it is close it?