?
Solved

Classic ASP/SQL Server Connection Issue

Posted on 2014-01-12
5
Medium Priority
?
990 Views
Last Modified: 2014-01-27
Sorry for posting this again but I thought it was solved...it is recurring.  Please note that these errors appear to occur at high volume times on my site.  Most of the time there is no problem but we are going to be having a busy year and I want to get this resolved.

Here are the two errors that I get:

Description: Cannot open database "xxxx" requested by the login. The login failed.
Err #:
Com Err #: -2147467259
Category: Microsoft SQL Server Native Client 10.0

And

Description: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Err #:
Com Err #: -2147467259
Category: Microsoft SQL Server Native Client 10.0

I have other sites with another database that NEVER gets this error even when the site activity is high.  I realize that they may be related or not but I really need them both solved.  My suspicion is that it is a db setting but  may be an iis 7.0 or asp setting as well?

Thanks so much, and please remember that the site works very well most of the time!
0
Comment
Question by:Bob Schneider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 53

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 800 total points
ID: 39775415
Are these on a dedicated site?  

First thing I would check is sql management studio and see what queries are staying open a long time or not closing. Also check the server if the cpu or memory is maxing out.

If you are on a shared or vps, you may not be getting enough resources allocated. I tried for a short time to use a vps and ran into issues with constant crashing.  At the time, part of the problem was only 2 gigs of ram was available total and of that just over 1 gig went to sql server and that is not enough.
0
 
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 400 total points
ID: 39775431
It's not a DB setting.  It's a lack of resources for one reason or another.  The first one sounds like a problem with network congestion and the request not getting thru.  The second sounds like some query is taking too much of your resources.  "lock resources" implies a query that writes a lot to the database.  There's no reason (usually) to lock for reading.
0
 
LVL 53

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 800 total points
ID: 39775436
Beyond the db, if you have options for people to upload images and you are running aspJpeg or imagemagick, those things take up resources as well.  Or if you are using code that outputs word or excel also takes up resources.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 39775500
I suggest you read up on deadlocking.  It is a large subject that is best covered in counltess blogs.  

Having said that, there is no silver bullet other than to roll up your sleeves and A) Identify the query or queries that are the root cause and B) Optimize the query and/or database and/or server.
0
 

Accepted Solution

by:
Danny_De_Schutter earned 400 total points
ID: 39779044
This is a script to search your deadlocks in an mssql database

SELECT  L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName, 
    P.object_id AS LockedObjectId, 
    L.resource_type AS LockedResource, 
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,        
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question