ASP and SQL issues

[Guest]

I am running SQL Server 2000/W2K advance server/Dual proc/4gig Ram. We have a
ASP.Net/C# application/IIS6 that queries this database. Whenever we update or
insert the same record by pressing a "Save" button or the "Activate" button
simultaneously from the app it seems like the connection string to SQL gets
disconnected and throws an occasional "timeout or deadlock". It doesn't
happen on the same operation. It is very sporadic. The errors are different
each time whick makes me believe that there is an issue with the app's
connection to SQL. The asp app does not use stored procedures. What is the
best methodology to troubleshoot this? Start with SQL profiler? How about on
the asp side? I can't post the actual errors because there are too many of
them and very sporadic. Any insights highly appreciated.

 

[Karl Seguin [MVP]]

Are you using Trusted Connections or just standard SQL username/password?

I wouldn't mnid seeing some of your data access code. This could be related
to improperly disposing/referecing resources or something. When I close my
eyes I see static variables all over the place.

SQL Profiler is good. Enterprise Manager also has some tools to monitor
deadlocks, user activity and connections.

Are the processes log running? Are you specifying any lock modifiers in your
queries? Does it only happen when the system is under load?

Karl

 

[sloan]

CLOSE your connections.

CLOSE your IDataReaders.

...

Better yet,

Either download
Data Access Application Block 2.0 (sql server specific)
or
Enterprise.Library.Data ( sql server and other rdbms)

and let that code take care of the best practices for you.
Even with those, you need to
CLOSE your IDataReaders.

If you don't ..... you don't allow others to use the available connections.

Look up Connection Pooling ..... it'll get you started.

 

[Guest]

Hi Karl,
we are using username password on SQL and Integrated Security=SSPI on
web.config. The processes log are running, there are no lock modifiers, and
it happens only when two or more users are hitting the same exact button on
the app at the same time. For example, we have three testers that will hit
the same "OK" button to enter the same customer information. What are you
suggesting on static variables? Any example on this? for every sqlconn.open
we will have a .dispose and .close . Any help is appreciated.

 

[Guest]

for every sqlconn.open we will have a .dispose and .close

 

[Karl Seguin [MVP]]

Can we see any sample DAL code?

Karl

 

[bruce barker]

you are probably getting a deadlock. this is a expected occurrence in a
multiuser db (it can happen any time queries/transactions use more than
1 table). the standard approach is to detect the deadlock, sleep a
random amount of time and try again.

if you get too many deadlocks, then you need to change your schema, or
sql code.


-- bruce (sqlwork.com)