November 29, 2010 at 10:05 pm
Hi,
My name is Shazad. I am a database developer for writing queries and Stored Procedures for Crystal reports.
But currently my company moves me to perform DBA operations. Situation was going well until I got some errors on our Production Server’s when running queries is some reports, the error is “Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” I have noticed that this error would be generated whenever some other users are updating/viewing the records who are also appeared in the selected query/Stored Procedure. The query was also getting slow before throwing this error.
I had tried Google for resolution of this error and found solution that use “WITH (NOLOCK)” hints on base tables and views. Also add the command “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” in my stored procedures. This would resolved the deadlock error.
But now I have observed that the queries/SPs who generate that error are working fine on SQL Server Management Studio. But when I call these queries/SPs on my crystal reports using ODBC connection on the same situation when other users are updating records, the query although won’t throw any deadlock error take very long time to generate the Result Set (the query returned the result in 20 second on Mgmt Studio while same query with same parameters retuned the same Result Set in 25 minutes). This also happens when I create a linked server of our Prod Server and execute the SP from that linked server.
I have also created DB maintenance tasks which I run periodically.
I have tried both ODBC drivers for SQL Server i.e. the driver shipped with windows and the SQL Native Client driver.
Can anyone tell that why it is doing so. Is there any extra configuration required for ODBC connection?
My Production Server Specs are given below.
HP ProLiant DL580 G4 Server
Hardware Detail
•No. of Processors: 4
•No. of Core / Processors: 2 (Dual Core)
•Memory: 8GB
•No. of Cards in slots: None
•No. of Hard Disks: 2 (72GB 15K RPM Each)
Operating System Details
•Operating System: Windows 2008 Server Enterprise Edition R1 Service Pack 1.
•Operating System Type: 64-bit Operating System.
SQL Server Details
•Operating System: SQL Server 2005 Server Enterprise Edition Service Pack 2.
•Operating System Type: 64-bit Operating System.
November 30, 2010 at 3:38 am
Hi,
I would like to have more information about the system queried.
* Does the report data comes from one single database or several?
* If several databases, are they within the same instance/server?
* It seems that several procedures are executed to generate one report. Is that so?
Have you tried running trace to find out what actually takes long?
Cheers
November 30, 2010 at 10:40 am
Hi,
see my replies below"
I would like to have more information about the system queried.
* Does the report data comes from one single database or several?
ans: Single database.
* If several databases, are they within the same instance/server?
ans: there is one instance/server.
* It seems that several procedures are executed to generate one report. Is that so?
ans: No, the report only uses single stored procedure and that stored procedure use only single table-valued function.
Have you tried running trace to find out what actually takes long?
ans: I will try to trace when this situation again happen. As I mentioned that this situation occurs at sometime not always.
Cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply