Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?
Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com
Showing posts with label dead. Show all posts
Showing posts with label dead. Show all posts
Monday, March 19, 2012
deadlock detection
Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com
deadlock detection
Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
--
David Gugick
Imceda Software
www.imceda.com
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
--
David Gugick
Imceda Software
www.imceda.com
Sunday, March 11, 2012
Deadlock ! :(
Hi
Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.
I am getting dead lock on the following procedure.
important background information
1. this is a multi user web-based call centre application
2. this procedure loads up a new contact based on priority
I see no reason how a dead lock could occur.
does any one have any idea. could it be something else that is locking up resource used by this procedure?
CREATE PROCEDURE topcat.getNewContactInfo
(
@.contact_id int
)
AS
BEGIN
begin transaction
declare @.id int
set @.id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
UPDATE class_contact SET checked_in = 1 WHERE _id = @.id
SELECT TOP 1 * FROM class_contact
WHERE _id = @.id
commit
END
GO
wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.
James :(Put option (nolock) on your select statement.|||Thank you
I hope it works. I have checked it out in books online and according to that, what you suggest should resolve our dead lock issue.
Cheers
James :)|||dont begin your transactions until you are ready to actually change the data.
try this instead of nolock. see if it works.
CREATE PROCEDURE topcat.getNewContactInfo
(
@.contact_id int
)
AS
BEGIN
declare @.id int
set @.id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
begin transaction --begin xact here!!!!
UPDATE class_contact SET checked_in = 1 WHERE _id = @.id
SELECT TOP 1 * FROM class_contact
WHERE _id = @.id
-- i would test for an error here with a rollback if @.@.error > 0 etc...
this will shorten the time that the actual xact runs lessening contention.|||True
I have thought of doing that after i found out that beginning a transaction would implicitly imply that the select queries within that transaction could also lock resources.
The system was built in a hurry by a very busy programmer and it's some what chaotic, especially the database itself.
now because the system is a multi-user call centre application, originally the "begin transaction" was used to group the select query and update query so to ensure that while someone is bringing up a particular record, another person can't bring up the same record, this was meant to be achieved by setting the "checked_in" flag to "1" with the update statement.
as of now i still can't see how the procedure above could cause a dead lock. i will repeat the simplified version of the store procedure above
begin transaction
STEP1 - set @.id = select _id from table1 where blah blah blah;
STEP2 - update table1 set blah blah blah where _id = @.id
STEP3 - select * from table1 where _id = @.id
commit
if you look at STEP1, STEP2 and STEP3, they are all refering to the same record, i.e. even if they try to lock some record, they will lock the same record, how is a dead lock possible in this situation? well unless if STEP1 lock out STEP2 and STEP3 within that same execution of the procedure, this is highly unlikely tho, 1st because it doesn't make sense for one statment to lock out another statement within the same transactoin, and 2ndly because these DEAD LOCK only occur very occasionally.
any ideas why it would cause a dead lock?
Cheers, sorry for the length (thank you)
james :)|||Originally posted by Ruprect
dont begin your transactions until you are ready to actually change the data.
try this instead of nolock. see if it works.
this will shorten the time that the actual xact runs lessening contention.
i will try your suggestoin tho, it may very well work out for us.
thank you|||Another option - if you do want to hold the lock on the record from the point that you perform the select, is to write a cursor that will select the record for the id, and then you can specifically update the table using:
UPDATE <table_name>
SET <field_name> = <new value>
WHERE CURRENT OF <cursor_name>
just a thought...
(also, while i know you can use CURRENT OF in SQL Server, the example I've given here is Sybase syntax - because that's where I've used it before - but the two are usually the same :) )
Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.
I am getting dead lock on the following procedure.
important background information
1. this is a multi user web-based call centre application
2. this procedure loads up a new contact based on priority
I see no reason how a dead lock could occur.
does any one have any idea. could it be something else that is locking up resource used by this procedure?
CREATE PROCEDURE topcat.getNewContactInfo
(
@.contact_id int
)
AS
BEGIN
begin transaction
declare @.id int
set @.id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
UPDATE class_contact SET checked_in = 1 WHERE _id = @.id
SELECT TOP 1 * FROM class_contact
WHERE _id = @.id
commit
END
GO
wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.
James :(Put option (nolock) on your select statement.|||Thank you
I hope it works. I have checked it out in books online and according to that, what you suggest should resolve our dead lock issue.
Cheers
James :)|||dont begin your transactions until you are ready to actually change the data.
try this instead of nolock. see if it works.
CREATE PROCEDURE topcat.getNewContactInfo
(
@.contact_id int
)
AS
BEGIN
declare @.id int
set @.id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
begin transaction --begin xact here!!!!
UPDATE class_contact SET checked_in = 1 WHERE _id = @.id
SELECT TOP 1 * FROM class_contact
WHERE _id = @.id
-- i would test for an error here with a rollback if @.@.error > 0 etc...
this will shorten the time that the actual xact runs lessening contention.|||True
I have thought of doing that after i found out that beginning a transaction would implicitly imply that the select queries within that transaction could also lock resources.
The system was built in a hurry by a very busy programmer and it's some what chaotic, especially the database itself.
now because the system is a multi-user call centre application, originally the "begin transaction" was used to group the select query and update query so to ensure that while someone is bringing up a particular record, another person can't bring up the same record, this was meant to be achieved by setting the "checked_in" flag to "1" with the update statement.
as of now i still can't see how the procedure above could cause a dead lock. i will repeat the simplified version of the store procedure above
begin transaction
STEP1 - set @.id = select _id from table1 where blah blah blah;
STEP2 - update table1 set blah blah blah where _id = @.id
STEP3 - select * from table1 where _id = @.id
commit
if you look at STEP1, STEP2 and STEP3, they are all refering to the same record, i.e. even if they try to lock some record, they will lock the same record, how is a dead lock possible in this situation? well unless if STEP1 lock out STEP2 and STEP3 within that same execution of the procedure, this is highly unlikely tho, 1st because it doesn't make sense for one statment to lock out another statement within the same transactoin, and 2ndly because these DEAD LOCK only occur very occasionally.
any ideas why it would cause a dead lock?
Cheers, sorry for the length (thank you)
james :)|||Originally posted by Ruprect
dont begin your transactions until you are ready to actually change the data.
try this instead of nolock. see if it works.
this will shorten the time that the actual xact runs lessening contention.
i will try your suggestoin tho, it may very well work out for us.
thank you|||Another option - if you do want to hold the lock on the record from the point that you perform the select, is to write a cursor that will select the record for the id, and then you can specifically update the table using:
UPDATE <table_name>
SET <field_name> = <new value>
WHERE CURRENT OF <cursor_name>
just a thought...
(also, while i know you can use CURRENT OF in SQL Server, the example I've given here is Sybase syntax - because that's where I've used it before - but the two are usually the same :) )
Thursday, March 8, 2012
DeadLock
Hello
we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
dead lock issues.
any idea?
Regards
GopiHave you enabled
DBCC TRACEON (1204) ?
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||adding, error message.
Message
Save Profile Failed Transaction (Process ID 75) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
Source
MicroClear.EnterpriseSolutions.DataProfileServices
Stack Trace
at
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileService.SaveProfileFromXmlDocument(XmlDocument
profileDocument, ArrayList updateOtherProfiles, Boolean insertNotExist,
ProfileParam[] updateProfiles, String MainProfileFieldsList, Boolean
byPassSecurity) at
MicroClear.EnterpriseSolutions.Web.Services.ActionServices.Actor.ProfileHelpers.SaveProfile(ArrayList
updateOtherProfiles, Boolean insertNotExist) at
MicroClear.Actors.eFormsAdmin.AdmSubmitRequest.NewSetFlag() in
c:\eForms\Actors\eFormsAdmin\AdmSubmitRequest.cs:line 2528
Base Exception
Int32 SaveProfileFromXmlDocument(System.Xml.XmlDocument,
System.Collections.ArrayList, Boolean,
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileParam[],
System.String, Boolean)
Actor
SetFlag_2
Module
SubmissionService
Framework Msg
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||No, we didnt trace but using SQL profiler, trying to identify.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Have you enabled
> DBCC TRACEON (1204) ?
> "Gopi" <gopi2it@.gmail.com> wrote in message
> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
>> dead lock issues.
>> any idea?
>> Regards
>> Gopi
>|||Gopi
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
"Gopi" <gopi2it@.gmail.com> wrote in message
news:eH$LooSeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> No, we didnt trace but using SQL profiler, trying to identify.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Have you enabled
>> DBCC TRACEON (1204) ?
>> "Gopi" <gopi2it@.gmail.com> wrote in message
>> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are
>> facing dead lock issues.
>> any idea?
>> Regards
>> Gopi
>>
>
we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
dead lock issues.
any idea?
Regards
GopiHave you enabled
DBCC TRACEON (1204) ?
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||adding, error message.
Message
Save Profile Failed Transaction (Process ID 75) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
Source
MicroClear.EnterpriseSolutions.DataProfileServices
Stack Trace
at
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileService.SaveProfileFromXmlDocument(XmlDocument
profileDocument, ArrayList updateOtherProfiles, Boolean insertNotExist,
ProfileParam[] updateProfiles, String MainProfileFieldsList, Boolean
byPassSecurity) at
MicroClear.EnterpriseSolutions.Web.Services.ActionServices.Actor.ProfileHelpers.SaveProfile(ArrayList
updateOtherProfiles, Boolean insertNotExist) at
MicroClear.Actors.eFormsAdmin.AdmSubmitRequest.NewSetFlag() in
c:\eForms\Actors\eFormsAdmin\AdmSubmitRequest.cs:line 2528
Base Exception
Int32 SaveProfileFromXmlDocument(System.Xml.XmlDocument,
System.Collections.ArrayList, Boolean,
MicroClear.EnterpriseSolutions.DataProfileServices.ProfileParam[],
System.String, Boolean)
Actor
SetFlag_2
Module
SubmissionService
Framework Msg
"Gopi" <gopi2it@.gmail.com> wrote in message
news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Hello
> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
> dead lock issues.
> any idea?
> Regards
> Gopi
>|||No, we didnt trace but using SQL profiler, trying to identify.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Have you enabled
> DBCC TRACEON (1204) ?
> "Gopi" <gopi2it@.gmail.com> wrote in message
> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are facing
>> dead lock issues.
>> any idea?
>> Regards
>> Gopi
>|||Gopi
http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
"Gopi" <gopi2it@.gmail.com> wrote in message
news:eH$LooSeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> No, we didnt trace but using SQL profiler, trying to identify.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uTBqxeSeIHA.4684@.TK2MSFTNGP06.phx.gbl...
>> Have you enabled
>> DBCC TRACEON (1204) ?
>> "Gopi" <gopi2it@.gmail.com> wrote in message
>> news:ett4icSeIHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Hello
>> we having SQL 2000 Ent in cluster enviornment (in SAN), now we are
>> facing dead lock issues.
>> any idea?
>> Regards
>> Gopi
>>
>
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
http://www.sqlpass.org/resources/sessions/summit2002/S231_S348.ppt
dead lock problem
Version: SQL Server 2000 8.00.818
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have been asked to look into a problem in one of the database
at our client site. I have very little idea of the application.
It seems they are facing intermittent deadlock problem. This is the
query of the session which is *always* rolled back.
SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
...
FROM air_itin_price, air_itin_fare_calc
WHERE air_itin_price.air_itin_id = ?
AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_id
order by air_itin_fare_calc.air_itin_price_id
The index on the two tables
ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
(
[air_itin_id],
[psgr_type]
) WITH FILLFACTOR = 50 ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
[dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMARY]
CREATE INDEX [air_itin_price_airitinpriceid] ON
[dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
This is a read only query only, even though the isolation level is same for
all sessions (SERIALIZABLE).
Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer wi
ll
use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behavio
r
same with CLUSTERED INDEX also. I also notice that the primary key on the ta
ble
air_itin_price is a composite index on air_itin_id + psgr_type. But the quer
y
is only for air_itin_id. Does that make a difference?
Any pointers will be appreciated."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_i
d
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same fo
r
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behav
ior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the qu
ery
> is only for air_itin_id. Does that make a difference?
> Any pointers will be appreciated.
some more info from trace:=
Deadlock encountered ... Printing deadlock information
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Wait-for graph
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:1
2004-03-19 14:12:34.65 spid4 PAG: 6:1:3120 CleanCnt:1 M
ode: S Flags: 0x2
2004-03-19 14:12:34.65 spid4 Grant List 0::
2004-03-19 14:12:34.65 spid4 Owner:0x42bcba80 Mode: S Flg:0x0
Ref:1 Life:00000000
SPID:178 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 178 ECID: 0 Statement Type: EXECUT
E Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: RPC Event: sp_cursorfetch;1
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: IX SP
ID:76 ECID:0
Ec
0x713CF510) Value:0x42bd18e0 Cost0/3F0)
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:2
2004-03-19 14:12:34.65 spid4 PAG: 6:1:10267 CleanCnt:1 M
ode: IX Flags: 0x0
2004-03-19 14:12:34.65 spid4 Grant List 2::
2004-03-19 14:12:34.65 spid4 Owner:0x42bd3080 Mode: IX Flg:0x0
Ref:0 Life:02000000
SPID:76 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 76 ECID: 0 Statement Type: INSERT
Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: Language Event: INSERT INTO a
ir_itin_price
(air_itin_id,psgr_type,quantity,pub_fare
,base_fare,q_charge,other_charges,tt
l_markup,ttl_tax,securit
y_fee,fare_tax_rate, us1_tax) VALUES
(398529,0,1,237.24,183.10,0.00,0.00,0.00,44.14,10.00,0.0000,13.74)
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPI
D:178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
2004-03-19 14:12:34.65 spid4 Victim Resource Owner:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:
178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
Looks like it is a conversion deadlock.|||both sessions are using READ COMMITTED.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id =
air_itin_fare_calc.air_itin_price_id
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same
for
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the
behavior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the
query
> is only for air_itin_id. Does that make a difference?
>
Perhaps. If the query used the key, then it's locks would be more narrow.
It looks like the row being inserted by one client might belong in the
resultset of for the other client. If the query specified the full key, it
might be clear to SQL that that is not the case.
Also the query is using
sp_cursorfetch;1
What kind of cursor is the client using?
David
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have been asked to look into a problem in one of the database
at our client site. I have very little idea of the application.
It seems they are facing intermittent deadlock problem. This is the
query of the session which is *always* rolled back.
SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
...
FROM air_itin_price, air_itin_fare_calc
WHERE air_itin_price.air_itin_id = ?
AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_id
order by air_itin_fare_calc.air_itin_price_id
The index on the two tables
ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
(
[air_itin_id],
[psgr_type]
) WITH FILLFACTOR = 50 ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
[dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMARY]
CREATE INDEX [air_itin_price_airitinpriceid] ON
[dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
This is a read only query only, even though the isolation level is same for
all sessions (SERIALIZABLE).
Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer wi
ll
use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behavio
r
same with CLUSTERED INDEX also. I also notice that the primary key on the ta
ble
air_itin_price is a composite index on air_itin_id + psgr_type. But the quer
y
is only for air_itin_id. Does that make a difference?
Any pointers will be appreciated."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_i
d
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same fo
r
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behav
ior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the qu
ery
> is only for air_itin_id. Does that make a difference?
> Any pointers will be appreciated.
some more info from trace:=
Deadlock encountered ... Printing deadlock information
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Wait-for graph
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:1
2004-03-19 14:12:34.65 spid4 PAG: 6:1:3120 CleanCnt:1 M
ode: S Flags: 0x2
2004-03-19 14:12:34.65 spid4 Grant List 0::
2004-03-19 14:12:34.65 spid4 Owner:0x42bcba80 Mode: S Flg:0x0
Ref:1 Life:00000000
SPID:178 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 178 ECID: 0 Statement Type: EXECUT
E Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: RPC Event: sp_cursorfetch;1
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: IX SP
ID:76 ECID:0
Ec
0x713CF510) Value:0x42bd18e0 Cost0/3F0)2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:2
2004-03-19 14:12:34.65 spid4 PAG: 6:1:10267 CleanCnt:1 M
ode: IX Flags: 0x0
2004-03-19 14:12:34.65 spid4 Grant List 2::
2004-03-19 14:12:34.65 spid4 Owner:0x42bd3080 Mode: IX Flg:0x0
Ref:0 Life:02000000
SPID:76 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 76 ECID: 0 Statement Type: INSERT
Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: Language Event: INSERT INTO a
ir_itin_price
(air_itin_id,psgr_type,quantity,pub_fare
,base_fare,q_charge,other_charges,tt
l_markup,ttl_tax,securit
y_fee,fare_tax_rate, us1_tax) VALUES
(398529,0,1,237.24,183.10,0.00,0.00,0.00,44.14,10.00,0.0000,13.74)
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPI
D:178 ECID:0
Ec
0x716F1548) Value:0x42bca6c0 Cost0/0)2004-03-19 14:12:34.65 spid4 Victim Resource Owner:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:
178 ECID:0
Ec
0x716F1548) Value:0x42bca6c0 Cost0/0)Looks like it is a conversion deadlock.|||both sessions are using READ COMMITTED.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id =
air_itin_fare_calc.air_itin_price_id
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same
for
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the
behavior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the
query
> is only for air_itin_id. Does that make a difference?
>
Perhaps. If the query used the key, then it's locks would be more narrow.
It looks like the row being inserted by one client might belong in the
resultset of for the other client. If the query specified the full key, it
might be clear to SQL that that is not the case.
Also the query is using
sp_cursorfetch;1
What kind of cursor is the client using?
David
Dead lock operation query
If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> To add on to John, Enable trace flag (1204); which will record the
>> deadlock chain into the error log.
>> Execute the below command from query analyzer to enable trace flag 1204.
>> DBCC TRACEON(1204,-1)
>> Also see the below url to reduce deadlock.
>> http://www.sql-server-performance.com/deadlocks.asp
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
>> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>>
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> > If I saw there's a dead lock through the Enterprise Manager's current
> > activity section, how can I see those processes which have dead lock
> > (blocking and being blocked) is running what SQL statement? So that I
> > can know which SQL statement cause the dead lock there.
> >
> > Usually when I open the deadlock process, I can see something like
> > 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> > should be cause the the SQL statement in the application scripting
> > portion.
> >
> > I can kill the process from there, but before doing that, I wish to see
> > the SQL statement causing that.
> >
> >
> > Thanks.
> >
> >
> >
> > Peter CCH
> >
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> To add on to John, Enable trace flag (1204); which will record the
>> deadlock chain into the error log.
>> Execute the below command from query analyzer to enable trace flag 1204.
>> DBCC TRACEON(1204,-1)
>> Also see the below url to reduce deadlock.
>> http://www.sql-server-performance.com/deadlocks.asp
>> Thanks
>> Hari
>> SQL Server MVP
>>
>> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
>> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>> If I saw there's a dead lock through the Enterprise Manager's current
>> activity section, how can I see those processes which have dead lock
>> (blocking and being blocked) is running what SQL statement? So that I
>> can know which SQL statement cause the dead lock there.
>> Usually when I open the deadlock process, I can see something like
>> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
>> should be cause the the SQL statement in the application scripting
>> portion.
>> I can kill the process from there, but before doing that, I wish to see
>> the SQL statement causing that.
>>
>> Thanks.
>>
>> Peter CCH
>>
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> > If I saw there's a dead lock through the Enterprise Manager's current
> > activity section, how can I see those processes which have dead lock
> > (blocking and being blocked) is running what SQL statement? So that I
> > can know which SQL statement cause the dead lock there.
> >
> > Usually when I open the deadlock process, I can see something like
> > 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> > should be cause the the SQL statement in the application scripting
> > portion.
> >
> > I can kill the process from there, but before doing that, I wish to see
> > the SQL statement causing that.
> >
> >
> > Thanks.
> >
> >
> >
> > Peter CCH
> >
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Dead lock operation query
If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCH
Hi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default...b;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCH
Hi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/default...b;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>
|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>
|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegr oups.com...
>
|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Dead lock operation query
If I saw there's a dead lock through the Enterprise Manager's current
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/defaul...kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com
activity section, how can I see those processes which have dead lock
(blocking and being blocked) is running what SQL statement? So that I
can know which SQL statement cause the dead lock there.
Usually when I open the deadlock process, I can see something like
'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
should be cause the the SQL statement in the application scripting
portion.
I can kill the process from there, but before doing that, I wish to see
the SQL statement causing that.
Thanks.
Peter CCHHi
sp_who2 will give you information about the processes involved. You may want
to read
http://support.microsoft.com/defaul...kb;en-us;224587 and try
sp_blocker_pss80
http://support.microsoft.com/kb/271509/EN-US/
John
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hi,
To add on to John, Enable trace flag (1204); which will record the deadlock
chain into the error log.
Execute the below command from query analyzer to enable trace flag 1204.
DBCC TRACEON(1204,-1)
Also see the below url to reduce deadlock.
http://www.sql-server-performance.com/deadlocks.asp
Thanks
Hari
SQL Server MVP
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to see
> the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
>|||Hari,
Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
Thanks,
Gopi
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
> deadlock chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||The -1 means to enable the flag for ALL connections. Without the -1, it only
enables the flag for the current connection, and connections that have other
trace flags already enabled.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gopi" <rgopinath@.hotmail.com> wrote in message
news:%23Gne01IkFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Hari,
> Would you know what the "-1" does in "DBCC TRACEON(1204,-1)" ?
> Thanks,
> Gopi
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
>|||We've been using this trace flag with lots of success. You can see in the
SQL error log what DB and the object ids involved (tables & indexes) in the
chain. This makes it "much" easier to find the cause(s) of the deadlock.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23V4GOXFkFHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hi,
> To add on to John, Enable trace flag (1204); which will record the
deadlock
> chain into the error log.
> Execute the below command from query analyzer to enable trace flag 1204.
> DBCC TRACEON(1204,-1)
> Also see the below url to reduce deadlock.
> http://www.sql-server-performance.com/deadlocks.asp
> Thanks
> Hari
> SQL Server MVP
>
> "Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
> news:1122191600.869516.130600@.z14g2000cwz.googlegroups.com...
>|||Peter CCH wrote:
> If I saw there's a dead lock through the Enterprise Manager's current
> activity section, how can I see those processes which have dead lock
> (blocking and being blocked) is running what SQL statement? So that I
> can know which SQL statement cause the dead lock there.
> Usually when I open the deadlock process, I can see something like
> 'sp_xxxxx' ... it seems to be the store procedure.But the dead lock
> should be cause the the SQL statement in the application scripting
> portion.
> I can kill the process from there, but before doing that, I wish to
> see the SQL statement causing that.
>
> Thanks.
>
> Peter CCH
Deadlocks and blocking problems are two different things, although they
do share the "blocking" aspect. It sounds like what you are seeing is
blocking problems, not deadlock issues. Deadlocks are quickly resolved
by SQL Server and don't really show up in system tables. They are
normally identified through application error reporting and through
running traces on a server (from Profiler or using the SQL Trace API).
If you are having blocking problems, you need to identify the long
running transactions and figure out why they are taking so long to
complete. It's possible the application is not fetching result sets
quickly enough or not fetching all results to the end of the result set.
It's also possible there are performance problems in the queries that
require tuning.
The best built-in tool to determine these problems is Profiler or,
better yet, using SQL Trace directly from the sp_trace*() functions. You
can have Profiler script a trace for you using the File - Script Trace
menu option.
Look at the SQL:BatchStarting/Completed and RPC:Starting/Completed
events to start with. Examine the CPU, Duration, and Reads columns and
try to get a handle on the high CPU and long running batches.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Dead Lock Error in Replication
Hello All,
As I was going trough the log file generated (as a result of -Output <filepath> -OutputVerboseLevel 2). I saw some error lines which I would like to share:
Percent Complete: 4
Downloading data changes to the Subscriber
The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSenumchanges(?,?,?,?,?,?,?,?)}
Percent Complete: 0
Category:SQLSERVER
Source: SERVER_PUBLISHER
Number: 1205
Message: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Distributor 'SERVER_PUBLISHER'
I am using Merge Replication. I understand the process failed due to some DeadLocking. How do I further investigate this process and avoid it happening again in the future.
Thanks
AnandTry restarting the Merge Agent and check the query timeout value.|||I have encountered the same problem. Could you tell me how to set the query timeout value? Run command "sp_configure 'remote query timeout', 0" ?|||Hi,
1.Open SQL Enterprise Manager
2.Expand the SQL Server Group > Select your server
3.Right click on your server > Properties
4.Go to the Connections Tab
5.Enter the Query Timeout Value ( bottom ).
As I was going trough the log file generated (as a result of -Output <filepath> -OutputVerboseLevel 2). I saw some error lines which I would like to share:
Percent Complete: 4
Downloading data changes to the Subscriber
The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process could not enumerate changes at the 'Publisher'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSenumchanges(?,?,?,?,?,?,?,?)}
Percent Complete: 0
Category:SQLSERVER
Source: SERVER_PUBLISHER
Number: 1205
Message: Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Subscriber 'SERVER_SUBS'
Disconnecting from Publisher 'SERVER_PUBLISHER'
Disconnecting from Distributor 'SERVER_PUBLISHER'
I am using Merge Replication. I understand the process failed due to some DeadLocking. How do I further investigate this process and avoid it happening again in the future.
Thanks
AnandTry restarting the Merge Agent and check the query timeout value.|||I have encountered the same problem. Could you tell me how to set the query timeout value? Run command "sp_configure 'remote query timeout', 0" ?|||Hi,
1.Open SQL Enterprise Manager
2.Expand the SQL Server Group > Select your server
3.Right click on your server > Properties
4.Go to the Connections Tab
5.Enter the Query Timeout Value ( bottom ).
dead lock
Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
Thanks
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
Thanks
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
Dead Lock
We are using SQLserver 2000 SP3 Standard Version. We are having dead lock an
d
following is log about deadlock
2006-04-12 10:57:05.31 spid4 Wait-for graph
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:1
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
CleanCnt:1 Mode: S Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 1::
2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:528 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type: SELECT
Line #: 38
2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
dbo.VB_ConflictsAppointmentsByDate
@.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
@.Location=,@.ResourceID='000150',@.S_Start
=156,@.S_End=168
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:137 ECID:0 Ec0x49C09530) Value:0x60540ac0 Cost0/BC)
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec0x7E0EB508) Value:0x675dfc20 Cost0/0)
2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec0x7E0EB508) Value:0x675dfc20 Cost0/0)
SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start
=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec0x49C09530) Value:0x60540ac0 Cost0/BC)
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec0x7E0EB508) Value:0x675dfc20 Cost0/0)
> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec0x7E0EB508) Value:0x675dfc20 Cost0/0)
> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:
> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of th
e
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again th
is
> may cause an unacceptable degradation in the concurrency of the applciatio
n.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>
d
following is log about deadlock
2006-04-12 10:57:05.31 spid4 Wait-for graph
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:1
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
CleanCnt:1 Mode: S Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 1::
2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:528 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type: SELECT
Line #: 38
2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
dbo.VB_ConflictsAppointmentsByDate
@.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
@.Location=,@.ResourceID='000150',@.S_Start
=156,@.S_End=168
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:137 ECID:0 Ec
0x49C09530) Value:0x60540ac0 Cost0/BC)2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec
0x7E0EB508) Value:0x675dfc20 Cost0/0)2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec
0x7E0EB508) Value:0x675dfc20 Cost0/0)SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start
=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec
0x49C09530) Value:0x60540ac0 Cost0/BC)> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec
0x7E0EB508) Value:0x675dfc20 Cost0/0)> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec
0x7E0EB508) Value:0x675dfc20 Cost0/0)> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:
> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of th
e
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again th
is
> may cause an unacceptable degradation in the concurrency of the applciatio
n.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>
dead lock
Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
Dead Lock
Hello !!!
I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.
Note: When I execute each transaction independient I have no problems.
Is there any possibility to lock table A completly for the first transaction and release It for second one ??
Could you give me any suggestion of what to do step by step ?
Thanks !!!Are you running these processes by submitting SQL commands from your Java App, or are you just executing existing Stored Procedures?|||begin tran
select 1 from tableA (tablock) where 1=2
update...
if @.@.error != 0 begin
...
rollback tran
return (1)
end
...
commit tran
I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.
Note: When I execute each transaction independient I have no problems.
Is there any possibility to lock table A completly for the first transaction and release It for second one ??
Could you give me any suggestion of what to do step by step ?
Thanks !!!Are you running these processes by submitting SQL commands from your Java App, or are you just executing existing Stored Procedures?|||begin tran
select 1 from tableA (tablock) where 1=2
update...
if @.@.error != 0 begin
...
rollback tran
return (1)
end
...
commit tran
Dead Lock
We are using SQLserver 2000 SP3 Standard Version. We are having dead lock and
following is log about deadlock
2006-04-12 10:57:05.31 spid4 Wait-for graph
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:1
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
CleanCnt:1 Mode: S Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 1::
2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:528 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type: SELECT
Line #: 38
2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
dbo.VB_ConflictsAppointmentsByDate
@.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
@.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:
> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> > We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> > and
> > following is log about deadlock
> >
> > 2006-04-12 10:57:05.31 spid4 Wait-for graph
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:1
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> > CleanCnt:1 Mode: S Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 1::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> > Flg:0x0
> > Ref:0 Life:00000001 SPID:528 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> > SELECT
> > Line #: 38
> > 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> > dbo.VB_ConflictsAppointmentsByDate
> > @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> > @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> > SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:2
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 2::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> > Flg:0x0
> > Ref:0 Life:02000000 SPID:137 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> > UPDATE
> > Line #: 434
> > 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> > 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> >
> > SPID 137 is updating the row which SPID 528 truying to access (This SP is
> > only selecting the rows no update) I specified the UPDLOCK lock hint in
> > update statment with no improvement.
> > Any help is appreciated.
> > --
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of the
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again this
> may cause an unacceptable degradation in the concurrency of the applciation.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>
following is log about deadlock
2006-04-12 10:57:05.31 spid4 Wait-for graph
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:1
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
CleanCnt:1 Mode: S Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 1::
2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S Flg:0x0
Ref:0 Life:00000001 SPID:528 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type: SELECT
Line #: 38
2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
dbo.VB_ConflictsAppointmentsByDate
@.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
@.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
2006-04-12 10:57:05.31 spid4
2006-04-12 10:57:05.31 spid4 Node:2
2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 10:57:05.31 spid4 Grant List 2::
2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:137 ECID:0
2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type: UPDATE
Line #: 434
2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
2006-04-12 10:57:05.31 spid4 Requested By:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
SPID 137 is updating the row which SPID 528 truying to access (This SP is
only selecting the rows no update) I specified the UPDLOCK lock hint in
update statment with no improvement.
Any help is appreciated.
--
Farhan"Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> and
> following is log about deadlock
> 2006-04-12 10:57:05.31 spid4 Wait-for graph
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:1
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> CleanCnt:1 Mode: S Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 1::
> 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> Flg:0x0
> Ref:0 Life:00000001 SPID:528 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> SELECT
> Line #: 38
> 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> dbo.VB_ConflictsAppointmentsByDate
> @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> 2006-04-12 10:57:05.31 spid4
> 2006-04-12 10:57:05.31 spid4 Node:2
> 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-04-12 10:57:05.31 spid4 Grant List 2::
> 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:137 ECID:0
> 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> UPDATE
> Line #: 434
> 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> 2006-04-12 10:57:05.31 spid4 Requested By:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> SPID 137 is updating the row which SPID 528 truying to access (This SP is
> only selecting the rows no update) I specified the UPDLOCK lock hint in
> update statment with no improvement.
> Any help is appreciated.
> --
It's hard to suggest a change that resolves teh deadlock with minimal
degradation of concurrency or correctness without a complete picture of the
database structures, transactions and concurreny requirements of the
application.
However as a general matter, if you escalate the locks taken by one of the
transactions to a table lock, the deadlock should will go away. Again this
may cause an unacceptable degradation in the concurrency of the applciation.
Another aproach to simply optimize the SELECT. If it can read less, lock
less, or use a different index, etc you might resolve the deadlock and
improve appliction performance to boot.
David|||Thanks David.
I was thinking on same line, update sp is very complex and hard to adjust
versus select.
Appreciated.
--
Farhan
"David Browne" wrote:
> "Farhan Soomro" <FarhanSoomro@.discussions.microsoft.com> wrote in message
> news:E43DE237-5437-4BE0-AA3D-80B14FD67B9C@.microsoft.com...
> > We are using SQLserver 2000 SP3 Standard Version. We are having dead lock
> > and
> > following is log about deadlock
> >
> > 2006-04-12 10:57:05.31 spid4 Wait-for graph
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:1
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:53 (480260485b26)
> > CleanCnt:1 Mode: S Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 1::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x66a25520 Mode: S
> > Flg:0x0
> > Ref:0 Life:00000001 SPID:528 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 528 ECID: 0 Statement Type:
> > SELECT
> > Line #: 38
> > 2006-04-12 10:57:05.31 spid4 Input Buf: Language Event: Exec
> > dbo.VB_ConflictsAppointmentsByDate
> > @.StartDate='04/12/2006',@.EndDate='04/12/2006',@.Discipline='',
> > @.Location=,@.ResourceID='000150',@.S_Start=156,@.S_End=168
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: X
> > SPID:137 ECID:0 Ec:(0x49C09530) Value:0x60540ac0 Cost:(0/BC)
> > 2006-04-12 10:57:05.31 spid4
> > 2006-04-12 10:57:05.31 spid4 Node:2
> > 2006-04-12 10:57:05.31 spid4 KEY: 8:1221032277:1 (b3016336158a)
> > CleanCnt:1 Mode: X Flags: 0x0
> > 2006-04-12 10:57:05.31 spid4 Grant List 2::
> > 2006-04-12 10:57:05.31 spid4 Owner:0x60541880 Mode: X
> > Flg:0x0
> > Ref:0 Life:02000000 SPID:137 ECID:0
> > 2006-04-12 10:57:05.31 spid4 SPID: 137 ECID: 0 Statement Type:
> > UPDATE
> > Line #: 434
> > 2006-04-12 10:57:05.31 spid4 Input Buf: RPC Event: sp_prepexec;1
> > 2006-04-12 10:57:05.31 spid4 Requested By:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> > 2006-04-12 10:57:05.31 spid4 Victim Resource Owner:
> > 2006-04-12 10:57:05.31 spid4 ResType:LockOwner Stype:'OR' Mode: S
> > SPID:528 ECID:0 Ec:(0x7E0EB508) Value:0x675dfc20 Cost:(0/0)
> >
> > SPID 137 is updating the row which SPID 528 truying to access (This SP is
> > only selecting the rows no update) I specified the UPDLOCK lock hint in
> > update statment with no improvement.
> > Any help is appreciated.
> > --
> It's hard to suggest a change that resolves teh deadlock with minimal
> degradation of concurrency or correctness without a complete picture of the
> database structures, transactions and concurreny requirements of the
> application.
> However as a general matter, if you escalate the locks taken by one of the
> transactions to a table lock, the deadlock should will go away. Again this
> may cause an unacceptable degradation in the concurrency of the applciation.
> Another aproach to simply optimize the SELECT. If it can read less, lock
> less, or use a different index, etc you might resolve the deadlock and
> improve appliction performance to boot.
> David
>
>
dead lock
Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some point?I believe SP3?the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some point?I believe SP3?the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
Dead links
Hi.
In report manager, links to Home, My subscription, Site settings, Help,
Contents, Properties works fine.
But links to New Folder, New Data source and Upload files are completly
dead - what could be wrong?
I'v installed evalution edition incl. SP1.
Best
LABProblem solved, it was a matter of browser-sequrity.
/LAB
"LAB" <nomail@.nomail.dk> skrev i en meddelelse
news:#NLIMzXnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Hi.
> In report manager, links to Home, My subscription, Site settings, Help,
> Contents, Properties works fine.
> But links to New Folder, New Data source and Upload files are completly
> dead - what could be wrong?
> I'v installed evalution edition incl. SP1.
> Best
> LAB
>
In report manager, links to Home, My subscription, Site settings, Help,
Contents, Properties works fine.
But links to New Folder, New Data source and Upload files are completly
dead - what could be wrong?
I'v installed evalution edition incl. SP1.
Best
LABProblem solved, it was a matter of browser-sequrity.
/LAB
"LAB" <nomail@.nomail.dk> skrev i en meddelelse
news:#NLIMzXnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Hi.
> In report manager, links to Home, My subscription, Site settings, Help,
> Contents, Properties works fine.
> But links to New Folder, New Data source and Upload files are completly
> dead - what could be wrong?
> I'v installed evalution edition incl. SP1.
> Best
> LAB
>
Dead End...
Hi,
I want to register a new server,
I go to Server Management Studio -> Right Click Database Engine -> New -> Server registration
Make up a server name and test
Apears the "..sql server does not allow remote connections..."
I go to surface area configuration for services and connections, but it doenst have any components, because i have to make a server registration first!
I dont understand...
If you please help me...
Thanx
Best Regards
Can't you connect to the local server by using the Surface Area Configuration tool on the remote server?|||Can you check whether the SQL Server services have been started onthat machine?
http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=12&faqid=143 fyi.
|||Hi again,
Thank you for the asnwer,
first i install the express edition, it makes a registration of a server automatically (my computer name)\sqlexpress, i made my database etc...
Now i want to make a new server registration, i've been in (F1) help but it doenst seem clear, i cant find the change computer, nor the feature configuration, and dont know how i can see if the system is runing...
I've went to this page
http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=12&faqid=143
but it only explains the SAC system that i have done...
|||You need to either log in to the console of the server machine or use remote desktop to log in. Then you can run the local SAC tool on the server machine to start SQL Server if it isn't running and to configure SQL Server to accept connections from TCP/IP. You'll also need to make sure the firewall on the server machine is configured to allow access to the port SQL Server is using.
Here's a blog post that will run you through the process step-by-step: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx. The information in this KB article might help too: http://support.microsoft.com/default.aspx?scid=kb%3bEN-US%3b914277
Hope this helps,
Steve
Dead easy string handling question
Hi there,
I'm a bit embarrassed about this question, because I'm sure that a lot of you would find it trivial, but I'm really not the best at T-SQL and especially not string handling.
I'm trying to generate a 'parent' value, by replacing characters in the 'child' field with ''. It's a classic Chart of Accounts sort of problem, for feeding into a Parent/Child dimension in AS. It's a lot easier to understand if you look at this:
This is the set I have:
GLCode GLDescription
0.-.-.- Balance S
0.0.-.- Balance S.Balance Sheet
0.0.0.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 Balance S.Balance Sheet.Balance Sheet.Assets - Area3
This is the set I want
Ch GLCode Par GLCode GLDescription
0.-.-.- -.-.-.- Balance S
0.0.-.- 0.-.-.- Balance S.Balance Sheet
0.0.0.- 0.0.-.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area3
That is to say,
0.0.0.- is the parent of 0.0.0.5001
0.0.-.- is the parent of 0.0.0.-
0.-.-.- is the parent of 0.0.-.-
So, what I really need to do is replace the last non '-' string with '-'. I've tried various combos of PATINDEX, CHARINDEX, REPLACE etc, but I'm really struggling. To make in more complicated, and of the strings between the . can be any length.
Any ideas?
Maybe something like this would work for you:
SET NOCOUNT ON
DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20),
[Description] varchar(100)
)
INSERT INTO @.MyTable VALUES ( '0.-.-.-', 'Balance S' )
INSERT INTO @.MyTable VALUES ( '0.0.-.-', 'Balance S.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '0.0.0.-', 'Balance S.Balance Sheet.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5000', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area1' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area2' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5002', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area3' )
SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1 THEN '0.0.0.-'
WHEN parsename( GLCode, 2 ) <> '-' THEN '0.0.-.-'
WHEN parsename( GLCode, 3 ) <> '-' THEN '0.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END,
[Description]
FROM @.MyTable
ORDER BY GLCode
Ch GLCode Par GLCode Description
-- - -
0.-.-.- -.-.-.- Balance S
0.0.-.- 0.-.-.- Balance S.Balance Sheet
0.0.0.- 0.0.-.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area3
Thanks for that Arnie,
I did something a bit stupid in my example - I implied that the source dataset is a lot simpler than it acually is. It's a 13,000 row Chart of Accounts, and I just gave you the top 6 rows. If we look further down, and resample the data, we can see members like this:
In this case, 3.S.A.0001 , 3.S.A.0003 , 3.S.A.0005 , 3.S.A.0102 , 3.S.A.0110 are the children of parent 3.S.A.-
3.S.A.- is the child of parent 3.S.-.-
3.S.-.- is the child of parent 3.-.-.-
(and we can therefore infer that the top member, 3.R.W.4501 is the child of 3.R.W.-
so I don't think your approach of hardcoding the parent into the CASE would work. But your approach was perfectly reasonable, given the lame example you had to work with!
You used ISNUMERIC, which I didn't think of, and PARSENAME, which I haven't used before, so I'll see if I can use those in a more dynamic solution.
As you've probably guessed, I'm a long way from being a T-SQL ninja, so if anyone has any clever ideas on how I should approach this, I'd be interested to know...
|||
The approach I supplied previously seems to work just fine on the additional values you supplied.
(And should as long as the forth part is always a number.)
|||Maybe I'm missing something, but I don't quite understand how hardcoding '0's into the output is going to help where the code is something like '2.H.A.003'. But you've given me some ideas, so thanks for your help.
SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1 THEN '0.0.0.-'
WHEN parsename( GLCode, 2 ) <> '-' THEN '0.0.-.-'
WHEN parsename( GLCode, 3 ) <> '-' THEN '0.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END,
[GLDescription]
FROM dbo.WRK_CoA
ORDER BY GLCode
Ch GLCode Par GLCode GLDescription
1.S.U.0380 0.0.0.- Academy of F&H Education.Planning
1.S.U.0600 0.0.0.- Academy of F&H Education.Planning and Develop
2.-.-.- -.-.-.- Adult Academy
2.B.-.- 0.-.-.- Adult Academy.Creative & Cultural Industries
2.B.S.- 0.0.-.- Adult Academy.Creative & Cultural Industri
2.B.S.0130 0.0.0.- Adult Academy.Creative & Cultural Indus
2.H.-.- 0.-.-.- Adult Academy.Employment Services
2.H.A.- 0.0.-.- Adult Academy.Employment Services.Bu
2.H.A.0001 0.0.0.- Adult Academy.Employment Services.Busin
2.H.A.0003 0.0.0.- Adult Academy.Employment Services.Busines
2.H.A.0005 0.0.0.- Adult Academy.Employment Services.Busine
2.H.A.0102 0.0.0.- Adult Academy.Employment Services.Business
2.H.A.0110 0.0.0.- Adult Academy.Employment Services.Business
Sam,
Sorry, I should have gone into more detail. You can use PARSENAME() to deconstruct the values, AND you can use
PARSENAME() to re-construct the values.
Hopefully this will give you the guidance you need.
DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20)
)
INSERT INTO @.MyTable VALUES ( '0.-.-.-' )
INSERT INTO @.MyTable VALUES ( '0.0.-.-' )
INSERT INTO @.MyTable VALUES ( '0.0.0.-' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5000' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5001' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5002' )
INSERT INTO @.MyTable VALUES ( '3.R.W.4501' )
INSERT INTO @.MyTable VALUES ( '3.S.-.-' )
INSERT INTO @.MyTable VALUES ( '3.S.A.-' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0001' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0003' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0005' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0102' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0110' )
SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1
THEN parsename( GLCode, 4 ) + '.' + parsename( GLCode, 3 ) + '.' + parsename( GLCode, 2 ) + '.-'
WHEN parsename( GLCode, 2 ) <> '-'
THEN parsename( GLCode, 4 ) + '.' + parsename( GLCode, 3 ) + '.-.-'
WHEN parsename( GLCode, 3 ) <> '-'
THEN parsename( GLCode, 4 ) + '.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END
FROM @.MyTable
ORDER BY GLCode
Ch GLCode Par GLCode
-- -
0.-.-.- -.-.-.-
0.0.-.- 0.-.-.-
0.0.0.- 0.0.-.-
0.0.0.5000 0.0.0.-
0.0.0.5001 0.0.0.-
0.0.0.5002 0.0.0.-
3.R.W.4501 3.R.W.-
3.S.-.- 3.-.-.-
3.S.A.- 3.S.-.-
3.S.A.0001 3.S.A.-
3.S.A.0003 3.S.A.-
3.S.A.0005 3.S.A.-
3.S.A.0102 3.S.A.-
3.S.A.0110 3.S.A.-
i dunno, here's my attempt:
Code Snippet
SET NOCOUNT ON
DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20),
[Description] varchar(100)
)
INSERT INTO @.MyTable VALUES ( '3.-.-.-', 'Balance S' )
INSERT INTO @.MyTable VALUES ( '3.S.-.-', 'Balance S.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '3.S.A.-', 'Balance S.Balance Sheet.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area1' )
INSERT INTO @.MyTable VALUES ( '3.S.A.5001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area2' )
INSERT INTO @.MyTable VALUES ( '3.S.A.5002', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area3' )
SELECT
CASE
WHEN CHARINDEX('-', GLCode) = 0 THEN LTRIM(RTRIM(LEFT(GLCode, LEN(GLCode) - CHARINDEX('.', REVERSE(GLCode)))))
WHEN CHARINDEX('-', GLCODE) = 3 THEN '-.-.-.-'
ELSE LTRIM(RTRIM(LEFT(GLCode, CHARINDEX('.-', GLCode))))
END
FROM @.MyTable
edit: the above isn't correct, but perhaps it'll offer some help...|||Thanks guys - I can see that I'll be able to pick apart what you've done and come up with a solution.
Subscribe to:
Posts (Atom)