Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Classic ASP/SQL Server Connection Issue

Posted on 2014-01-12
5
Medium Priority
?
1,005 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 54

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 54

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

597 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