?
Solved

Transaction log is being filled up

Posted on 2007-07-25
11
Medium Priority
?
417 Views
Last Modified: 2008-09-27
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 .
0
Comment
Question by:Joserip
8 Comments
 

Author Comment

by:Joserip
ID: 19571758
The  'test_table'  is indexed .
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 19574808
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
 
LVL 10

Expert Comment

by:bret
ID: 19575652
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Joserip
ID: 19579915
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 19580027
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
 

Author Comment

by:Joserip
ID: 19581238

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
 

Author Comment

by:Joserip
ID: 19581287

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
 
LVL 1

Accepted Solution

by:
PeretzBenRafael earned 500 total points
ID: 19593510
 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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Often times it's very very easy to extend a volume on a Linux instance in AWS, but impossible to shrink it. I wanted to contribute to the experts-exchange community a way of providing a procedure that works on an AWS instance. It can also be used on…
LinkedIn blogging is great for networking, building up an audience, and expanding your influence as well. However, if you want to achieve these results, you need to work really hard to make your post worth liking and sharing. Here are 4 tips that ca…
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question