Put this in the front of all your SELECT statements to keep simple SELECTs on tables and views from locking the more important functions of UPDATES/ADDS/DELETES:
"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED "
Then, get your developers to go through all their code (start with whoever owns the blocking process) and make sure they shut down and clear their data objects like connections and recordsets. Make sure they have a failsafe that shuts down and clears these items on error or when an application gets shut down. I have one for MS Access that shuts down everything in the .mdb on closing the .mdb. Probably would work for VB.
Then, run a script on your database to find the 10 longest running queries and study them to see if you can speed them up, get rid of them, yada yada.
Then study profiler to see what parameters you can set on sniffing out blocks. If you use Profiler correctly you can probably determine even the underwear color of the offending process. . . . .
(it's Friday).
HTH
>>> "Henry Bukasa " <mssqldba-ezmlmshield-x30157660.[Email address protected] 8/31/2007 7:17 AM >>>
Hi Steve,
The only thing it shows it's "close cursor"(the one blocking) and "open
cursor" (on the blocked).
Thanks
-----Original Message-----
From: Steve Faulkner [mailto:Steve.[Email address protected]
Sent: 31 August 2007 04:11 PM
To: HENRY.[Email address protected]
Subject: RE: Blocked Process
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Check in current activity what transactions are causing the blocking?
-----Original Message-----
From: Henry Bukasa
[Email address protected]
Sent: 31 August 2007 15:05
To: LazyDBA Discussion
Subject: RE: Blocked Process
Hi Thomas,
Basicaslly we did a data center migration 2 months ago but since that we
have so many memory issues. And now the blocking issues.
Thanks
-----Original Message-----
From: Thomas Andre L.
[mailto:mssqldba-ezmlmshield-x36291054.[Email address protected]
Sent: 31 August 2007 02:14 PM
To: LazyDBA Discussion
Subject: RE: Blocked Process
1. Check for poorly written queries
2. Fragmentation issues
3. Bottles necks
4. Has any thing changed or changes applied in the database.
-----Original Message-----
From: Henry Bukasa
[mailto:mssqldba-ezmlmshield-x47026983.[Email address protected]
Sent: Thursday, August 30, 2007 4:44 PM
To: LazyDBA Discussion
Subject: Blocked Process
Hi Guys,
I'm getting some of my processes blocking each other. This is happening
now
every day. Is there anything
I can do to end this? We are on MS SQL Server 2000 SP4.
Thanks
Henry Bukasa
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
**************************EMAIL DISCLAIMER***************************
This email and any files transmitted with it may be confidential and are
intended solely for the use of the individual or entity to whom they are
addressed. If you are not the intended recipient or the individual
responsible for delivering the e-mail to the intended recipient, any
disclosure, copying, distribution or any action taken or omitted to be
taken in reliance on it, is strictly prohibited. If you have received
this
e-mail in error, please delete it and notify the sender or contact
Health
Information Management 312.413.4947.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
FFastFill plc is a public limited company registered in England and Wales
with details as follows;
Registered Office: 1-3 Norton Folgate, London E1 6DB Company Number:978346
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
[Email address protected]
This email has been scanned for all viruses by the FFastFill Email
Security System.
________________________________________________________________________
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page