Return to Question

I have been asked to clean up preceding and appending spaces in several columns of a table. the table is an import from flat file and has many mistyped rows. the table has over 150 million rows, so far I have attempted performing an update statement that updates the columns using ltrim(rtrim(columnname)) and I have also tried to create a temp table (heap) and insert into ... select from using the same ltrim(rtrim(columnname)) syntax. in every case the transaction log grows out of control to the point of running out of disk space. I understand how transaction logs work fairly well. However in the context of performing some kind of bulk maintenance like this, I am stumped.

Currently the database is in bulk-logged recovery model, I am running transaction log backups every 30 seconds and the transaction log continues to grow faster than I can back it up. I have researched how I might break this job up into batches, but cannot seem to formulate a query that could do this effectively. I have an identity field I could key off of, all the other columns are varchar columns. Right now I'm thinking the answer is to simply get more disk space for the server, but I was hoping there was a better solution. Any advice is appreciated. Updated based on questions: I am using Microsoft SQL Server 2014 Enterprise Edition. the File is long gone, this is a table that has grown in size over the course of 10 years and many versions of SQL Server. only smaller incremental batch updates are performed and I do believe the new inserts are already cleansed before inserting. So I am fixing old stuff :( thanks,

I have been asked to clean up preceding and appending spaces in several columns of a table. the table is an import from flat file and has many mistyped rows.

The table has over 150 million rows.

So far I have attempted performing an update statement that updates the columns using ltrim(rtrim(columnname)) and I have also tried to create a temp table (heap) and insert into ... select from using the same ltrim(rtrim(columnname)) syntax.

In every case the transaction log grows out of control to the point of running out of disk space.

I understand how transaction logs work fairly well. However in the context of performing some kind of bulk maintenance like this, I am stumped.

Currently the database is in bulk-logged recovery model, I am running transaction log backups every 30 seconds and the transaction log continues to grow faster than I can back it up. I have researched how I might break this job up into batches, but cannot seem to formulate a query that could do this effectively. I have an identity field I could key off of, all the other columns are varchar columns. Right now I'm thinking the answer is to simply get more disk space for the server, however I was hoping there was a better solution.

I am using Microsoft SQL Server 2014 Enterprise Edition.

The flat file is long gone; this is a table that has grown in size over the course of 10 years and many versions of SQL Server. only smaller incremental batch updates are performed and I do believe the new inserts are already cleansed before inserting. So I am fixing old stuff :(

I have been asked to clean up preceding and appending spaces in several columns of a table. the table is an import from flat file and has many mistyped rows. the table has over 150 million rows, so far I have attempted performing an update statement that updates the columns using ltrim(rtrim(columnname)) and I have also tried to create a temp table (heap) and insert into ... select from using the same ltrim(rtrim(columnname)) syntax. in every case the transaction log grows out of control to the point of running out of disk space. I understand how transaction logs work fairly well. However in the context of performing some kind of bulk maintenance like this, I am stumped.

Currently the database is in bulk-logged recovery model, I am running transaction log backups every 30 seconds and the transaction log continues to grow faster than I can back it up. I have researched how I might break this job up into batches, but cannot seem to formulate a query that could do this effectively. I have an identity field I could key off of, all the other columns are varchar columns. Right now I'm thinking the answer is to simply get more disk space for the server, but I was hoping there was a better solution. Any advice is appreciated. thanks,

I have been asked to clean up preceding and appending spaces in several columns of a table. the table is an import from flat file and has many mistyped rows. the table has over 150 million rows, so far I have attempted performing an update statement that updates the columns using ltrim(rtrim(columnname)) and I have also tried to create a temp table (heap) and insert into ... select from using the same ltrim(rtrim(columnname)) syntax. in every case the transaction log grows out of control to the point of running out of disk space. I understand how transaction logs work fairly well. However in the context of performing some kind of bulk maintenance like this, I am stumped.

Currently the database is in bulk-logged recovery model, I am running transaction log backups every 30 seconds and the transaction log continues to grow faster than I can back it up. I have researched how I might break this job up into batches, but cannot seem to formulate a query that could do this effectively. I have an identity field I could key off of, all the other columns are varchar columns. Right now I'm thinking the answer is to simply get more disk space for the server, but I was hoping there was a better solution. Any advice is appreciated. Updated based on questions: I am using Microsoft SQL Server 2014 Enterprise Edition. the File is long gone, this is a table that has grown in size over the course of 10 years and many versions of SQL Server. only smaller incremental batch updates are performed and I do believe the new inserts are already cleansed before inserting. So I am fixing old stuff :( thanks,