URGENT: alternative to log file being full
Rob
I am attempting to alter using the following command:
alter table tb_History
alter column browser INT NULL
go
In running the above stmt for the first time, it filled up the tran log, so
I expanded it to over 130 GB, essentially exhausting the entire disk, but to
no avail.... the stmt ran for over 12 hours and reported the same error:
The log file for database 'DB_Dmart' is full.
Back up the transaction log for the database to free up some log space.
The statement has been terminated.
The size of the DB_Dmart database is approx. 200 GB and resides on an 8-way
8 GB server. Its recovery mode is set to SIMPLE.
As an alternative, I was also thinking of creating a new table with proposed
altered column and then doing a BCP out and in from the original table to the
new table. After which, I would delete the old table and rename the new one
as the old. However, I am afraid that the BCP'd file may be too large for my
disk to accommodate and I'm not sure if, from a time perspective, this would
be a good option.
If someone can provide any help, suggestion, and/or comments, it would be
highly appreciated. Thanks in advance.
Tony Rogerson
Create a new column with the right data type, call it new_browser INT but
make it NULL.
Now the key is to update the new_browser column in batches of 'x',000 rows
so that the transaction log has a chance to truncate.
You could do that using a cursor and go through each row updating
new_browser but it will be slow, but I'm not sure if it will be as slow as
doing this...
set rowcount 10000
declare @i int
set @i = ( select min( youruniquecolumnn ) from yourtable )
while 1 = 1
begin
update yourtable set new_browser = browser
where youruniquecolumn >= @i
if @@rowcount = 0
break
end
Make sure you test on a subset of the data first!
You would then, drop the old column and constraints, use sp_rename to rename
the new_browser to browser and put all your constraints back and then i
think job done.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Rob" <R...@discussions.microsoft.com> wrote in message
news:0998C543-7C36-4753...@microsoft.com...
Raymond D'Anjou
"Tony Rogerson" <tonyro...@sqlserverfaq.com> wrote in message
news:%238iCR6z...@TK2MSFTNGP10.phx.gbl...
Tony Rogerson
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Raymond D'Anjou" <rda...@canatradeNOSPAM.com> wrote in message
news:uc1%23v$zJGHA...@tk2msftngp13.phx.gbl...
JT
from tb_History, altering the column, truncating the transaction log,
re-creating the indexes, and then truncating the log again.
"Rob" <R...@discussions.microsoft.com> wrote in message
news:0998C543-7C36-4753...@microsoft.com...
Uri Dimant
In addition to Tony's idead to update with a small batches
try this one
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
UPDATE Table
SET col= baabalba
WHERE ..........
IF @@ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Rob" <R...@discussions.microsoft.com> wrote in message
news:0998C543-7C36-4753...@microsoft.com...
Tony Rogerson
Before you put the constraints back on, do an ALTER TABLE to make the column
NOT NULL but you'll have to put a DEFAULT on there as well.
Other way would be just to BCP.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyro...@sqlserverfaq.com> wrote in message
news:%238iCR6z...@TK2MSFTNGP10.phx.gbl...
Rob
table does not have a unique column. Rows are repeating as this database is
treated as sort of a warehouse.
Tony Rogerson
Couple it with UPDATE ... WHERE CURRENT OF <cursor name>
I've not done one in ages so take a look at the syntax and have a play on a
subset of data, if you get stuck post back.
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Rob" <R...@discussions.microsoft.com> wrote in message
news:189D34EB-E9F3-4D20...@microsoft.com...
Raymond D'Anjou
"Tony Rogerson" <tonyro...@sqlserverfaq.com> wrote in message
news:OuaexB0J...@TK2MSFTNGP14.phx.gbl...
Tony Rogerson
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Raymond D'Anjou" <rda...@canatradeNOSPAM.com> wrote in message
news:%23ZVn3R0...@TK2MSFTNGP14.phx.gbl...
Rob
JT
transaction into smaller batches and thus allow the transaction log to be
truncated.
Create a table called tb_History_New with the new structure. You can then
insert rows from tb_History to tb_History_New in smaller batches one month
at a time. This will reduce the maximum number of active transactions and
also provide a window for the trnsaction log to be truncated. Even in a
table with no primary key defined, there should at least be some suitable
datetime column.
declare @TranDate as datetime
select @TranDate = '2002/01/01'
while
insert into tb_History_New select * from tb_History where TranDate >=
@TranDate
if @@rowcount = 0 break
checkpoint -- provide checkoint for log truncation
waitfor delay '00:00:30' -- wait 30 seconds for the checkpoint to
take effect
select @TranDate = dateadd( month, 1, @TranDate)
end
Once done, drop tb_History and rename tb_History_New.
"Rob" <R...@discussions.microsoft.com> wrote in message
news:FAE23865-D77E-4651...@microsoft.com...
ML
conclusion that the column needs to stay nillable.
ML
ML
ML
JT
"ML" <M...@discussions.microsoft.com> wrote in message
news:930D0EC4-7DEB-40A7...@microsoft.com...
ML
or nillable?
Is it better to be in denial or to be in the Nile?
:)
ML