Preparing db for update of 200K records...

1 Dec 2005 6:39 PM

Eric

I'm in the process of encrypting my database and need to add a new column and
then populate it w/an encryped string from an existing column. While I've
begun to do this in my testing environment, I've noticed my log file to grow
substantially. In fact, the following was logged on my test db: "Autogrow of
file 'ER_log' in database 'DEV' cancelled or timed out after 2594 ms"

Can anyone offer any advice or suggestions as to any preliminary things I
should do before populating the new column?

My production environment is as follows:
Windows 2003/SQL Server 2000 Enterprise
TranLog - 500mb allocated/Auto Growth by 10%
Free disk space - 80 GB
This table has two indexes - one clustered, one non

1 Dec 2005 6:56 PM

Alexander Kuznetsov

when you add and populate a column, it is very likely that you are
getting a lot of page splits. If you set fillfactor to some low value,
the rows will have enough room to grow within exiisting pages, there
will be no splits.

1 Dec 2005 7:12 PM

JT

Drop the indexes, add the column, update the column, then re-create the
indexes.

Show quote

"Eric" <E***@discussions.microsoft.com> wrote in message
news:86097AD4-3F97-499D-A6B9-0D337109B604@microsoft.com...
> I'm in the process of encrypting my database and need to add a new column
> and
> then populate it w/an encryped string from an existing column. While I've
> begun to do this in my testing environment, I've noticed my log file to
> grow
> substantially. In fact, the following was logged on my test db: "Autogrow
> of
> file 'ER_log' in database 'DEV' cancelled or timed out after 2594 ms"
>
> Can anyone offer any advice or suggestions as to any preliminary things I
> should do before populating the new column?
>
> My production environment is as follows:
> Windows 2003/SQL Server 2000 Enterprise
> TranLog - 500mb allocated/Auto Growth by 10%
> Free disk space - 80 GB
> This table has two indexes - one clustered, one non
>
>

1 Dec 2005 7:58 PM

Eric

I didn't think it was possible to drop a clustered index. Should I rather
use DBCC DBREINDEX on the clustered after the update is complete?

Show quote

"JT" wrote:

> Drop the indexes, add the column, update the column, then re-create the
> indexes.
>
> "Eric" <E***@discussions.microsoft.com> wrote in message
> news:86097AD4-3F97-499D-A6B9-0D337109B604@microsoft.com...
> > I'm in the process of encrypting my database and need to add a new column
> > and
> > then populate it w/an encryped string from an existing column. While I've
> > begun to do this in my testing environment, I've noticed my log file to
> > grow
> > substantially. In fact, the following was logged on my test db: "Autogrow
> > of
> > file 'ER_log' in database 'DEV' cancelled or timed out after 2594 ms"
> >
> > Can anyone offer any advice or suggestions as to any preliminary things I
> > should do before populating the new column?
> >
> > My production environment is as follows:
> > Windows 2003/SQL Server 2000 Enterprise
> > TranLog - 500mb allocated/Auto Growth by 10%
> > Free disk space - 80 GB
> > This table has two indexes - one clustered, one non
> >
> >
>
>
>

1 Dec 2005 9:20 PM

JT

Yes, you can drop a clustered index, but first drop the non-clustered
indexes, then alter the table to drop the primary key constraint (if
applicable), then drop the non-clustered index. The reason I suggest this is
not just to reduce index fragmentation, but also to reduce transaction
logging during the add / update process. After the add / update column
process has completed, re-create the indexes and PK constraint. Once done,
there would be no need for DBCC DBREINDEX or INDEXDEFRAG.

Show quote

"Eric" <E***@discussions.microsoft.com> wrote in message
news:943B8D72-79E2-42E4-8C01-B960F38EBD48@microsoft.com...
>I didn't think it was possible to drop a clustered index. Should I rather
> use DBCC DBREINDEX on the clustered after the update is complete?
>
> "JT" wrote:
>
>> Drop the indexes, add the column, update the column, then re-create the
>> indexes.
>>
>> "Eric" <E***@discussions.microsoft.com> wrote in message
>> news:86097AD4-3F97-499D-A6B9-0D337109B604@microsoft.com...
>> > I'm in the process of encrypting my database and need to add a new
>> > column
>> > and
>> > then populate it w/an encryped string from an existing column. While
>> > I've
>> > begun to do this in my testing environment, I've noticed my log file to
>> > grow
>> > substantially. In fact, the following was logged on my test db:
>> > "Autogrow
>> > of
>> > file 'ER_log' in database 'DEV' cancelled or timed out after 2594 ms"
>> >
>> > Can anyone offer any advice or suggestions as to any preliminary things
>> > I
>> > should do before populating the new column?
>> >
>> > My production environment is as follows:
>> > Windows 2003/SQL Server 2000 Enterprise
>> > TranLog - 500mb allocated/Auto Growth by 10%
>> > Free disk space - 80 GB
>> > This table has two indexes - one clustered, one non
>> >
>> >
>>
>>
>>

1 Dec 2005 8:10 PM

Alexander Kuznetsov

> Drop the indexes, add the column, update the column

even if there are no indexes, still there might be lots of page splits.
You still might need to set fillfactor to a low value.

Another way is to
select t.*, new_encrypted_column
into staging_table
from old_table

drop old table

rename staging table

that also could be way faster

1 Dec 2005 8:02 PM

Stu

backup the database first, set the recovery mode to simple (minimal
transaction logs), perform the procedure, take another backup, and set
the recovery mode back to full.