Transaction log is being filled up

The  'test_table'  is indexed .

More details please - you don't say which Sybase product and version you're using, or even quite what the problem is.

Is the problem that where you do try this using ALTER TABLE the log fills? Are you looking for an alternative to ALTER TABLE, or a means of preventing the log filling?

Depending on what the problem is, it could be handy to see the table definition including all indexes.

One way to do this would be to create a new table with the new column included and move the data over in batches (based on ranges of the primary key), allowing the tran log to be dumped in between batches.  Once the data is moved over, drop the original table and rename the new one.  Don't forget to create the same indexes, triggers, and constraints.

This test_table has 8 million records so the transaction log is getting filled up while copying all the data to a temp table.
So I need test_table to have another placeID varchar(15) to be added without using alter command.
some sql code will be very helpful to me


CREATE TABLE dbo.test_table
(
    STDId             varchar(15) NOT NULL,
    GROUPID            int        NOT NULL,
    AREAId           varchar(15)  NOT NULL,
 
)
LOCK DATAROWS
go
GRANT SELECT ON dbo.test_table TO ReadExecute
go

IF OBJECT_ID('dbo.test_table') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.test_table >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.test_table >>>'
go
CREATE UNIQUE INDEX Test_PK
    ON dbo.Deal(AREAId,STDId,GROUPID)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.test_table') AND name='Test_PK')
    PRINT '<<< CREATED INDEX dbo.test_table.Test_PK >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.test_table.Test_PK >>>'
go

Bret's already given you the answer - do batched inserts and regularly dump the log in between.

declare @rowcount int

select @rowcount = 1
set rowcount 50000

while @rowcount > 0
begin
     dump tran [database] with truncate_only

     insert new_table (STDId, GROUPID, AREAId, Column1)
     select (STDId, GROUPID, AREAId, null)   -- or any other value
     from test_table

     select @rowcount = @@rowcount
end
set rowcount 0


This assumes the new table has already been created. You probably want to create its index(es) first as creating them once fully populated may also blow your log and/or tempdb.

Because this is not doing the insert as a single transaction, you'll also need some code to detect if a batch failed.

I've picked 50,000 as a batch size but it can be any size, really.

Sorry for not being clear  -- I need the 'test_table' only with the added new column -- 'column1' and the present 8 million records
and not the new_table with 8 million records.
Can you please correct  this .

Also near the 800000 records if I take the exact count(*) from the table and give that value will be ideal.

CREATE TABLE dbo.new_table
(
    STDId             varchar(15) NOT NULL,
    GROUPID            int        NOT NULL,
    AREAId           varchar(15)  NOT NULL,
    Column1           Varchar(15)  NOT NUL
 
)
LOCK DATAROWS
go
GRANT SELECT ON dbo.new_table TO ReadExecute
go

IF OBJECT_ID('dbo.new_table') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.new_table >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.new_table >>>'
go
CREATE UNIQUE INDEX Test_PK
    ON dbo.Deal(AREAId,STDId,GROUPID)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.new_table') AND name='Test_PK')
    PRINT '<<< CREATED INDEX dbo.new_table.Test_PK >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.new_table.Test_PK >>>'
go

declare @rowcount int

select @rowcount = 1
set rowcount 800000

while @rowcount > 0
begin
     dump tran [database] with truncate_only

     insert new_table (STDId, GROUPID, AREAId, Column1)
     select (STDId, GROUPID, AREAId, null)   -- or any other value
     from test_table

     select @rowcount = @@rowcount
end
set rowcount 0

'DROPPING THE test_table

IF OBJECT_ID('dbo.test_table') IS NOT NULL
BEGIN
    DROP TABLE dbo.test_table
    IF OBJECT_ID('dbo.test_table') IS NOT NULL
        PRINT '<<< FAILED DROPPING TABLE dbo.test_table >>>'
    ELSE
        PRINT '<<< DROPPED TABLE dbo.test_table >>>'
END

' CREATING THE  'test_table'

CREATE TABLE dbo.test_table
(
    STDId             varchar(15) NOT NULL,
    GROUPID            int        NOT NULL,
    AREAId           varchar(15)  NOT NULL,
    column1          varchar(15)  NOT NULL
 
)
LOCK DATAROWS
go
GRANT SELECT ON dbo.test_table TO ReadExecute
go

IF OBJECT_ID('dbo.test_table') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.test_table >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.test_table >>>'
go
CREATE UNIQUE INDEX Test_PK
    ON dbo.Deal(AREAId,STDId,GROUPID)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.test_table') AND name='Test_PK')
    PRINT '<<< CREATED INDEX dbo.test_table.Test_PK >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.test_table.Test_PK >>>'
go

declare @rowcount int

select @rowcount = 1
set rowcount 800000

while @rowcount > 0
begin
     dump tran [database] with truncate_only

     insert test_table (STDId, GROUPID, AREAId, Column1)
     select (STDId, GROUPID, AREAId, null)   -- or any other value
     from new_table

     select @rowcount = @@rowcount
end
set rowcount 0

Sorry for not being clear  -- I need the 'test_table' only with the added new column -- 'column1' and the present 8 million records
and not the new_table with 8 million records.
Can you please correct  this .

Also near the 800000 records if I take the exact count(*) from the table and give that value will be ideal.

CREATE TABLE dbo.new_table
(
    STDId             varchar(15) NOT NULL,
    GROUPID            int        NOT NULL,
    AREAId           varchar(15)  NOT NULL,
    Column1           Varchar(15)  NOT NUL
 
)
LOCK DATAROWS
go
GRANT SELECT ON dbo.new_table TO ReadExecute
go

IF OBJECT_ID('dbo.new_table') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.new_table >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.new_table >>>'
go
CREATE UNIQUE INDEX Test_PK
    ON dbo.Deal(AREAId,STDId,GROUPID)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.new_table') AND name='Test_PK')
    PRINT '<<< CREATED INDEX dbo.new_table.Test_PK >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.new_table.Test_PK >>>'
go

declare @rowcount int

select @rowcount = 1
set rowcount 800000

while @rowcount > 0
begin
     dump tran [database] with truncate_only

     insert new_table (STDId, GROUPID, AREAId, Column1)
     select (STDId, GROUPID, AREAId, null)   -- or any other value
     from test_table

     select @rowcount = @@rowcount
end
set rowcount 0

'DROPPING THE test_table

IF OBJECT_ID('dbo.test_table') IS NOT NULL
BEGIN
    DROP TABLE dbo.test_table
    IF OBJECT_ID('dbo.test_table') IS NOT NULL
        PRINT '<<< FAILED DROPPING TABLE dbo.test_table >>>'
    ELSE
        PRINT '<<< DROPPED TABLE dbo.test_table >>>'
END

' CREATING THE  'test_table'

CREATE TABLE dbo.test_table
(
    STDId             varchar(15) NOT NULL,
    GROUPID            int        NOT NULL,
    AREAId           varchar(15)  NOT NULL,
    column1          varchar(15)  NOT NULL
 
)
LOCK DATAROWS
go
GRANT SELECT ON dbo.test_table TO ReadExecute
go

IF OBJECT_ID('dbo.test_table') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.test_table >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.test_table >>>'
go
CREATE UNIQUE INDEX Test_PK
    ON dbo.test_table(AREAId,STDId,GROUPID)
go
IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('dbo.test_table') AND name='Test_PK')
    PRINT '<<< CREATED INDEX dbo.test_table.Test_PK >>>'
ELSE
    PRINT '<<< FAILED CREATING INDEX dbo.test_table.Test_PK >>>'
go

declare @rowcount int

select @rowcount = 1
set rowcount 800000

while @rowcount > 0
begin
     dump tran [database] with truncate_only

     insert test_table (STDId, GROUPID, AREAId, Column1)
     select (STDId, GROUPID, AREAId, null)   -- or any other value
     from new_table

     select @rowcount = @@rowcount
end
set rowcount 0