Deadlock Issue.

Greetings All, here is the ddl to create my test:
create table Parent
(
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
RIAmt decimal(28,10),
CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
)
go
create table Child
(
CPK1 decimal(10) not null,
CPK2 decimal(9) not null,
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
)
go
ALTER TABLE Child ADD CONSTRAINT FK
FOREIGN KEY (PPK1, PPK2)
REFERENCES Parent(PPK1, PPK2)
go
Next I open two different SQLCMD Windows: cmd1 and cmd 2
cmd1: begin tran;
go
insert into parent values (1, 999999999);
go
cmd2: begin tran;
go
insert into parent values (2, 999999999);
go
insert into child values (1, 999999999, 2, 999999999);
go
cmd1: insert into child values (2, 999999999, 1, 999999999);
go
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
WAIT CONDITION IS GENERATED
cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
go
DEADLOCK OCCURS
I am curious why this deadlock occurs when each thread is only
accessing data created in its own thread? I am thinking that a table
scan is taking place on the child table when I do the select and it is
bumping into a locked record?
Any and all help would be greatly appreciated.
Regards, TFD.> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
Your theory is correct. Since there is no index on PPK1 and PPK2, the
SELECT select statements must scan all data and become blocked when
uncommitted data are encountered.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> Greetings All, here is the ddl to create my test:
> create table Parent
> (
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> RIAmt decimal(28,10),
> CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> )
> go
> create table Child
> (
> CPK1 decimal(10) not null,
> CPK2 decimal(9) not null,
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> )
> go
> ALTER TABLE Child ADD CONSTRAINT FK
> FOREIGN KEY (PPK1, PPK2)
> REFERENCES Parent(PPK1, PPK2)
> go
>
> Next I open two different SQLCMD Windows: cmd1 and cmd 2
> cmd1: begin tran;
> go
> insert into parent values (1, 999999999);
> go
> cmd2: begin tran;
> go
> insert into parent values (2, 999999999);
> go
> insert into child values (1, 999999999, 2, 999999999);
> go
> cmd1: insert into child values (2, 999999999, 1, 999999999);
> go
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> WAIT CONDITION IS GENERATED
> cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> go
> DEADLOCK OCCURS
> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
> Any and all help would be greatly appreciated.
> Regards, TFD.
>|||Dan, let me ask you a broad question that may not have a direct answer
but hopefully some best practice might be applicable. This issue I
demonstrated here is happening in an application developed by my
company. It is a mult-threaded parallel processing application that is
required to have high throughput and will be performing complex
calculations. One way I can prevent the issue I brought up her is to
have ADO start the transaction in "snapshot" mode. This will avoid the
deadlock issue but I am worried about tempdb peformance? An
alternative is to go throught he physical data model and ensure that
all FK's have the appropriate indexes so that the scenario here (which
can happen in many places in the application) will not occur.
What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
Regards, TFD.
Dan Guzman wrote:[vbcol=seagreen]
> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> SELECT select statements must scan all data and become blocked when
> uncommitted data are encountered.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...|||> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
should not be used as a general cure for blocking. The SQL Server 2005
Books Online does a pretty good job of discussing the pros and cons of the
various row versioning levels
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d1
81b640c9b.htm).
However, I want to add that performance and concurrency go hand-in-hand.
Blocking is often a symptom of an underlying performance issue as
illustrated by you example. Sure, you might be able to improve concurrency
by using SNAPSHOT ISOLATION but that's not the right approach unless you
know the root cause and ramifications. If you simply change the isolation
level rather than perform index/query tuning, you'll find the app doesn't
scale. CPU and disk i/o will be consumed in direct proportion to table size
snapshot isolation overhead only compounds the issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> Dan, let me ask you a broad question that may not have a direct answer
> but hopefully some best practice might be applicable. This issue I
> demonstrated here is happening in an application developed by my
> company. It is a mult-threaded parallel processing application that is
> required to have high throughput and will be performing complex
> calculations. One way I can prevent the issue I brought up her is to
> have ADO start the transaction in "snapshot" mode. This will avoid the
> deadlock issue but I am worried about tempdb peformance? An
> alternative is to go throught he physical data model and ensure that
> all FK's have the appropriate indexes so that the scenario here (which
> can happen in many places in the application) will not occur.
> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> Regards, TFD.
>
> Dan Guzman wrote:
>|||Dan, perhaps you can entertain one more question for me seeing that you
know what is going on
The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:[vbcol=seagreen]
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6
d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrenc
y
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table si
ze
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:[vbcol=seagreen]
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on
> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegroups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>

Deadlock Issue.

Greetings All, here is the ddl to create my test:
create table Parent
(
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
RIAmt decimal(28,10),
CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
)
go
create table Child
(
CPK1 decimal(10) not null,
CPK2 decimal(9) not null,
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
)
go
ALTER TABLE Child ADD CONSTRAINT FK
FOREIGN KEY (PPK1, PPK2)
REFERENCES Parent(PPK1, PPK2)
go
Next I open two different SQLCMD Windows: cmd1 and cmd 2
cmd1: begin tran;
go
insert into parent values (1, 999999999);
go
cmd2: begin tran;
go
insert into parent values (2, 999999999);
go
insert into child values (1, 999999999, 2, 999999999);
go
cmd1: insert into child values (2, 999999999, 1, 999999999);
go
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
WAIT CONDITION IS GENERATED
cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
go
DEADLOCK OCCURS
I am curious why this deadlock occurs when each thread is only
accessing data created in its own thread? I am thinking that a table
scan is taking place on the child table when I do the select and it is
bumping into a locked record?
Any and all help would be greatly appreciated.
Regards, TFD.
> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
Your theory is correct. Since there is no index on PPK1 and PPK2, the
SELECT select statements must scan all data and become blocked when
uncommitted data are encountered.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162506125.569245.65950@.b28g2000cwb.googlegro ups.com...
> Greetings All, here is the ddl to create my test:
> create table Parent
> (
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> RIAmt decimal(28,10),
> CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> )
> go
> create table Child
> (
> CPK1 decimal(10) not null,
> CPK2 decimal(9) not null,
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> )
> go
> ALTER TABLE Child ADD CONSTRAINT FK
> FOREIGN KEY (PPK1, PPK2)
> REFERENCES Parent(PPK1, PPK2)
> go
>
> Next I open two different SQLCMD Windows: cmd1 and cmd 2
> cmd1: begin tran;
> go
> insert into parent values (1, 999999999);
> go
> cmd2: begin tran;
> go
> insert into parent values (2, 999999999);
> go
> insert into child values (1, 999999999, 2, 999999999);
> go
> cmd1: insert into child values (2, 999999999, 1, 999999999);
> go
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> WAIT CONDITION IS GENERATED
> cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> go
> DEADLOCK OCCURS
> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
> Any and all help would be greatly appreciated.
> Regards, TFD.
>
|||Dan, let me ask you a broad question that may not have a direct answer
but hopefully some best practice might be applicable. This issue I
demonstrated here is happening in an application developed by my
company. It is a mult-threaded parallel processing application that is
required to have high throughput and will be performing complex
calculations. One way I can prevent the issue I brought up her is to
have ADO start the transaction in "snapshot" mode. This will avoid the
deadlock issue but I am worried about tempdb peformance? An
alternative is to go throught he physical data model and ensure that
all FK's have the appropriate indexes so that the scenario here (which
can happen in many places in the application) will not occur.
What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
Regards, TFD.
Dan Guzman wrote:[vbcol=seagreen]
> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> SELECT select statements must scan all data and become blocked when
> uncommitted data are encountered.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162506125.569245.65950@.b28g2000cwb.googlegro ups.com...
|||> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
should not be used as a general cure for blocking. The SQL Server 2005
Books Online does a pretty good job of discussing the pros and cons of the
various row versioning levels
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
However, I want to add that performance and concurrency go hand-in-hand.
Blocking is often a symptom of an underlying performance issue as
illustrated by you example. Sure, you might be able to improve concurrency
by using SNAPSHOT ISOLATION but that's not the right approach unless you
know the root cause and ramifications. If you simply change the isolation
level rather than perform index/query tuning, you'll find the app doesn't
scale. CPU and disk i/o will be consumed in direct proportion to table size
snapshot isolation overhead only compounds the issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162524560.033768.252890@.f16g2000cwb.googlegr oups.com...
> Dan, let me ask you a broad question that may not have a direct answer
> but hopefully some best practice might be applicable. This issue I
> demonstrated here is happening in an application developed by my
> company. It is a mult-threaded parallel processing application that is
> required to have high throughput and will be performing complex
> calculations. One way I can prevent the issue I brought up her is to
> have ADO start the transaction in "snapshot" mode. This will avoid the
> deadlock issue but I am worried about tempdb peformance? An
> alternative is to go throught he physical data model and ensure that
> all FK's have the appropriate indexes so that the scenario here (which
> can happen in many places in the application) will not occur.
> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> Regards, TFD.
>
> Dan Guzman wrote:
>
|||Dan, perhaps you can entertain one more question for me seeing that you
know what is going on
The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:[vbcol=seagreen]
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrency
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table size
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegr oups.com...
|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:[vbcol=seagreen]
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on
> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:
|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegro ups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>

Deadlock Issue.

Greetings All, here is the ddl to create my test:
create table Parent
(
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
RIAmt decimal(28,10),
CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
)
go
create table Child
(
CPK1 decimal(10) not null,
CPK2 decimal(9) not null,
PPK1 decimal(10) not null,
PPK2 decimal(9) not null,
CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
)
go
ALTER TABLE Child ADD CONSTRAINT FK
FOREIGN KEY (PPK1, PPK2)
REFERENCES Parent(PPK1, PPK2)
go
Next I open two different SQLCMD Windows: cmd1 and cmd 2
cmd1: begin tran;
go
insert into parent values (1, 999999999);
go
cmd2: begin tran;
go
insert into parent values (2, 999999999);
go
insert into child values (1, 999999999, 2, 999999999);
go
cmd1: insert into child values (2, 999999999, 1, 999999999);
go
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
WAIT CONDITION IS GENERATED
cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
go
DEADLOCK OCCURS
I am curious why this deadlock occurs when each thread is only
accessing data created in its own thread? I am thinking that a table
scan is taking place on the child table when I do the select and it is
bumping into a locked record?
Any and all help would be greatly appreciated.
Regards, TFD.> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
Your theory is correct. Since there is no index on PPK1 and PPK2, the
SELECT select statements must scan all data and become blocked when
uncommitted data are encountered.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> Greetings All, here is the ddl to create my test:
> create table Parent
> (
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> RIAmt decimal(28,10),
> CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> )
> go
> create table Child
> (
> CPK1 decimal(10) not null,
> CPK2 decimal(9) not null,
> PPK1 decimal(10) not null,
> PPK2 decimal(9) not null,
> CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> )
> go
> ALTER TABLE Child ADD CONSTRAINT FK
> FOREIGN KEY (PPK1, PPK2)
> REFERENCES Parent(PPK1, PPK2)
> go
>
> Next I open two different SQLCMD Windows: cmd1 and cmd 2
> cmd1: begin tran;
> go
> insert into parent values (1, 999999999);
> go
> cmd2: begin tran;
> go
> insert into parent values (2, 999999999);
> go
> insert into child values (1, 999999999, 2, 999999999);
> go
> cmd1: insert into child values (2, 999999999, 1, 999999999);
> go
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> WAIT CONDITION IS GENERATED
> cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> go
> DEADLOCK OCCURS
> I am curious why this deadlock occurs when each thread is only
> accessing data created in its own thread? I am thinking that a table
> scan is taking place on the child table when I do the select and it is
> bumping into a locked record?
> Any and all help would be greatly appreciated.
> Regards, TFD.
>|||Dan, let me ask you a broad question that may not have a direct answer
but hopefully some best practice might be applicable. This issue I
demonstrated here is happening in an application developed by my
company. It is a mult-threaded parallel processing application that is
required to have high throughput and will be performing complex
calculations. One way I can prevent the issue I brought up her is to
have ADO start the transaction in "snapshot" mode. This will avoid the
deadlock issue but I am worried about tempdb peformance? An
alternative is to go throught he physical data model and ensure that
all FK's have the appropriate indexes so that the scenario here (which
can happen in many places in the application) will not occur.
What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
Regards, TFD.
Dan Guzman wrote:
> > I am curious why this deadlock occurs when each thread is only
> > accessing data created in its own thread? I am thinking that a table
> > scan is taking place on the child table when I do the select and it is
> > bumping into a locked record?
> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> SELECT select statements must scan all data and become blocked when
> uncommitted data are encountered.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> > Greetings All, here is the ddl to create my test:
> >
> > create table Parent
> > (
> > PPK1 decimal(10) not null,
> > PPK2 decimal(9) not null,
> > RIAmt decimal(28,10),
> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> > )
> > go
> >
> > create table Child
> > (
> > CPK1 decimal(10) not null,
> > CPK2 decimal(9) not null,
> > PPK1 decimal(10) not null,
> > PPK2 decimal(9) not null,
> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> > )
> > go
> >
> > ALTER TABLE Child ADD CONSTRAINT FK
> > FOREIGN KEY (PPK1, PPK2)
> > REFERENCES Parent(PPK1, PPK2)
> > go
> >
> >
> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> >
> > cmd1: begin tran;
> > go
> > insert into parent values (1, 999999999);
> > go
> >
> > cmd2: begin tran;
> > go
> > insert into parent values (2, 999999999);
> > go
> > insert into child values (1, 999999999, 2, 999999999);
> > go
> >
> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> > go
> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> > go
> > WAIT CONDITION IS GENERATED
> >
> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> > go
> > DEADLOCK OCCURS
> >
> > I am curious why this deadlock occurs when each thread is only
> > accessing data created in its own thread? I am thinking that a table
> > scan is taking place on the child table when I do the select and it is
> > bumping into a locked record?
> >
> > Any and all help would be greatly appreciated.
> >
> > Regards, TFD.
> >|||> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
should not be used as a general cure for blocking. The SQL Server 2005
Books Online does a pretty good job of discussing the pros and cons of the
various row versioning levels
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
However, I want to add that performance and concurrency go hand-in-hand.
Blocking is often a symptom of an underlying performance issue as
illustrated by you example. Sure, you might be able to improve concurrency
by using SNAPSHOT ISOLATION but that's not the right approach unless you
know the root cause and ramifications. If you simply change the isolation
level rather than perform index/query tuning, you'll find the app doesn't
scale. CPU and disk i/o will be consumed in direct proportion to table size
snapshot isolation overhead only compounds the issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> Dan, let me ask you a broad question that may not have a direct answer
> but hopefully some best practice might be applicable. This issue I
> demonstrated here is happening in an application developed by my
> company. It is a mult-threaded parallel processing application that is
> required to have high throughput and will be performing complex
> calculations. One way I can prevent the issue I brought up her is to
> have ADO start the transaction in "snapshot" mode. This will avoid the
> deadlock issue but I am worried about tempdb peformance? An
> alternative is to go throught he physical data model and ensure that
> all FK's have the appropriate indexes so that the scenario here (which
> can happen in many places in the application) will not occur.
> What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> Regards, TFD.
>
> Dan Guzman wrote:
>> > I am curious why this deadlock occurs when each thread is only
>> > accessing data created in its own thread? I am thinking that a table
>> > scan is taking place on the child table when I do the select and it is
>> > bumping into a locked record?
>> Your theory is correct. Since there is no index on PPK1 and PPK2, the
>> SELECT select statements must scan all data and become blocked when
>> uncommitted data are encountered.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
>> > Greetings All, here is the ddl to create my test:
>> >
>> > create table Parent
>> > (
>> > PPK1 decimal(10) not null,
>> > PPK2 decimal(9) not null,
>> > RIAmt decimal(28,10),
>> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
>> > )
>> > go
>> >
>> > create table Child
>> > (
>> > CPK1 decimal(10) not null,
>> > CPK2 decimal(9) not null,
>> > PPK1 decimal(10) not null,
>> > PPK2 decimal(9) not null,
>> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
>> > )
>> > go
>> >
>> > ALTER TABLE Child ADD CONSTRAINT FK
>> > FOREIGN KEY (PPK1, PPK2)
>> > REFERENCES Parent(PPK1, PPK2)
>> > go
>> >
>> >
>> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
>> >
>> > cmd1: begin tran;
>> > go
>> > insert into parent values (1, 999999999);
>> > go
>> >
>> > cmd2: begin tran;
>> > go
>> > insert into parent values (2, 999999999);
>> > go
>> > insert into child values (1, 999999999, 2, 999999999);
>> > go
>> >
>> > cmd1: insert into child values (2, 999999999, 1, 999999999);
>> > go
>> > select * from child where ppk1 = 2 and ppk2 = 999999999;
>> > go
>> > WAIT CONDITION IS GENERATED
>> >
>> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
>> > go
>> > DEADLOCK OCCURS
>> >
>> > I am curious why this deadlock occurs when each thread is only
>> > accessing data created in its own thread? I am thinking that a table
>> > scan is taking place on the child table when I do the select and it is
>> > bumping into a locked record?
>> >
>> > Any and all help would be greatly appreciated.
>> >
>> > Regards, TFD.
>> >
>|||Dan, perhaps you can entertain one more question for me seeing that you
know what is going on :)
The scenario I described above is further complicated by the fact that
in my application the base table is accessed via view. When I create
the index on the FK's and then execute the SQL the scan goes away.
When I make the same call via a database view the index is not used and
I am once again doing a table scan and my deadlock rears its ugly head.
How do I force an index when selecting data through a view?
e.g.)
CREATE INDEX Parent_IDX1
ON Parent(PPK1,PPK2);
** This uses the index on PPK1 and PPK2
select * from child where ppk1 = 2 and ppk2 = 999999999;
go
** This does not use the index on PPK1 and PPK2
** The Optimizer comes back sayign it used the Primary Key of Child for
a Clustered Index seek.
CREATE VIEW MyView AS
SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
FROM Child
go
How do I force the Index Parent_IDX1 to get used? MY test only has a
few rows of data but in production this table will be heavily populated
and used.
Any and all help woudl be greatly appreciated.
TFD
Dan Guzman wrote:
> > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> should not be used as a general cure for blocking. The SQL Server 2005
> Books Online does a pretty good job of discussing the pros and cons of the
> various row versioning levels
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> However, I want to add that performance and concurrency go hand-in-hand.
> Blocking is often a symptom of an underlying performance issue as
> illustrated by you example. Sure, you might be able to improve concurrency
> by using SNAPSHOT ISOLATION but that's not the right approach unless you
> know the root cause and ramifications. If you simply change the isolation
> level rather than perform index/query tuning, you'll find the app doesn't
> scale. CPU and disk i/o will be consumed in direct proportion to table size
> snapshot isolation overhead only compounds the issue.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> > Dan, let me ask you a broad question that may not have a direct answer
> > but hopefully some best practice might be applicable. This issue I
> > demonstrated here is happening in an application developed by my
> > company. It is a mult-threaded parallel processing application that is
> > required to have high throughput and will be performing complex
> > calculations. One way I can prevent the issue I brought up her is to
> > have ADO start the transaction in "snapshot" mode. This will avoid the
> > deadlock issue but I am worried about tempdb peformance? An
> > alternative is to go throught he physical data model and ensure that
> > all FK's have the appropriate indexes so that the scenario here (which
> > can happen in many places in the application) will not occur.
> >
> > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> >
> > Regards, TFD.
> >
> >
> > Dan Guzman wrote:
> >> > I am curious why this deadlock occurs when each thread is only
> >> > accessing data created in its own thread? I am thinking that a table
> >> > scan is taking place on the child table when I do the select and it is
> >> > bumping into a locked record?
> >>
> >> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> >> SELECT select statements must scan all data and become blocked when
> >> uncommitted data are encountered.
> >>
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> >> > Greetings All, here is the ddl to create my test:
> >> >
> >> > create table Parent
> >> > (
> >> > PPK1 decimal(10) not null,
> >> > PPK2 decimal(9) not null,
> >> > RIAmt decimal(28,10),
> >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> >> > )
> >> > go
> >> >
> >> > create table Child
> >> > (
> >> > CPK1 decimal(10) not null,
> >> > CPK2 decimal(9) not null,
> >> > PPK1 decimal(10) not null,
> >> > PPK2 decimal(9) not null,
> >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> >> > )
> >> > go
> >> >
> >> > ALTER TABLE Child ADD CONSTRAINT FK
> >> > FOREIGN KEY (PPK1, PPK2)
> >> > REFERENCES Parent(PPK1, PPK2)
> >> > go
> >> >
> >> >
> >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> >> >
> >> > cmd1: begin tran;
> >> > go
> >> > insert into parent values (1, 999999999);
> >> > go
> >> >
> >> > cmd2: begin tran;
> >> > go
> >> > insert into parent values (2, 999999999);
> >> > go
> >> > insert into child values (1, 999999999, 2, 999999999);
> >> > go
> >> >
> >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> >> > go
> >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> >> > go
> >> > WAIT CONDITION IS GENERATED
> >> >
> >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> >> > go
> >> > DEADLOCK OCCURS
> >> >
> >> > I am curious why this deadlock occurs when each thread is only
> >> > accessing data created in its own thread? I am thinking that a table
> >> > scan is taking place on the child table when I do the select and it is
> >> > bumping into a locked record?
> >> >
> >> > Any and all help would be greatly appreciated.
> >> >
> >> > Regards, TFD.
> >> >
> >|||I found a solution to this problem. I just need to create a clustered
index on PPK1,PPK2 and that will ensure that a clustered index seek
takes place. Problem solved.
TFD.
LineVoltageHalogen wrote:
> Dan, perhaps you can entertain one more question for me seeing that you
> know what is going on :)
> The scenario I described above is further complicated by the fact that
> in my application the base table is accessed via view. When I create
> the index on the FK's and then execute the SQL the scan goes away.
> When I make the same call via a database view the index is not used and
> I am once again doing a table scan and my deadlock rears its ugly head.
> How do I force an index when selecting data through a view?
>
> e.g.)
> CREATE INDEX Parent_IDX1
> ON Parent(PPK1,PPK2);
> ** This uses the index on PPK1 and PPK2
> select * from child where ppk1 = 2 and ppk2 = 999999999;
> go
> ** This does not use the index on PPK1 and PPK2
> ** The Optimizer comes back sayign it used the Primary Key of Child for
> a Clustered Index seek.
> CREATE VIEW MyView AS
> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
> FROM Child
> go
>
> How do I force the Index Parent_IDX1 to get used? MY test only has a
> few rows of data but in production this table will be heavily populated
> and used.
> Any and all help woudl be greatly appreciated.
> TFD
>
>
> Dan Guzman wrote:
> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> >
> > I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
> > should not be used as a general cure for blocking. The SQL Server 2005
> > Books Online does a pretty good job of discussing the pros and cons of the
> > various row versioning levels
> > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
> >
> > However, I want to add that performance and concurrency go hand-in-hand.
> > Blocking is often a symptom of an underlying performance issue as
> > illustrated by you example. Sure, you might be able to improve concurrency
> > by using SNAPSHOT ISOLATION but that's not the right approach unless you
> > know the root cause and ramifications. If you simply change the isolation
> > level rather than perform index/query tuning, you'll find the app doesn't
> > scale. CPU and disk i/o will be consumed in direct proportion to table size
> > snapshot isolation overhead only compounds the issue.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> > news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
> > > Dan, let me ask you a broad question that may not have a direct answer
> > > but hopefully some best practice might be applicable. This issue I
> > > demonstrated here is happening in an application developed by my
> > > company. It is a mult-threaded parallel processing application that is
> > > required to have high throughput and will be performing complex
> > > calculations. One way I can prevent the issue I brought up her is to
> > > have ADO start the transaction in "snapshot" mode. This will avoid the
> > > deadlock issue but I am worried about tempdb peformance? An
> > > alternative is to go throught he physical data model and ensure that
> > > all FK's have the appropriate indexes so that the scenario here (which
> > > can happen in many places in the application) will not occur.
> > >
> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
> > >
> > > Regards, TFD.
> > >
> > >
> > > Dan Guzman wrote:
> > >> > I am curious why this deadlock occurs when each thread is only
> > >> > accessing data created in its own thread? I am thinking that a table
> > >> > scan is taking place on the child table when I do the select and it is
> > >> > bumping into a locked record?
> > >>
> > >> Your theory is correct. Since there is no index on PPK1 and PPK2, the
> > >> SELECT select statements must scan all data and become blocked when
> > >> uncommitted data are encountered.
> > >>
> > >>
> > >> --
> > >> Hope this helps.
> > >>
> > >> Dan Guzman
> > >> SQL Server MVP
> > >>
> > >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
> > >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
> > >> > Greetings All, here is the ddl to create my test:
> > >> >
> > >> > create table Parent
> > >> > (
> > >> > PPK1 decimal(10) not null,
> > >> > PPK2 decimal(9) not null,
> > >> > RIAmt decimal(28,10),
> > >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
> > >> > )
> > >> > go
> > >> >
> > >> > create table Child
> > >> > (
> > >> > CPK1 decimal(10) not null,
> > >> > CPK2 decimal(9) not null,
> > >> > PPK1 decimal(10) not null,
> > >> > PPK2 decimal(9) not null,
> > >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
> > >> > )
> > >> > go
> > >> >
> > >> > ALTER TABLE Child ADD CONSTRAINT FK
> > >> > FOREIGN KEY (PPK1, PPK2)
> > >> > REFERENCES Parent(PPK1, PPK2)
> > >> > go
> > >> >
> > >> >
> > >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
> > >> >
> > >> > cmd1: begin tran;
> > >> > go
> > >> > insert into parent values (1, 999999999);
> > >> > go
> > >> >
> > >> > cmd2: begin tran;
> > >> > go
> > >> > insert into parent values (2, 999999999);
> > >> > go
> > >> > insert into child values (1, 999999999, 2, 999999999);
> > >> > go
> > >> >
> > >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
> > >> > go
> > >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
> > >> > go
> > >> > WAIT CONDITION IS GENERATED
> > >> >
> > >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
> > >> > go
> > >> > DEADLOCK OCCURS
> > >> >
> > >> > I am curious why this deadlock occurs when each thread is only
> > >> > accessing data created in its own thread? I am thinking that a table
> > >> > scan is taking place on the child table when I do the select and it is
> > >> > bumping into a locked record?
> > >> >
> > >> > Any and all help would be greatly appreciated.
> > >> >
> > >> > Regards, TFD.
> > >> >
> > >|||I'm glad to see you were able to work things out. Generally speaking, every
table should have a clustered index and columns used on joins and range
searches are often good candidates. The Database Engine Tuning Advisor
usually does a decent job of making recommendations so you might consider
providing the tool a representative workload to see of it makes additional
recommendations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1162615001.690202.22950@.k70g2000cwa.googlegroups.com...
>I found a solution to this problem. I just need to create a clustered
> index on PPK1,PPK2 and that will ensure that a clustered index seek
> takes place. Problem solved.
> TFD.
>
> LineVoltageHalogen wrote:
>> Dan, perhaps you can entertain one more question for me seeing that you
>> know what is going on :)
>> The scenario I described above is further complicated by the fact that
>> in my application the base table is accessed via view. When I create
>> the index on the FK's and then execute the SQL the scan goes away.
>> When I make the same call via a database view the index is not used and
>> I am once again doing a table scan and my deadlock rears its ugly head.
>> How do I force an index when selecting data through a view?
>>
>> e.g.)
>> CREATE INDEX Parent_IDX1
>> ON Parent(PPK1,PPK2);
>> ** This uses the index on PPK1 and PPK2
>> select * from child where ppk1 = 2 and ppk2 = 999999999;
>> go
>> ** This does not use the index on PPK1 and PPK2
>> ** The Optimizer comes back sayign it used the Primary Key of Child for
>> a Clustered Index seek.
>> CREATE VIEW MyView AS
>> SELECT Child.CPK1, Child.CPK2, Child.PPK1, Child.PPK2
>> FROM Child
>> go
>>
>> How do I force the Index Parent_IDX1 to get used? MY test only has a
>> few rows of data but in production this table will be heavily populated
>> and used.
>> Any and all help woudl be greatly appreciated.
>> TFD
>>
>>
>> Dan Guzman wrote:
>> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
>> >
>> > I think SNAPSHOT ISOLATION is a good tool to have in one's arsenal but
>> > should not be used as a general cure for blocking. The SQL Server 2005
>> > Books Online does a pretty good job of discussing the pros and cons of
>> > the
>> > various row versioning levels
>> > (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/1d7972a0-5f52-4ae4-b1da-6d181b640c9b.htm).
>> >
>> > However, I want to add that performance and concurrency go
>> > hand-in-hand.
>> > Blocking is often a symptom of an underlying performance issue as
>> > illustrated by you example. Sure, you might be able to improve
>> > concurrency
>> > by using SNAPSHOT ISOLATION but that's not the right approach unless
>> > you
>> > know the root cause and ramifications. If you simply change the
>> > isolation
>> > level rather than perform index/query tuning, you'll find the app
>> > doesn't
>> > scale. CPU and disk i/o will be consumed in direct proportion to table
>> > size
>> > snapshot isolation overhead only compounds the issue.
>> >
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> > news:1162524560.033768.252890@.f16g2000cwb.googlegroups.com...
>> > > Dan, let me ask you a broad question that may not have a direct
>> > > answer
>> > > but hopefully some best practice might be applicable. This issue I
>> > > demonstrated here is happening in an application developed by my
>> > > company. It is a mult-threaded parallel processing application that
>> > > is
>> > > required to have high throughput and will be performing complex
>> > > calculations. One way I can prevent the issue I brought up her is to
>> > > have ADO start the transaction in "snapshot" mode. This will avoid
>> > > the
>> > > deadlock issue but I am worried about tempdb peformance? An
>> > > alternative is to go throught he physical data model and ensure that
>> > > all FK's have the appropriate indexes so that the scenario here
>> > > (which
>> > > can happen in many places in the application) will not occur.
>> > >
>> > > What are your thoughts on SQL 2005's SNAPSHOT ISOLATION.
>> > >
>> > > Regards, TFD.
>> > >
>> > >
>> > > Dan Guzman wrote:
>> > >> > I am curious why this deadlock occurs when each thread is only
>> > >> > accessing data created in its own thread? I am thinking that a
>> > >> > table
>> > >> > scan is taking place on the child table when I do the select and
>> > >> > it is
>> > >> > bumping into a locked record?
>> > >>
>> > >> Your theory is correct. Since there is no index on PPK1 and PPK2,
>> > >> the
>> > >> SELECT select statements must scan all data and become blocked when
>> > >> uncommitted data are encountered.
>> > >>
>> > >>
>> > >> --
>> > >> Hope this helps.
>> > >>
>> > >> Dan Guzman
>> > >> SQL Server MVP
>> > >>
>> > >> "LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
>> > >> news:1162506125.569245.65950@.b28g2000cwb.googlegroups.com...
>> > >> > Greetings All, here is the ddl to create my test:
>> > >> >
>> > >> > create table Parent
>> > >> > (
>> > >> > PPK1 decimal(10) not null,
>> > >> > PPK2 decimal(9) not null,
>> > >> > RIAmt decimal(28,10),
>> > >> > CONSTRAINT RII_PK PRIMARY KEY CLUSTERED (PPK1, PPK2)
>> > >> > )
>> > >> > go
>> > >> >
>> > >> > create table Child
>> > >> > (
>> > >> > CPK1 decimal(10) not null,
>> > >> > CPK2 decimal(9) not null,
>> > >> > PPK1 decimal(10) not null,
>> > >> > PPK2 decimal(9) not null,
>> > >> > CONSTRAINT RBI_PK PRIMARY KEY CLUSTERED (CPK1, CPK2)
>> > >> > )
>> > >> > go
>> > >> >
>> > >> > ALTER TABLE Child ADD CONSTRAINT FK
>> > >> > FOREIGN KEY (PPK1, PPK2)
>> > >> > REFERENCES Parent(PPK1, PPK2)
>> > >> > go
>> > >> >
>> > >> >
>> > >> > Next I open two different SQLCMD Windows: cmd1 and cmd 2
>> > >> >
>> > >> > cmd1: begin tran;
>> > >> > go
>> > >> > insert into parent values (1, 999999999);
>> > >> > go
>> > >> >
>> > >> > cmd2: begin tran;
>> > >> > go
>> > >> > insert into parent values (2, 999999999);
>> > >> > go
>> > >> > insert into child values (1, 999999999, 2, 999999999);
>> > >> > go
>> > >> >
>> > >> > cmd1: insert into child values (2, 999999999, 1, 999999999);
>> > >> > go
>> > >> > select * from child where ppk1 = 2 and ppk2 = 999999999;
>> > >> > go
>> > >> > WAIT CONDITION IS GENERATED
>> > >> >
>> > >> > cmd2: select * from child where ppk1 = 1 and ppk2 = 999999999;
>> > >> > go
>> > >> > DEADLOCK OCCURS
>> > >> >
>> > >> > I am curious why this deadlock occurs when each thread is only
>> > >> > accessing data created in its own thread? I am thinking that a
>> > >> > table
>> > >> > scan is taking place on the child table when I do the select and
>> > >> > it is
>> > >> > bumping into a locked record?
>> > >> >
>> > >> > Any and all help would be greatly appreciated.
>> > >> >
>> > >> > Regards, TFD.
>> > >> >
>> > >
>

deadlock alert

sql2k sp3
I want to have an alert email me whenever a deadlock occurs. SQLMail is
working properly. I create an alert and specify Error 1205. Then I specify
myself in the email section. Then I purposely cause a deadlock but never get
emailed. In addition to this, the deadlock never goes in the TLog either. An
y
ideas?
TIA, ChrisRDid you select "Always write to Windows Eventlog" for error 1205?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> sql2k sp3
> I want to have an alert email me whenever a deadlock occurs. SQLMail is
> working properly. I create an alert and specify Error 1205. Then I specify
> myself in the email section. Then I purposely cause a deadlock but never g
et
> emailed. In addition to this, the deadlock never goes in the TLog either.
Any
> ideas?
>
> TIA, ChrisR|||I don't see that as an option anywhere. Will this help my email/ TLog proble
m?
"Tibor Karaszi" wrote:

> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
>
>|||Where would I check this at?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
specify[vbcol=seagreen]
get[vbcol=seagreen]
either. Any[vbcol=seagreen]
>|||EM, right-click the server, Manage SQL Server Messages, search your message
in this dialog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <chris@.noemail.com> wrote in message news:%230t$w4MsEHA.3320@.TK2MSFTNGP15.phx.gbl..
.
> Where would I check this at?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
> specify
> get
> either. Any
>

deadlock alert

sql2k sp3
I want to have an alert email me whenever a deadlock occurs. SQLMail is
working properly. I create an alert and specify Error 1205. Then I specify
myself in the email section. Then I purposely cause a deadlock but never get
emailed. In addition to this, the deadlock never goes in the TLog either. Any
ideas?
TIA, ChrisRDid you select "Always write to Windows Eventlog" for error 1205?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> sql2k sp3
> I want to have an alert email me whenever a deadlock occurs. SQLMail is
> working properly. I create an alert and specify Error 1205. Then I specify
> myself in the email section. Then I purposely cause a deadlock but never get
> emailed. In addition to this, the deadlock never goes in the TLog either. Any
> ideas?
>
> TIA, ChrisR|||I don't see that as an option anywhere. Will this help my email/ TLog problem?
"Tibor Karaszi" wrote:
> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> > sql2k sp3
> >
> > I want to have an alert email me whenever a deadlock occurs. SQLMail is
> > working properly. I create an alert and specify Error 1205. Then I specify
> > myself in the email section. Then I purposely cause a deadlock but never get
> > emailed. In addition to this, the deadlock never goes in the TLog either. Any
> > ideas?
> >
> >
> > TIA, ChrisR
>
>|||Where would I check this at?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> > sql2k sp3
> >
> > I want to have an alert email me whenever a deadlock occurs. SQLMail is
> > working properly. I create an alert and specify Error 1205. Then I
specify
> > myself in the email section. Then I purposely cause a deadlock but never
get
> > emailed. In addition to this, the deadlock never goes in the TLog
either. Any
> > ideas?
> >
> >
> > TIA, ChrisR
>|||EM, right-click the server, Manage SQL Server Messages, search your message in this dialog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <chris@.noemail.com> wrote in message news:%230t$w4MsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> Where would I check this at?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
>> Did you select "Always write to Windows Eventlog" for error 1205?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
>> > sql2k sp3
>> >
>> > I want to have an alert email me whenever a deadlock occurs. SQLMail is
>> > working properly. I create an alert and specify Error 1205. Then I
> specify
>> > myself in the email section. Then I purposely cause a deadlock but never
> get
>> > emailed. In addition to this, the deadlock never goes in the TLog
> either. Any
>> > ideas?
>> >
>> >
>> > TIA, ChrisR
>>
>

deadlock alert

sql2k sp3
I want to have an alert email me whenever a deadlock occurs. SQLMail is
working properly. I create an alert and specify Error 1205. Then I specify
myself in the email section. Then I purposely cause a deadlock but never get
emailed. In addition to this, the deadlock never goes in the TLog either. Any
ideas?
TIA, ChrisR
Did you select "Always write to Windows Eventlog" for error 1205?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
> sql2k sp3
> I want to have an alert email me whenever a deadlock occurs. SQLMail is
> working properly. I create an alert and specify Error 1205. Then I specify
> myself in the email section. Then I purposely cause a deadlock but never get
> emailed. In addition to this, the deadlock never goes in the TLog either. Any
> ideas?
>
> TIA, ChrisR
|||I don't see that as an option anywhere. Will this help my email/ TLog problem?
"Tibor Karaszi" wrote:

> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
>
>
|||Where would I check this at?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Did you select "Always write to Windows Eventlog" for error 1205?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:01EDCE91-12E5-4DD6-9C13-C5CDA5BE3D20@.microsoft.com...
specify[vbcol=seagreen]
get[vbcol=seagreen]
either. Any
>
|||EM, right-click the server, Manage SQL Server Messages, search your message in this dialog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <chris@.noemail.com> wrote in message news:%230t$w4MsEHA.3320@.TK2MSFTNGP15.phx.gbl...
> Where would I check this at?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:udq$65JsEHA.2212@.TK2MSFTNGP14.phx.gbl...
> specify
> get
> either. Any
>

DDL triggers on create Logins

Hi all,
I have setup a DDL trigger for server logins. If I drop a user my trigger fires off an email with the EVENTDATA().value. If I create a new user the Eventdata is null. I am using the code below:
TIA,
Joe

DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
declare @.user varchar(100),@.event varchar(1000), @.subj varchar(100)
select @.user = SUSER_SNAME()
Select @.subj = 'Login Event Issued from '+@.user
select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
isnull((SELECT EVENTDATA().value'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')),' ')+' Please email the group with the SIR# for this action.'
EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'SQL2005mail',
@.recipients = 'j.f@.myco.com',
@.body = @.event,
@.subject = @.subj
;

TSQLCommand text is not available for CREATE LOGIN, so you won't be able to see the data. But you can always see other useful information through EVENTDATA.value(). For example

select @.event = @.user+' committed the following event on ENSQLD1_2005: '+
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') +
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') +
...

|||Thank you,
I was able to trap the new user name and the create login eventtype. Now I have the triggers emailing me on any drop of user or create of one.

Do you know what event gets fired off when I change the permissions of a user? I thought it would be Alter_login, but when I grant rights to a DB to a user I dont get an email.

Thanks again!
Joe|||

> Do you know what event gets fired off when I change the permissions of a

> user?

Well, did you try running profiler and inspect the commands sent?

DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT 'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_XML_SCHEMA_COLLECTION_EVENTS
-
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_TRIGGER_EVENTS
-
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_TYPE_EVENTS
-
91 CREATE_TYPE
93 DROP_TYPE

DDL_TABLE_EVENTS
-
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE

DDL_SYNONYM_EVENTS
-
34 CREATE_SYNONYM
36 DROP_SYNONYM

DDL_STATISTICS_EVENTS
--
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS

DDL_SERVICE_EVENTS

161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE

DDL_SCHEMA_EVENTS

141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA

DDL_ROUTE_EVENTS

164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE

DDL_ROLE_EVENTS
-
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE

DDL_REMOTE_SERVICE_BINDING_EVENTS
--
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING

DDL_QUEUE_EVENTS

157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE

DDL_PROCEDURE_EVENTS
-
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE

DDL_PARTITION_SCHEME_EVENTS

194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME

DDL_PARTITION_FUNCTION_EVENTS

191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION

DDL_EVENT_NOTIFICATION_EVENTS
-
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION

DDL_ASSEMBLY_EVENTS
--
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY

DDL_CONTRACT_EVENTS
--
154 CREATE_CONTRACT
156 DROP_CONTRACT

DDL_FUNCTION_EVENTS

61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION

DDL_INDEX_EVENTS

24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX

DDL_MESSAGE_TYPE_EVENTS

151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

DDL Triggers

I know that DDL_LOGIN_EVENTS is the same as CREATE LOGIN, ALTER LOGIN and DROP LOGIN combined but where is this documented?

I have some code here (http://sqlservercode.blogspot.com/2006/08/ddl-trigger-events-revisited.html) that basically shows that you can combine events

But where is this info in BOL?

For example if I do this:

create a trigger and I use DDL_VIEW_EVENTS

CREATE TRIGGER ddlTestEvents
ON DATABASE
FOR DDL_VIEW_EVENTS
AS
PRINT'You must disable Trigger "ddlTestEvents" to drop, create or alter Views!'
ROLLBACK;
GO

After that I would check the sys.triggers and sys.trigger_events views to see what was inserted

SELECT name,te.type,te.type_desc
FROMsys.triggers t
JOINsys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents')
ORDER BY te.type,te.type_desc

In this case 3 rows were inserted

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

So here is the complete list for who wants it

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_XML_SCHEMA_COLLECTION_EVENTS
-
177 CREATE_XML_SCHEMA_COLLECTION
178 ALTER_XML_SCHEMA_COLLECTION
179 DROP_XML_SCHEMA_COLLECTION

DDL_VIEW_EVENTS
-
41 CREATE_VIEW
42 ALTER_VIEW
43 DROP_VIEW

DDL_TRIGGER_EVENTS
-
71 CREATE_TRIGGER
72 ALTER_TRIGGER
73 DROP_TRIGGER

DDL_USER_EVENTS
-
131 CREATE_USER
132 ALTER_USER
133 DROP_USER

DDL_TYPE_EVENTS
-
91 CREATE_TYPE
93 DROP_TYPE

DDL_TABLE_EVENTS
-
21 CREATE_TABLE
22 ALTER_TABLE
23 DROP_TABLE

DDL_SYNONYM_EVENTS
-
34 CREATE_SYNONYM
36 DROP_SYNONYM

DDL_STATISTICS_EVENTS
--
27 CREATE_STATISTICS
28 UPDATE_STATISTICS
29 DROP_STATISTICS

DDL_SERVICE_EVENTS

161 CREATE_SERVICE
162 ALTER_SERVICE
163 DROP_SERVICE

DDL_SCHEMA_EVENTS

141 CREATE_SCHEMA
142 ALTER_SCHEMA
143 DROP_SCHEMA

DDL_ROUTE_EVENTS

164 CREATE_ROUTE
165 ALTER_ROUTE
166 DROP_ROUTE

DDL_ROLE_EVENTS
-
134 CREATE_ROLE
135 ALTER_ROLE
136 DROP_ROLE

DDL_REMOTE_SERVICE_BINDING_EVENTS
--
174 CREATE_REMOTE_SERVICE_BINDING
175 ALTER_REMOTE_SERVICE_BINDING
176 DROP_REMOTE_SERVICE_BINDING

DDL_QUEUE_EVENTS

157 CREATE_QUEUE
158 ALTER_QUEUE
159 DROP_QUEUE

DDL_PROCEDURE_EVENTS
-
51 CREATE_PROCEDURE
52 ALTER_PROCEDURE
53 DROP_PROCEDURE

DDL_PARTITION_SCHEME_EVENTS

194 CREATE_PARTITION_SCHEME
195 ALTER_PARTITION_SCHEME
196 DROP_PARTITION_SCHEME

DDL_PARTITION_FUNCTION_EVENTS

191 CREATE_PARTITION_FUNCTION
192 ALTER_PARTITION_FUNCTION
193 DROP_PARTITION_FUNCTION

DDL_EVENT_NOTIFICATION_EVENTS
-
74 CREATE_EVENT_NOTIFICATION
76 DROP_EVENT_NOTIFICATION

DDL_ASSEMBLY_EVENTS
--
101 CREATE_ASSEMBLY
102 ALTER_ASSEMBLY
103 DROP_ASSEMBLY

DDL_CONTRACT_EVENTS
--
154 CREATE_CONTRACT
156 DROP_CONTRACT

DDL_FUNCTION_EVENTS

61 CREATE_FUNCTION
62 ALTER_FUNCTION
63 DROP_FUNCTION

DDL_INDEX_EVENTS

24 CREATE_INDEX
25 ALTER_INDEX
26 DROP_INDEX
206 CREATE_XML_INDEX

DDL_MESSAGE_TYPE_EVENTS

151 CREATE_MESSAGE_TYPE
152 ALTER_MESSAGE_TYPE
153 DROP_MESSAGE_TYPE

Denis The SQL Menace

http://sqlservercode.blogspot.com

Hi Denis,

This information is documented in the Books Online topic "Event Groups for Use with DDL Triggers.

http://msdn2.microsoft.com/en-us/library/ms191441.aspx

Regards,

Gail

|||

Thank you, however I would prefer text over an image (So that I can work my copy and paste magic!!)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

DDL statement is not allowed

hey, i have a small bussines with share point and msde2000, when i try to create a table in the servername\sharepoint, i get this DDL statement is not allowed, can anyone help wiht that
thanks
The sharepoint instance is reserved for sharepoint use, you can not perform
DDL operations on it. You can install another instance of MSDE by
downloading it from the web.
--
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"eslalinde" <eslalinde@.discussions.microsoft.com> wrote in message
news:E5133112-0575-4A3D-AC3A-81A21F470194@.microsoft.com...
> hey, i have a small bussines with share point and msde2000, when i try to
create a table in the servername\sharepoint, i get this DDL statement is not
allowed, can anyone help wiht that
> thanks

DDL Server Trigger Error (SQL2005)

I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's coming
up with the following error:
-----
Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
-----
I've checked on the internet and have noted someone else having the same
problem, but with no resolution. Is this a bug? Has anyone a work-around?
Thanks.
Hi
Can you post DDL to be tested?
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> coming
> up with the following error:
> -----
> Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> registration.
> -----
> I've checked on the internet and have noted someone else having the same
> problem, but with no resolution. Is this a bug? Has anyone a work-around?
> Thanks.
>
>
|||Hi Uri,
Here's the code (some bits removed for security):
CREATE TRIGGER trAll_ddl_server
ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
AS
DECLARE @.eventData xml, @.eventID int
SET @.eventData = eventdata()
INSERT <AuditTbl> (AuditEventObject, PostTime,
spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
VALUES ('trAll_ddl_server',
cast(@.eventdata.query('data(//PostTime)') AS sysname),
cast(@.eventdata.query('data(//spid)') AS sysname),
cast(@.eventdata.query('data(//LoginName)') AS sysname),
cast(@.eventdata.query('data(//EventType)') AS sysname),
cast(@.eventdata.query('data(//ServerName)') AS sysname),
cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
cast(@.eventdata.query('data(//SchemaName)') AS sysname),
cast(@.eventdata.query('data(//ObjectName)') AS sysname),
cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
cast(@.eventdata.query('data(//ObjectType)') AS sysname),
cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
cast(@.eventdata.query('data(//EventData)') AS xml)
)
BEGIN
PRINT 'Permission denied. please contact the DBA.'
ROLLBACK
-- Set RolledBack flag
END
GO
/*
DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
DROP TRIGGER trAll_ddl_server ON ALL SERVER
*/
"Uri Dimant" wrote:

> Hi
> Can you post DDL to be tested?
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
>
>
|||Sorry, I've got the error
Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
Incorrect syntax near '<'.
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> Here's the code (some bits removed for security):
> CREATE TRIGGER trAll_ddl_server
> ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
> AS
> DECLARE @.eventData xml, @.eventID int
> SET @.eventData = eventdata()
> INSERT <AuditTbl> ( AuditEventObject, PostTime,
> spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
> ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
> VALUES ('trAll_ddl_server',
> cast(@.eventdata.query('data(//PostTime)') AS sysname),
> cast(@.eventdata.query('data(//spid)') AS sysname),
> cast(@.eventdata.query('data(//LoginName)') AS sysname),
> cast(@.eventdata.query('data(//EventType)') AS sysname),
> cast(@.eventdata.query('data(//ServerName)') AS sysname),
> cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
> cast(@.eventdata.query('data(//SchemaName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectName)') AS sysname),
> cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectType)') AS sysname),
> cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
> cast(@.eventdata.query('data(//EventData)') AS xml)
> )
> BEGIN
> PRINT 'Permission denied. please contact the DBA.'
> ROLLBACK
> -- Set RolledBack flag
> END
> GO
> /*
> DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
> DROP TRIGGER trAll_ddl_server ON ALL SERVER
> */
>
>
> "Uri Dimant" wrote:
|||Hi Uri,
I did say that some code had been taken out. Ok, here's some code that
should run as-is:
CREATE TRIGGER trig_All_ddl_database
ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
PRINT 'Database DDL Trigger Fired.'
GO
"Uri Dimant" wrote:

> Sorry, I've got the error
> Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
> Incorrect syntax near the keyword 'AS'.
> Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
> Incorrect syntax near '<'.
>
>
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...
>
>

DDL Server Trigger Error (SQL2005)

I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's comin
g
up with the following error:
----
--
Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
----
--
I've checked on the internet and have noted someone else having the same
problem, but with no resolution. Is this a bug? Has anyone a work-around?
Thanks.Hi
Can you post DDL to be tested?
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> coming
> up with the following error:
> ----
--
> Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> registration.
> ----
--
> I've checked on the internet and have noted someone else having the same
> problem, but with no resolution. Is this a bug? Has anyone a work-around?
> Thanks.
>
>|||Hi Uri,
Here's the code (some bits removed for security):
CREATE TRIGGER trAll_ddl_server
ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
AS
DECLARE @.eventData xml, @.eventID int
SET @.eventData = eventdata()
INSERT <AuditTbl> ( AuditEventObject, PostTime,
spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
VALUES ('trAll_ddl_server',
cast(@.eventdata.query('data(//PostTime)') AS sysname),
cast(@.eventdata.query('data(//spid)') AS sysname),
cast(@.eventdata.query('data(//LoginName)') AS sysname),
cast(@.eventdata.query('data(//EventType)') AS sysname),
cast(@.eventdata.query('data(//ServerName)') AS sysname),
cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
cast(@.eventdata.query('data(//SchemaName)') AS sysname),
cast(@.eventdata.query('data(//ObjectName)') AS sysname),
cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
cast(@.eventdata.query('data(//ObjectType)') AS sysname),
cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
cast(@.eventdata.query('data(//EventData)') AS xml)
)
BEGIN
PRINT 'Permission denied. please contact the DBA.'
ROLLBACK
-- Set RolledBack flag
END
GO
/*
DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
DROP TRIGGER trAll_ddl_server ON ALL SERVER
*/
"Uri Dimant" wrote:

> Hi
> Can you post DDL to be tested?
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
>
>|||Sorry, I've got the error
Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
Incorrect syntax near '<'.
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> Here's the code (some bits removed for security):
> CREATE TRIGGER trAll_ddl_server
> ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
> AS
> DECLARE @.eventData xml, @.eventID int
> SET @.eventData = eventdata()
> INSERT <AuditTbl> ( AuditEventObject, PostTime,
> spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
> ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
> VALUES ('trAll_ddl_server',
> cast(@.eventdata.query('data(//PostTime)') AS sysname),
> cast(@.eventdata.query('data(//spid)') AS sysname),
> cast(@.eventdata.query('data(//LoginName)') AS sysname),
> cast(@.eventdata.query('data(//EventType)') AS sysname),
> cast(@.eventdata.query('data(//ServerName)') AS sysname),
> cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
> cast(@.eventdata.query('data(//SchemaName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectName)') AS sysname),
> cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectType)') AS sysname),
> cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
> cast(@.eventdata.query('data(//EventData)') AS xml)
> )
> BEGIN
> PRINT 'Permission denied. please contact the DBA.'
> ROLLBACK
> -- Set RolledBack flag
> END
> GO
> /*
> DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
> DROP TRIGGER trAll_ddl_server ON ALL SERVER
> */
>
>
> "Uri Dimant" wrote:
>|||Hi Uri,
I did say that some code had been taken out. Ok, here's some code that
should run as-is:
CREATE TRIGGER trig_All_ddl_database
ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
PRINT 'Database DDL Trigger Fired.'
GO
"Uri Dimant" wrote:

> Sorry, I've got the error
> Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
> Incorrect syntax near the keyword 'AS'.
> Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
> Incorrect syntax near '<'.
>
>
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...
>
>

DDL Server Trigger Error (SQL2005)

I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's coming
up with the following error:
-----
Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.
-----
I've checked on the internet and have noted someone else having the same
problem, but with no resolution. Is this a bug? Has anyone a work-around?
Thanks.Hi
Can you post DDL to be tested?
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> coming
> up with the following error:
> -----
> Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> registration.
> -----
> I've checked on the internet and have noted someone else having the same
> problem, but with no resolution. Is this a bug? Has anyone a work-around?
> Thanks.
>
>|||Hi Uri,
Here's the code (some bits removed for security):
CREATE TRIGGER trAll_ddl_server
ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
AS
DECLARE @.eventData xml, @.eventID int
SET @.eventData = eventdata()
INSERT <AuditTbl> ( AuditEventObject, PostTime,
spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
VALUES ('trAll_ddl_server',
cast(@.eventdata.query('data(//PostTime)') AS sysname),
cast(@.eventdata.query('data(//spid)') AS sysname),
cast(@.eventdata.query('data(//LoginName)') AS sysname),
cast(@.eventdata.query('data(//EventType)') AS sysname),
cast(@.eventdata.query('data(//ServerName)') AS sysname),
cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
cast(@.eventdata.query('data(//SchemaName)') AS sysname),
cast(@.eventdata.query('data(//ObjectName)') AS sysname),
cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
cast(@.eventdata.query('data(//ObjectType)') AS sysname),
cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
cast(@.eventdata.query('data(//EventData)') AS xml)
)
BEGIN
PRINT 'Permission denied. please contact the DBA.'
ROLLBACK
-- Set RolledBack flag
END
GO
/*
DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
DROP TRIGGER trAll_ddl_server ON ALL SERVER
*/
"Uri Dimant" wrote:
> Hi
> Can you post DDL to be tested?
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> > I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> > coming
> > up with the following error:
> > -----
> > Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> > "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> > registration.
> > -----
> >
> > I've checked on the internet and have noted someone else having the same
> > problem, but with no resolution. Is this a bug? Has anyone a work-around?
> >
> > Thanks.
> >
> >
> >
>
>|||Sorry, I've got the error
Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
Incorrect syntax near '<'.
"Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...
> Hi Uri,
> Here's the code (some bits removed for security):
> CREATE TRIGGER trAll_ddl_server
> ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
> AS
> DECLARE @.eventData xml, @.eventID int
> SET @.eventData = eventdata()
> INSERT <AuditTbl> ( AuditEventObject, PostTime,
> spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
> ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
> VALUES ('trAll_ddl_server',
> cast(@.eventdata.query('data(//PostTime)') AS sysname),
> cast(@.eventdata.query('data(//spid)') AS sysname),
> cast(@.eventdata.query('data(//LoginName)') AS sysname),
> cast(@.eventdata.query('data(//EventType)') AS sysname),
> cast(@.eventdata.query('data(//ServerName)') AS sysname),
> cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
> cast(@.eventdata.query('data(//SchemaName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectName)') AS sysname),
> cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
> cast(@.eventdata.query('data(//ObjectType)') AS sysname),
> cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
> cast(@.eventdata.query('data(//EventData)') AS xml)
> )
> BEGIN
> PRINT 'Permission denied. please contact the DBA.'
> ROLLBACK
> -- Set RolledBack flag
> END
> GO
> /*
> DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
> DROP TRIGGER trAll_ddl_server ON ALL SERVER
> */
>
>
> "Uri Dimant" wrote:
>> Hi
>> Can you post DDL to be tested?
>>
>> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
>> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
>> > I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
>> > coming
>> > up with the following error:
>> > -----
>> > Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
>> > "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
>> > registration.
>> > -----
>> >
>> > I've checked on the internet and have noted someone else having the
>> > same
>> > problem, but with no resolution. Is this a bug? Has anyone a
>> > work-around?
>> >
>> > Thanks.
>> >
>> >
>> >
>>|||Hi Uri,
I did say that some code had been taken out. Ok, here's some code that
should run as-is:
CREATE TRIGGER trig_All_ddl_database
ON DATABASE FOR
DDL_DATABASE_LEVEL_EVENTS
AS
PRINT 'Database DDL Trigger Fired.'
GO
"Uri Dimant" wrote:
> Sorry, I've got the error
> Msg 156, Level 15, State 1, Procedure trAll_ddl_server, Line 3
> Incorrect syntax near the keyword 'AS'.
> Msg 102, Level 15, State 1, Procedure trAll_ddl_server, Line 8
> Incorrect syntax near '<'.
>
>
>
> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> news:D494BCC9-0428-4486-B270-987210401878@.microsoft.com...
> > Hi Uri,
> >
> > Here's the code (some bits removed for security):
> >
> > CREATE TRIGGER trAll_ddl_server
> > ON ALL SERVER DDL_SERVER_LEVEL_EVENTS
> > AS
> >
> > DECLARE @.eventData xml, @.eventID int
> > SET @.eventData = eventdata()
> >
> > INSERT <AuditTbl> ( AuditEventObject, PostTime,
> > spid, LoginName, EventType, ServerName, DatabaseName, SchemaName,
> > ObjectName, TargetObjectName, TSQLCmd, ObjectType, EventData)
> > VALUES ('trAll_ddl_server',
> > cast(@.eventdata.query('data(//PostTime)') AS sysname),
> > cast(@.eventdata.query('data(//spid)') AS sysname),
> > cast(@.eventdata.query('data(//LoginName)') AS sysname),
> > cast(@.eventdata.query('data(//EventType)') AS sysname),
> > cast(@.eventdata.query('data(//ServerName)') AS sysname),
> > cast(@.eventdata.query('data(//DatabaseName)') AS sysname),
> > cast(@.eventdata.query('data(//SchemaName)') AS sysname),
> > cast(@.eventdata.query('data(//ObjectName)') AS sysname),
> > cast(@.eventdata.query('data(//TargetObjectName)') AS sysname),
> > cast(@.eventdata.query('data(//ObjectType)') AS sysname),
> > cast(@.eventdata.query('data(//TSQLCommand)') AS nvarchar(max)),
> > cast(@.eventdata.query('data(//EventData)') AS xml)
> > )
> >
> > BEGIN
> > PRINT 'Permission denied. please contact the DBA.'
> > ROLLBACK
> > -- Set RolledBack flag
> > END
> > GO
> >
> > /*
> > DISABLE TRIGGER trAll_ddl_server ON ALL SERVER
> > DROP TRIGGER trAll_ddl_server ON ALL SERVER
> > */
> >
> >
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> Can you post DDL to be tested?
> >>
> >>
> >> "Pendragon999" <Pendragon999@.discussions.microsoft.com> wrote in message
> >> news:A646F19B-B059-41F5-88FF-A290034AFDAD@.microsoft.com...
> >> > I'm trying to create an 'ALL' Server DLL Trigger, as per BOL, but it's
> >> > coming
> >> > up with the following error:
> >> > -----
> >> > Msg 1082, Level 15, State 1, Procedure trAll_ddl_server, Line 33
> >> > "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger
> >> > registration.
> >> > -----
> >> >
> >> > I've checked on the internet and have noted someone else having the
> >> > same
> >> > problem, but with no resolution. Is this a bug? Has anyone a
> >> > work-around?
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>