This is a tough problem to find. It is roughly caused by a query in
SQL blocking another so it cannot run. The KB articles your guy sent you
are for SQL 2k and SQL 7 on how to diagnose it.
What your guy is trying to have you do is diagnose what is causing
SQL Server to lock. Profiler is a program that comes with SQL server that
will allow you to monitor all queries sent to and from a SQL Server
instance. It's really cool and easy to use (Start
Menu-->Programs-->Microsoft SQL Server-->Profiler)
The big question is who controls the SQL Server box that is having
the problem? In my mind it is the DBA of the SQL box's responsibility to
find out what is causing the locking. A DBA can quickly run the trace or
look at current activity and tell you what is blocking. What someone gave
you below is instructions for a SQL DBA to use to diagnose. They gave you
detailed instructions, print them out and open profiler from the start menu
and you shouldn't have trouble going through them. The only issue then
would be knowing what to look for:) I would start your diagnostics by
checking to see what queries are running long currently. To do this:
1. Open Enterprise manager and connect to the machine having trouble.
2. Click on Management-->Current Activity-->Process Info.
The far right column is called Blocking. All rows should be 0 in the
blocking column. If not, then you have a query that is stopping other
queries from running. Double click on the row and it will tell you what
query. If that query is one of yours, find out why it is blocking. This
can be about 100 different things.
If no processes are locked, then you have to run a trace during the day so
when something is blocked you will have a record of it. Then with the logs
you record from profiler, you can find out what queries run the longest and
what ones use the most CPU. You know that the ones that run the longest can
be improved.
If you want to learn more, lookup "Blocks" in SQL books online and read the
KB articles your guy sent.
I am available to help you with this problem if needed, I have seen it quit
a few times.
Jacob
972-679-8020
[EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Nathan Stanford
Sent: Tuesday, October 21, 2003 7:24 AM
To: [EMAIL PROTECTED]
Subject: Re: Server Error Help
Importance: High
let's start with this first...
1. What database is this? Oracle, MS SQL, My SQL, MS Access or what?
For what you said I assume MS SQL Server
2. Can you send me a copy of just the script?
3. Is this the only script having the error?
I will do the best I can to help you. I used to live in the Dallas/Fort
Worth Area so it will have to all be done remotely. If worse came to worse
and I feel I can help maybe they could fly me in.... although from what you
said so far the dba guy you spoke to is trying to find the problem and not
sure what the exact problem is.
I hope I can help you.
Nathan
----- Original Message -----
From: "PJ Gaenir" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 10:05 PM
Subject: Server Error Help
> Hi Isaac,
>
> Thanks for the offer. I'm not getting dfwcfug messages and email is
> suppressed on the archives I've been using so I had to dig to find
> your email address. Had one fellow offer but never heard back from him
> even to acknowledge my email, so I'm pleading with you. ;-)
>
> My primary problem at the moment is a CF script that hasn't been
> touched in over a year, which I paid a CF guru real$ to review for
> speed and accuracy over a year ago, which has worked fine until
> now--now it is throwing errors like crazy. Or maybe it always did and
> I just never knew till now. (?!)
>
> To troubleshoot, my server guy (a nice guy) sent me info and it's
> like, it could be in Etruscan--I don't know where the program is he's
> referring to, how to start it, or do any of what he asks. Nor do I
> understand how testing something alone locally would replicate an
> error that seems to infer there are multiple CF requests hitting the
> db at once and that's the problem.
>
> First the error code, then the instructions I was given for
> troubleshooting that I don't understand how to implement:
>
> Error: -------------------------------------------------
>
> ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL
> Server Driver][SQL Server]Your transaction (process ID #14) was
> deadlocked with another process and has been chosen as the deadlock
> victim. Rerun your transaction.
>
> --------------------------------------------------------------
>
> Suggestion:
>
> The error indicates that some deadlocks occur in your application.
> You should capture more information to determine what caused the
> problem. You can start these tools to gather information, try to
> reproduce the problem, strop the traces until you see the error and
> analyze the results. Please turn on the trace flags 1204, 3605 and
> 3604 on SQL Server 2000. The flag 1204 will record the deadlock
> details. The flag 3604 will display the result to the screen. The flag
> 3605 will write the result to the error log files. So you also can
> view the SQL Server error log to find the deadlock information there.
>
> 1. Start the Profiler trace
> 2. Start the block script
> 3. Sqldiag.txt report
> 4. PerfMon with the following objects:
> a. SQL Server (all)
> b. Process
> c. PhysicalDisk
> 5. DBCC TRACEON (1204, 3605, 3604)
>
> For more information, please refer to the following KB articles
> Q271509 INF: How to Monitor SQL Server 2000 Blocking
> (http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;Q271509 ) and Q298475 INF: Information Needed to Successfully
> Troubleshoot Application
> (http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;Q298475 ).
>
> --------------------------------------------------------------
>
> My questions are things like, what program, what's a profiler trace
> and how do I start it, what does ANY of that mean, etc. I'm a project
> manager, not a real programmer, but hoping to be able to hire a "real"
> cf coder here in a year to replace me, god knows the company needs it
> lol, just waiting on $. Setting up a remote connection to my db is
> about as rocket science as I've gotten in sql server.
>
> Beyond the above, I have a variety of other small(?) complaints I've
> started to get recently, like a super short query "timing out"
> once in a great while, or the server mysteriously slowing way down
> once in a great while--none easily trackable. But I'm willing to
> donate my measly bucks to getting things solved if I can find experts
> willing to help on such piecemeal things!
>
> Best regards,
> Palyne
> [EMAIL PROTECTED]
>
>
> ---
> [This E-mail scanned for viruses by Declude Virus]
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED] To unsubscribe:
> Send UNSUBSCRIBE to [EMAIL PROTECTED] To subscribe /
> unsubscribe: http://www.dfwcfug.org
>
>
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED] To subscribe / unsubscribe:
http://www.dfwcfug.org
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
