asked on

Optimize union insert (runaway trans log) - table size, nolock and 'nolog'?

Hi all,

Stable SQL2000 setup on W2000. 7g db on drive (100meg trans log - Simple log mode) with another 10g free. Working with 3 similar tables, trying to union insert them to one table. Existing 3 tables no more than 2-3g, 15mil rows. Using stored proc like this:

INSERT INTO [CLAIMS_A_2002_04]
( CLAIM_TYPE, CL_YEAR, FPTYPE, DT_recipnum, DOS1, DOS2, PDC, SDC, TOTPAID, PROVTYPE, PTPPS, CPT1, CPT2, CPT3x)
SELECT 'A' AS 'CLAIM_TYPE', left([DOS1],4) as 'CL_YEAR', 'FP' as 'FPTYPE', [DT_recipnum], DOS1, DOS2, PDC, SDC, TOTPAID, PROVTYPE, PTPPS, CPT12, CPT3, CPT4x
FROM [acg_temp].[dbo].[FTP A Claims FP 2002_04] with (nolock)
UNION ALL
SELECT 'A' AS 'CLAIM_TYPE', 2002 as 'CL_YEAR', 'NFP' as 'FPTYPE', [DT_recipnum], DOS1, DOS2, PDC, SDC, TOTPAID, PROVTYPE, PTPPS, CPT1, CPT2, CPT3x
FROM [acg_temp].[dbo].[FTP A Claims nonFP 2002] with (nolock)
UNION ALL
SELECT 'A' AS 'CLAIM_TYPE', 2003 as 'CL_YEAR', 'NFP' as 'FPTYPE', [DT_recipnum], DOS1, DOS2, PDC, SDC, TOTPAID, PROVTYPE, PTPPS, CPT1, CPT2, CPT3
FROM [acg_temp].[dbo].[FTP A Claims nonFP 2003] with (nolock)
UNION ALL
SELECT 'A' AS 'CLAIM_TYPE', 2004 as 'CL_YEAR', 'NFP' as 'FPTYPE', [DT_recipnum], DOS1, DOS2, PDC, SDC, TOTPAID, PROVTYPE, PTPPS, CPT1, CPT2, CPT3
FROM [acg_temp].[dbo].[FTP A Claims nonFP 2004] with (nolock)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Procedure seems to start off okay but never finishes: last run blew up db to 10g with trans log of 7g. Usually ends with messge 'truncate log' or 'can't write to log' etc. Is there anything I can add/rewrite to get these tables into one table?? Figured the 'nolock' would do it, but now out of ideas. Is there any nolog switch etc? I can keep everybody out of the db while q is running, and have full backup so not concerned about rollback etc. Why is it writing so much to the logs?? really would help if this would work!

thanks!!

wes

new_wes

ASKER

PS - Row size on empty target table is at most 200char (below), so expecting 15m rows * 200 char = 3g of data?? What am i missing that the db/log would grow by almost 3x that much and still not complete?

CREATE TABLE [CLAIMS_A_2002_04] (
      [CLAIMSAKEY] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
      [CLAIM_TYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CL_YEAR] [bigint] NULL ,
      [FPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DT_recipnum] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DOS1] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DOS2] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PDC] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [SDC] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TOTPAID] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PROVTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [PTPPS] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CPT1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CPT2] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [CPT3x] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ASKER CERTIFIED SOLUTION

Nightman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

new_wes

ASKER

ha! no kidding about the drivespace. thanks!! trying now. any ideas why the log file seems to grow X times larger than the transactions it is logging??

Nightman

Probably the index changes as well as the data. Don't know what your indexing strategy is, but the indexes are also changing, not just the data,

new_wes

ASKER

hmm - no indexing yet - just imported thsee from text . . .

but success with the queries!! total runtime less than 1hr. thanks very much, makes moving forward much easier.

Nightman

Glad I could help.