Alter table db size getting increased.

Hi,
I am doing alter table and updating Nvarchar datatype to Ntext. AS there are
almost
54,09,873 records installation is taking more than 3 hours and log/database
files are getting increased beyond limit. Now there is hardly space left and
still installation is not completed.
Tasks Restore Size After installation
Datafile Size 39000 46529 MB
Log Size 4550 14032 MB
ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
54,09,873 Rows affected.
As far as I know and understand Alter basically copy the contents to some
temp table, deletes the table, recreate the table with new definition and
again copy back to original table( Sorry if sequence is wrong ?) and I thi
nk
this is the reason why this script is taking more space and impacting log
size.
IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
SIZE OF DB INCREASE.
--
SanjayYes, you might try dumping the data into an external text file, and then bc
p
or bulk insert it back into the new table structure.
"Sanjay" wrote:

> Hi,
> I am doing alter table and updating Nvarchar datatype to Ntext. AS there a
re
> almost
> 54,09,873 records installation is taking more than 3 hours and log/databas
e
> files are getting increased beyond limit. Now there is hardly space left a
nd
> still installation is not completed.
> Tasks Restore Size After installation
> Datafile Size 39000 46529 MB
> Log Size 4550 14032 MB
> ALTER TABLE [dbo].[xyz] ALTER COLUMN EmailText ntext
> 54,09,873 Rows affected.
> As far as I know and understand Alter basically copy the contents to some
> temp table, deletes the table, recreate the table with new definition and
> again copy back to original table( Sorry if sequence is wrong ?) and I t
hink
> this is the reason why this script is taking more space and impacting log
> size.
> IS THERE ANY SOLN OR ALTERNATIVE WHICH CAN MAKE THIS FASTER AND REDUCE THE
> SIZE OF DB INCREASE.
> --
> Sanjay|||Thanks for your help,
can you help me in syntax and will this be logged in log file.
i never tried that but will this help in reducing time and DB size issues.
"CBretana" wrote:
> Yes, you might try dumping the data into an external text file, and then
bcp
> or bulk insert it back into the new table structure.
> "Sanjay" wrote:
>|||Look up BCP in BOL -- it's a utility that comes w/SQL server.
Setting the logging level to SIMPLE would help too -- look up ALTER
DATABASE; the setting is SET RECOVERY
"Sanjay" wrote:
> Thanks for your help,
> can you help me in syntax and will this be logged in log file.
> i never tried that but will this help in reducing time and DB size issues.
> "CBretana" wrote:
>|||I tried but log file was increased around 5 gb and data file also.
i am doing something like this.
alter database xyz
set recovery simple
alter table and modifying nvarchar to ntext ( around 54,09,873 rows)
set recovery full.
"KH" wrote:
> Look up BCP in BOL -- it's a utility that comes w/SQL server.
> Setting the logging level to SIMPLE would help too -- look up ALTER
> DATABASE; the setting is SET RECOVERY
>
> "Sanjay" wrote:
>