Why is My Transaction Log So Big

Question

Why is My Transaction Log So Big

homebrew01

SSC Guru

Points: 55548
More actions
January 22, 2010 at 7:12 pm

#156603

Here's a different variation of this question.
I have a table "Prod_Table" with 139 million rows. Data space + Index space = 28 Gig
I need to extract 52 million into another table, but I only need the RWA_ID column.
I created a temp table with just 1 column:
CREATE TABLE [dbo].[Temp_Table](
[RWA_ID] [decimal](10, 0) NOT NULL)
Here's the Prod_Table:
CREATE TABLE [dbo].[Prod_Table](
[Call_ID] [int] IDENTITY(1,1) NOT NULL,
[CallStart_DateTime] [datetime] NOT NULL,
[CallEnd_DateTime] [datetime] NOT NULL,
[Time_ID] [int] NOT NULL,
[RWA_ID] [decimal](10, 0) NOT NULL,
[Disp_ID] [int] NOT NULL,
[Termed] [char](1) NOT NULL,
[DNC_Flag] [char](1) NOT NULL,
[ComcastCB_Flag] [char](1) NOT NULL,
[Aspect_ID] [int] NOT NULL,
[XID] [varchar](8) NOT NULL,
[Form_ID] [int] NOT NULL,
[Call_Type] [char](1) NOT NULL,
[CallBack_Datetime] [datetime] NULL,
[CallBack_RWATime] [datetime] NULL,
[Callback_ID] [varchar](8) NULL,
[CallBack_FName] [varchar](15) NULL,
[CallBack_LName] [varchar](20) NULL,
[CallBack_Phone] [char](10) NULL,
[CallBack_Ext] [char](5) NULL,
[CallBack_Comments] [varchar](255) NULL,
[Rep_Comments] [varchar](255) NULL,
[Phone] [char](10) NULL)
Then I ran this script:
insert into Temp_Table
select RWA_ID from Prod_Table with (nolock)
where disp_id = 32
It's running, and my log file has grown to 42 Gig !?!?
The temp table itself is only 1 Gig of data & 1 Gig of index space. Why did the log grow so much from inserting such a relatively small amount of data ? Should I have dropped the index on my temp table first ?
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Yes - should have dropped that index from the temp table first

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

I re-ran it without the index, but the log file was still 23 Gig .... seems awfully big compared to my 1 Gig temp table that was loaded.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

It might be trying to do a sort operation while you do that. What is your clustered index on?

it does seem pretty big - better than it was, but still big.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

CirquedeSQLeil (1/22/2010)
It might be trying to do a sort operation while you do that. What is your clustered index on?
it does seem pretty big - better than it was, but still big.

There are no clustered indexes on the source table. It does have 5 non-clustered indexes on:

Call_ID

Disp_ID

Form_ID

RWA_ID

Time_ID

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Try running 'select RWA_ID from Prod_Table with (nolock)

where disp_id = 32' on main db and see how much tie it takes exactly.

MJ