Estimate Log Space for Alter Table Statement

My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.

What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.

Any suggestions? Thanks in advance.

You can change the truncate log setting in a database if you don't need it to recover in time of emergency it is a very complicated subject so run a search for truncate log on checkpoint option for your database setting in the BOL(books online). The links below are basic guidelines from Microsoft. Hope this helps.

http://support.microsoft.com/kb/873235

http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx

|||

If I remember correctly when you alter column in table, SQL server renames old one, created new table with new structure and next moves data into new table. So in your case you need at least amount of space your data table takes for log because all process has to be done at one shot.
But maybe you can do it yourself: create table with new structure and move data from old table to new table with quantity like 100 or 1000 records in one shot? Log file will be small and you should succeed. You only have to take care about identity insert if you have one in your table.

Thanks

|||

Thanks jpazgier, that's a great suggestion! I usually do the alter using Enterprise Manager directly for this client but I have scripted this type of change for other clients. I never thought about taking the generated script and breaking up the insert part of it into multiple inserts and performing the truncate log after each insert. That would reduce the log space required.

If I don't delete the old records as I go, I still need twice the data just for the old and new copies but I wouldn't need so much log space. If I trust my alterations, then I could delete the old records as they get moved into the new copy and really reduce the free space required. Not as safe but probably sufficient.

Thanks

|||

(1) You could keep the DB in simple mode while you do the data transfer.

(2) You can also have a job running to truncate the log during the transfer and let the job run every 1 minute, so you dont have to do it yourself manually.

(3) You can use bcp/Bulk Insert to transfer the data.