deadlocking

Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:
> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>sql

deadlocking

Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
Richard
The only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:

> Recently we ran a script that added a new column to a table with 120 million
> rows of data. A large table with a lot of wide columns. The script was ran
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me that
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>
|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>

deadlocking

Recently we ran a script that added a new column to a table with 120 million
rows of data. A large table with a lot of wide columns. The script was ran
on a copy of the production database (a test copy) as part of the QA
process. The script ran 17 hours, and our QA department is telling me that
the script caused deadlocks all day long on the production database.
The only line in the script is the add column alter table command.
I was not there to see for myself, so has anyone experienced this
themselves?
Thanks
RichardThe only cause i can imagine for deadlocks in your scenario is some deadlock
on system tables.
For an alteration on a table so big and wide i suggest the following:
- create a copy of your table including the new column and all the
permission defined for the old table.
- use SSIS to copy the old table into the new table (look at Books on Line
to see how configure the package,the task, etc.)
- when the new table is filled, rename the old table, rename the new table
with the old name and drop the old table.
The process will be long but if you use as source a SQL Statement istead of
the table name, you can set the WITH NOLOCK option reducing the locking
activity on the input table.
Gilberto Zampatti
"Richard Douglass" wrote:

> Recently we ran a script that added a new column to a table with 120 milli
on
> rows of data. A large table with a lot of wide columns. The script was r
an
> on a copy of the production database (a test copy) as part of the QA
> process. The script ran 17 hours, and our QA department is telling me tha
t
> the script caused deadlocks all day long on the production database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>
>|||ALTER TABLE requires a schema modification lock. A schema modification lock
is not compatible with other lock types and will block access to the table
while the ALTER is running.
Depending the the particulars, adding a new column may require every row to
be modified or may run very quickly with only meta-data changes. In the
case of a large table with every row changed, you might find it faster to
build a new table using SELECT...INTO, dropping the old one and then
recreating indexes and constraints afterward.
Hope this helps.
Dan Guzman
SQL Server MVP
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>|||I bet it was BLOCKING and not DEADLOCKING that occurred.
If you have to do this in the future, first manually grow the database to
have empty space big enough for double the table size. Also manually grow
the transaction log file to handle full table size including indexes. THEN
try the alter. In any case, expect altering a table with 120M fat rows to
take a while, especially on poor hardware. I would have made this a
low/no-usage-time activity.
TheSQLGuru
President
Indicium Resources, Inc.
"Richard Douglass" <RDouglass@.arisinc.com> wrote in message
news:e44tw3JmHHA.3264@.TK2MSFTNGP04.phx.gbl...
> Recently we ran a script that added a new column to a table with 120
> million rows of data. A large table with a lot of wide columns. The
> script was ran on a copy of the production database (a test copy) as part
> of the QA process. The script ran 17 hours, and our QA department is
> telling me that the script caused deadlocks all day long on the production
> database.
> The only line in the script is the add column alter table command.
> I was not there to see for myself, so has anyone experienced this
> themselves?
> Thanks
> Richard
>

Deadlock in SQL 2000

Hi
I think you don't need wrap the script BEGIN TRAN ...COMMIT because the tran
asction is atomic by itself
"Hongbo" <hongbo@.goodoffices.com> wrote in message news:ezJRDs6lFHA.1372@.TK2
MSFTNGP10.phx.gbl...
Hi,
I have 2 update statements in SQL Server 2000:
1. with TRAN
==
BEGIN TRAN UpdateProductShipFlag
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
COMMIT TRAN UpdateProductShipFlag
==
2. without TRAN
==
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
==
I got "deadlock" error with first statement.
I do not use locking hints in any of my SQL statements.
Would you please tell me the difference between the above 2 statements
on lock level?
Thank you
HBAlso, it takes 2 SPIDs to deadlock. Where's the other SPID? What T-SQL
is the other SPID executing? What locks are involved in the deadlock?
etc. etc. Lots of questions that need to be asked to resolve your issue.
But to answer your question, there is no difference between those 2
statements on a lock level - as Uri said the update statement is atomic
on it's own so the scope of any locks involved in that statement are the
same with or without the explicit BEGIN/COMMIT and the BEING/COMMIT pair
don't change the type of locks involved or the resources locked.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Uri Dimant wrote:

> Hi
> I think you don't need wrap the script BEGIN TRAN ...COMMIT because
> the tranasction is atomic by itself
>
> "Hongbo" <hongbo@.goodoffices.com <mailto:hongbo@.goodoffices.com>>
> wrote in message news:ezJRDs6lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have 2 update statements in SQL Server 2000:
> 1. with TRAN
> ==
> BEGIN TRAN UpdateProductShipFlag
> UPDATE ProductToOrder
> SET ShippedFlag=1, ShippedDate=getdate()
> WHERE ProductToOrderId=500603
> COMMIT TRAN UpdateProductShipFlag
> ==
> 2. without TRAN
> ==
> UPDATE ProductToOrder
> SET ShippedFlag=1, ShippedDate=getdate()
> WHERE ProductToOrderId=500603
> ==
> I got "deadlock" error with first statement.
> I do not use locking hints in any of my SQL statements.
> Would you please tell me the difference between the above 2 statements
> on lock level?
> Thank you
> HB
>|||Uri and Mike,
Thank you for the help!
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uA0
26l$lFHA.4056@.TK2MSFTNGP10.phx.gbl...
Also, it takes 2 SPIDs to deadlock. Where's the other SPID? What T-SQL is
the other SPID executing? What locks are involved in the deadlock? etc. et
c. Lots of questions that need to be asked to resolve your issue.
But to answer your question, there is no difference between those 2 statemen
ts on a lock level - as Uri said the update statement is atomic on it's own
so the scope of any locks involved in that statement are the same with or wi
thout the explicit BEGIN/COMMIT and the BEING/COMMIT pair don't change the t
ype of locks involved or the resources locked.
mike hodgson
blog: http://sqlnerd.blogspot.com
Uri Dimant wrote:
Hi
I think you don't need wrap the script BEGIN TRAN ...COMMIT because the tra
nasction is atomic by itself
"Hongbo" <hongbo@.goodoffices.com> wrote in message news:ezJRDs6lFHA.1372@.TK2
MSFTNGP10.phx.gbl...
Hi,
I have 2 update statements in SQL Server 2000:
1. with TRAN
==
BEGIN TRAN UpdateProductShipFlag
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
COMMIT TRAN UpdateProductShipFlag
==
2. without TRAN
==
UPDATE ProductToOrder
SET ShippedFlag=1, ShippedDate=getdate()
WHERE ProductToOrderId=500603
==
I got "deadlock" error with first statement.
I do not use locking hints in any of my SQL statements.
Would you please tell me the difference between the above 2 statements
on lock level?
Thank you
HB

Deadlock accessing variables

I am trying to access a single variable in a script and a deadlock error continues to come up. I have a single string variable that is added to the readwritevariables collection in the editor. I am trying to execute the following code:

Dim variables As Variables

Try

Dts.VariableDispenser.LockForWrite("Test")

Dts.VariableDispenser.GetVariables(variables)

Catch ex As Exception

Throw ex

Finally

variables.Unlock()

End Try

I have installed Service Pack2 and this error continues. I know it has been posted on before and I appreciate any help.

Thanks

You shouldn't need to lock the variable in your script if you've added it in the editor. Try either removing the lock in your script or removing the variable in the editor.|||

Thank you. It took me awhile but I figured it out. If I was smart enough to read the error I would have figured it out sooner.

Thanks again

dbo schema added when i rename table

i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05

my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one

1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.

DROP TABLE aMgmt.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'
GO

any help?Unclear what is happening or what you are trying to do.

What did you use to record the script? What was the original ownership on the tables? What were you logged in as when you scripted the objects?

Who do you WANT to have ownership of the objects? I assume dbo...|||It may be becoz u r trying to copy/create the data using a dbo user.