HI In SQL Server, We have table with 8 crore of records and 3 column data type is float right now. Now we need to change the float data type column into Decimal column. How to proceed it with minimum downtime? We fired the usual ALTER statement to change the data type, but log file got filled and going to system out of memory exception. So kindly let me the better way to solve this issue. We cant use this technique Like: Creating 3 new temp columns and updating the existing data batch wise and dropping the existing column and renaming the temp column to live columns. Regards Karthik
If you don't have the possibility to create Three new columns, batch over the data and finally dropping old columns and renaming the new columns, I guess that's because you have a problem finding a long enough window for the job. What you could try, but I urge you to performance test the solution first: Create a new table with the correct datatypes. Rename existing table Create a view which does something like this: SELECT CAST(col1 as decimal(x,y)) as col1, CAST(col2 as decimal(x,y)) as col2, CAST(col3 as decimal(x,y)) as col3, --Any other columns you which are defined in the table FROM OriginalTableWithNewName UNION ALL SELECT col1, col2, col3, --Any other columns needed FROM NewTable Now your application can use the view. You would want to create an instead of-trigger on the view to handle inserts, updates and deletes. Inserts are simple - just use the INSERTED virtual table as source and insert into the new table with the correct datatypes. Deletes and Updates are likely to see a performance hit, when you have to use the INSERTED and DELETED virtual tables in WHERE and JOIN predicates in the instead of-trigger. When view and instead of-trigger is there, you can start moving data from the original table to the new table, some hundred thousand rows at the time. Transaction log will be filled also during this time, but you won't have to handle the whole change in one go. Either go shop for a large enough disk to handle the larger transaction log and a large enough backup storage for the transaction log backups. When all the rows have come into the new table, you can drop the old table, drop the view and rename the new table to the original table name. The technique with the view could also be used to handle adding Three new columns to the existing table and batch over rows. You'll still need a view and you'll still need an instead of-trigger, but your deletes and updates will be easier to manage in the view, since there's only one table. The performance will however still not be fantastic, as you'll need to use OR-conditions in the WHERE-clause of the instead of-trigger. The view definition would - instead of a UNION ALL - use a COALESCE or ISNULL with oldcol and newcol, to determine for each row from which column to fetch the data.