Transaction log is being filled up

I want to add a column Column1 of datatype 'date' at the end of a tabel 'test_table'
without using the Alter statement . This column can be nullable. The test_table is  with 8 million records .
JoseripAsked:
Who is Participating?
 
PeretzBenRafaelConnect With a Mentor Commented:
 Do you have the option to allow BCP IN / TRUNCATE set on this database?  At this point, I would suggest BCPing the data out and then back into a new table.  Specifically,

1)  Do a database dump of your database so that if anything goes wrong, you are not out 8 million pieces of hard-won data.

2)  Use the BCP utility to dump the data to a flat file in your favorite format; I use -c to get a readable format even though it takes up more disk space.

3)  Drop the table.

4)  Create a new table with the proper definition and the new column as the last, allowing NULL.  Do not create indexes, triggers, or referential integrity constraints.

5)  BCP the data in.  I have not tried the experiment; you may need a format file to get the missing column to be loaded as NULL.  Or, it may be sufficient to use the BCP option to identify the end-of-line as end-of-record; I am not sure.

6)  If there was a clustered index, re-create it with the USING SORTED_DATA option.  This option guarantees (sp?) to the server that the data is already in clustered index order.  If it had one before, it was dumped in that order.

7)  Re-create all other indexes, triggers, referential integrity constraints, doodads and gewgaws.

  Try it with 10 rows first.  If you need a format table, you can send me your table definition and two rows of data and I will try to provide it for you.

Alternate solution:  Make a very large transaction log.
Speculative solution:  Drop all indexes, triggers, and refential integrity constraints and do the ALTER TABLE.  If you choose to try this, back up your database first.
0
 
JoseripAuthor Commented:
The  'test_table'  is indexed .
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
bretCommented:
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.
0
 
JoseripAuthor Commented:
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
0
 
Joe WoodhousePrincipal ConsultantCommented:
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.
0
 
JoseripAuthor Commented:

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

0
 
JoseripAuthor Commented:

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.