Solved

Classic ASP/SQL Server Connection Issue

Posted on 2014-01-12
5
798 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
5 Comments
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 200 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 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 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 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 200 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 100 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 100 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now