0

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?

Community
  • 1
  • 1
Quovadisqc
  • 81
  • 7
  • 3
    dm_exec_sql_text always shows the procedure that is being executed as "create procedure..." – James Z Jun 17 '15 at 13:59
  • 5
    If you think it's leaking SQL connections, have you looked at the c# side to ensure that all SqlConnections are part of a `using` block? – Rowland Shaw Jun 17 '15 at 14:03
  • Sadly, the whole website is using sloppy programmation and isn't using this practice but everything I investiguated is opened / closed using DataAdapters. – Quovadisqc Jun 17 '15 at 19:02
  • @JamesZ this is what I was looking for, thanks – Quovadisqc Jun 17 '15 at 19:03

0 Answers0