transaction log full while increasing column size
Hi:
I am trying to increase size of a few columns in a very large table (from numeric(7,2) to (11,6)). After the "alter table <my table>" command ran for 1:30 hours I got the following error message:
"The transaction log for database 'MyDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
What should I do to resolve the issue? Thanks.
I am trying to increase size of a few columns in a very large table (from numeric(7,2) to (11,6)). After the "alter table <my table>" command ran for 1:30 hours I got the following error message:
"The transaction log for database 'MyDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"
What should I do to resolve the issue? Thanks.
ASKER
Hi David:
I talked to our DBA and found they haven't backed up the trans log for a while. After the log is backed up I should be able to do what I wanted to do, but I am thinking of using a different approach than my original one since it took more than an hour to run and fed up the transaction log.
Do you think the following would work, wouldn't take too long, and wouldn't feed up the trans log?
- create a new table with exact same schema except for larger column space for the columns I wanted to change.
- insert everything from the original table
- rename the original table and the new table so new could be in business.
Is "insert into <new table> select * from <original table>" efficient enough to avoid long running time and huge amount trans records?
Thanks
I talked to our DBA and found they haven't backed up the trans log for a while. After the log is backed up I should be able to do what I wanted to do, but I am thinking of using a different approach than my original one since it took more than an hour to run and fed up the transaction log.
Do you think the following would work, wouldn't take too long, and wouldn't feed up the trans log?
- create a new table with exact same schema except for larger column space for the columns I wanted to change.
- insert everything from the original table
- rename the original table and the new table so new could be in business.
Is "insert into <new table> select * from <original table>" efficient enough to avoid long running time and huge amount trans records?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it, David. Let me work on it and see how it goes...
ASKER
Problem solved. Thanks.
Does the transaction log have auto-grow?
Did the transaction log consume all available disk?
What size was the log before this alter table and after?
What recovery model is this database in? If full, how frequently are you doing transaction log backups? When are you doing a full database backup?
What is the status of this database? Is this a key production database that can't be taken off-line for anything, or on a test system, or somewhere in between?
As a thought, can you do something like this:
select * into newTable from existingTable where 1 = 2
alter newTable ...
rename existingtable to orginaltablename
rename newtable to existingtablename
insert existingtable
select top 1000
from originaltable ot
left outer join existingtable et
on ot.key = et.key
where et.key is null
The idea is to create a new table with the correct structure, and batch copy the rows across to the new table. That way wont be such a big all-or-nothing hit on the database and its log and the server.
HTH
David