deadlocks

Hi Everybody

I am new to sqlserver 2000.I know basics of locks.but i dont know how to
resolve deadlock issues.I am cofusing by reading articles with 90%
information and remaining 10% missing.Can any one help me which is the good
site to learn and resolve deadlocks.

Note: I create deadlock. when i try to trace deadlock using dbcc traceon
(1205,3604,-1).In error log showing nothing about the deadlock.

showing created traceon.......

Any help would be appreciated.

--
Message posted via http://www.sqlmonster.comSpecify trace flag 3605 instead of 3604 to write the deadlock info to the
SQL Server error log,

DBCC TRACEON (1205,3605,-1)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"pardhi via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:5cc8e2b282894cce84e1ab206f439136@.SQLMonster.c om...
> Hi Everybody
> I am new to sqlserver 2000.I know basics of locks.but i dont know how to
> resolve deadlock issues.I am cofusing by reading articles with 90%
> information and remaining 10% missing.Can any one help me which is the
> good
> site to learn and resolve deadlocks.
> Note: I create deadlock. when i try to trace deadlock using dbcc traceon
> (1205,3604,-1).In error log showing nothing about the deadlock.
> showing created traceon.......
> Any help would be appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Specify trace flag 3605 instead of 3604 to write the deadlock info to the
> SQL Server error log,
> DBCC TRACEON (1205,3605,-1)

And to make it even better, use 1204, not 1205. 1205 writes deadlock
information as well I believe, but on an extremely detailed level.

Unfortunately, the output from 1204 is cryptic, and far from trivial
to understand.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Have you looked at:
http://support.microsoft.com/kb/271509/EN-US/

Also "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 is a good source for understanding and resolving blocking.

John

"pardhi via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in message
news:5cc8e2b282894cce84e1ab206f439136@.SQLMonster.c om...
> Hi Everybody
> I am new to sqlserver 2000.I know basics of locks.but i dont know how to
> resolve deadlock issues.I am cofusing by reading articles with 90%
> information and remaining 10% missing.Can any one help me which is the
> good
> site to learn and resolve deadlocks.
> Note: I create deadlock. when i try to trace deadlock using dbcc traceon
> (1205,3604,-1).In error log showing nothing about the deadlock.
> showing created traceon.......
> Any help would be appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Hi Dan

I Created deadlock and opened new page and typed the command
DBCC TRACEON (1205,3605,-1).
I didn't see any deadlock message except

(End deadlock search 9232 a deadlock was not found)

but in enterprise manager showing spid 54 blocking and spid 55 blocked.

even tried (1204).

Can you please tell step by step how to see locking.

i am using standard vresion

Thanks

--
Message posted via http://www.sqlmonster.com|||Hi Eland

I Created deadlock and opened new page and typed the command
DBCC TRACEON (1205,3605,-1).
I didn't see any deadlock message except

(End deadlock search 9232 a deadlock was not found)

but in enterprise manager showing spid 54 blocking and spid 55 blocked.

even tried (1204).

Can you please tell step by step how to see locking.

i am using standard version

Thanks

--
Message posted via http://www.sqlmonster.com|||Hi John

Little bit confusing that article if any sent to me please.

--
Message posted via http://www.sqlmonster.com|||reddy via SQLMonster.com (forum@.SQLMonster.com) writes:
> I Created deadlock and opened new page and typed the command
> DBCC TRACEON (1205,3605,-1).
> I didn't see any deadlock message except
> (End deadlock search 9232 a deadlock was not found)
> but in enterprise manager showing spid 54 blocking and spid 55 blocked.
> even tried (1204).

Then you are probably not having a deadlock, just blocking. A deadlock
is when two processes block each other in a way so that none of them
can proceed without one of them being rolled back.

To produce a deadlock do this:

CREATE TABLE x(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
INSERT x (a, b) VALUES (1, 1)

And then run this from two windows in Query Analyzer:

BEGIN TRANSACTION
SELECT * FROM x WITH (HOLDLOCK)
WAITFOR DELAY '00:00:05'
UPDATE x SET b = 12
ROLLBACK TRANSACTION

One of these process will become a deadlock victim.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Confusing in what way?

John|||As Erland mentioned, it seems you are experiencing blocking rather than
deadlocks. The deadlock trace flags are won't help in resolving a blocking
problem.

Long-term blocking is a symptom of long-running queries or transactions.
Blocking may be caused by poor application design or an indication that
tuning is needed. The key is to keep transactions and queries as short as
possible so that blocking locks are held only for short periods. It's a
good practice that one never waits on user response in an open transaction.

You can identify the resource blocking spid 55 with EXEC sp_lock 55. This
will show a status of WAIT for the resource in question. You can find the
name of an object by specifying the reported ObjId in the query SELECT
OBJECT_NAME(<ObjId>). Use DBCC INPUTBUFFER or fn_get_sql to determine the
SQL statements involved in the blocking.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"reddy via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8e97e015504a44bf96d11d443d671bea@.SQLMonster.c om...
> Hi Dan
> I Created deadlock and opened new page and typed the command
> DBCC TRACEON (1205,3605,-1).
> I didn't see any deadlock message except
> (End deadlock search 9232 a deadlock was not found)
> but in enterprise manager showing spid 54 blocking and spid 55 blocked.
> even tried (1204).
> Can you please tell step by step how to see locking.
> i am using standard vresion
> Thanks
> --
> Message posted via http://www.sqlmonster.com|||Thanks a lot Dan.That helps me.

--
Message posted via http://www.sqlmonster.com|||Thanks a lot Erland.That helps me.

--
Message posted via http://www.sqlmonster.com|||Hi John

Nevermind i got my problem fixed
Thanks a lot.

--
Message posted via http://www.sqlmonster.com|||Hi

I am glad to hear that.

You may want to try out the deadlock examples in "Inside SQL Server
2000" in conjunction with the blocker script.

John

Deadlock with Port: 0

I've been diagnosing deadlocks for a last couple weeks at a client site and I understand the KEY: and TAB: locks issues. However, the client recently experienced a series of multi-branch deadlocks for which I can find no information. Attached is the deadlock text from the ERRORLOG. Any help would be much appreciated.

NickHave you tried to capture deadlocks with the profiler ? It is surely clearer than errorlogs.|||Unfortunately I don't have access to the production system this is running on. I have run profiler traces but that still doesn't tell me anything about Port: 0.

Dead locks in SQL SErver 2000

May I know the best solution for minimising dead locks.do you have a specific problem or asking for theory?|||can you privide some information about what's going on? What type of environment, what are users doing when the deadlock occures, is this one table or multipule tables involved?|||I recently stumbled across a pretty good synopsis of deadlocking in SQL Server over at the PASS website (Professional Association for SQL Server) that's in their public-access area. Here's the URL:

http://www.sqlpass.org/resources/sessions/summit2002/S231_S348.ppt

dbreindex vs index defrag question

Does anyone know if dbreindex and index defrag ideally perform the same function? I have been told that index defrag does not hold locks on a table when executed and dbreindex does. Other than this is there any difference between the two functions? My understanding was that dbreindex reindexes the data stored in a table for faster reads and index defrag removes purged data. Am I correct? I am currently running both functions on my SQL server and was advised that I really only need to run the index defrag job. Is this advise correct?http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm

You can reduce fragmentation and improve read-ahead performance by using one of the following:

Dropping and re-creating an index
=================================
Best performance, but places an exclusive table lock on the table, preventing any table access by users and shared table lock on the table, preventing all
but SELECT operations to be performed on it.

OR

Rebuilding an index by using the DBCC DBREINDEX statement
================================================== =======
Faster than dropping and re-creating, but during rebuilding a clustered index, an exclusive table lock is put on the table, preventing any table access by
users. And during rebuilding a nonclustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it

OR

Defragmenting an index by using the DBCC INDEXDEFRAG statement
================================================== ============
It does not hold locks (or only for very shot time) [i.e. online operation], but takes longer time - works little by little. It is not suggested to use for
very fragmented indexes

Hope it helps ...|||Thanks for the info. So basically running both index defrag and dbreindex is redundant because they perform the same function. I will disable my dbreindex job.

Thanks|||http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Remember these are deprecated in 2005. It is also not correct to say that they perform the same function - they perform similar functions.

HTH|||http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Remember these are deprecated in 2005. It is also not correct to say that they perform the same function - they perform similar functions.

HTHWow Pootie...thanks! I read this just in time for it to help me solve the sqlservercentral Question Of The Day!!!
Question: You are writing a new stored procedure to perform maintenance on your SQL Server 2005 databases that defragments the indexes in an online manner. What command should you use?

Correct Answer: ALTER INDEX with the REORGANIZE option

You Answered: ALTER INDEX with the REORGANIZE option

Total Participants: 466

Total Correct Answers: 196 or 42.1% of participants

Explanation:
You should use the ALTER INDEX with the REORGANIZE option because the DBCC commands have been deprecated.|||Wow - you are in the top 42.1% of respondants. Congratulations :beer:|||Hi dsmbwoy,

DBCC DBREINDEX and DBCC INDEXDEFRAG are not one and the same. DBREINDEX sorts the both internal and external fragmantation, whereas INDEXDEFRAG only assists with internal fragmentation. You might want to take a look at the following link, which explains the differences: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx?pf=true