How to prevent Transaction from growing to large

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to prevent Transaction from growing to large

Author

Topic

magictech
Starting Member

44 Posts

Posted - 2004-09-17 : 10:27:24

I added a column to a large table that has about 14 million records. During the process of adding this column, the transaction log of the database grew to about 55Gig, the database it self is only 32 Gig. I would like to know how to prevent the transaction from growing too large since I have to add the same column to several databases of the same size.

Here is what I’ve tried so far and haven’t had any luck:

1. I’ve change the recover mode of the database to simple. This didn’t work since SQL Server sees the process of adding a column as one transaction.
2. I’ve use profiler to capture data while adding columns to smaller tables but these didn’t help either.

Does anyone out there know of any method to prevent the transaction log from growing too large when adding a column to a very large table?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-17 : 10:47:19

see
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

For what happens when you ad the column see
http://www.nigelrivett.net/AlterTableProblems.html

To stop the tr log growing and probably add the column a lot quicker consider bcping the data out, truncate the table, add the column (or drop the table and recreate) then bcp the data back in.
You can include a default value for the column by bcping from a query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

magictech
Starting Member

44 Posts

Posted - 2004-09-17 : 12:10:26

Hi nr,

Thanks for your respond. Do you by any chance have a sample script that could accomplish this task using your recommended method? That is:

Bcp the data out.
Truncate the data. I prefer this since there are indexes on that table
Alter the table to add the new column.
Bcp the data back in the table

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-18 : 16:46:50

exec master..xp_cmdshell 'bcp "select *, 1 from mydb..mytbl" out c:\mytbl.bcp -N -T'
truncate table mytbl
alter table mytbl add i int
exec master..xp_cmdshell 'bcp mydb..mytbl in c:\mytbl.bcp -N -T -h"tablock"'

Consider dropping the indexes then adding them after the copy.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources