I am currently trying to stabilize an asp.net 2.0 website. I am about 95% sure that the main problem in the stability of the system is that the C# code is leaking SQL connections.
The accepted answer on this post describes exactly my problem:
Why is my SqlCommand returning a string when it should be an int?
That beign said, I am currently running this sql statement to pinpoint the possible problem:
SELECT S.spid, login_time, last_batch, status, hostname, program_name, cmd,
(
select text from sys.dm_exec_sql_text(S.sql_handle)
) as last_sql
FROM sys.sysprocesses S
where dbid > 0
and DB_NAME(dbid) = 'db'
and loginame = 'login'
order by last_batch asc
What I find weird is that the login used to connect to the DB from the website keeps returning last_sql as:
CREATE PROCEDURE name
-- Add the parameters for the stored procedure here
...
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
... Procedure code
The question is, why would a create procedure statement be run over and over?
Also, is it a bad practice to have 3-4 (not so active) website connecting to DB using the same connection string?