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