June 10, 2004 at 12:07 am
Hi all,
Im involved in ETL processing for a datawarehouse project and I created a SQL Server T-sql script to populate a dimension. My problem is that whenever I run the script an error is being given : 'transaction log is full'
The database is configured in SIMPLE RECOVERY options so I believe the transactions are not logged.
Im attaching a copy of the script, maybe someone could help me optimize it by updating/inserting data by batch. For example 10000 records at the same time then next 10000, .....etc. because the number of rows ar 36 million
SET NOCOUNT ON
-- CREATE TEMPORARY TABLE
CREATE TABLE #DIM_SO ([SO_CODE] [char] (8) NULL ,
[CUS_ID] [int] NULL ,
[CUS_ACCT_ID] [bigint] NULL ,
[SVC_ID] [int] NULL ,
[SVC_NUM] [int] NULL ,
[SO_TYPE_ID] [int] NULL ,
[SALES_AGENT_ID] [int] NULL ,
[SALES_AGENT_NUM] [int] NULL ,
[SO_CREATE_DATE] [datetime] NULL ,
[SRV_REQ_NUM] [int] NULL,
[SRV_CODE] [int] NULL)
-- INSERT DATA FROM TABLES TO TEMPORARY TABLES
SELECT TOP 100 MSOSO#, MSOSN, MSOSAG, MSOSDT, MSOCRT, MSOSRN, MSOSC, MSOTOS
INTO #SVORDR00
FROM interim_staging.dbo.icms_SVORDR00
SELECT * FROM #SVORDR00
SELECT TOP 100 CUS_ID, CUS_ACCT_ID, SVC_ID, SVC_CODE
INTO #DIM_SERVICE
FROM DIM_SERVICE
SELECT * FROM #DIM_SERVICE
SELECT TOP 100 [SO_TYPE_ID],[SO_TYPE_CODE]
INTO #DIM_SO_TYPE
FROM DIM_SO_TYPE
SELECT * FROM #DIM_SO_TYPE
SELECT TOP 100 [SALES_AGENT_CODE], [SALES_AGENT_ID]
INTO #DIM_SALES_AGENT
FROM DIM_SALES_AGENT
SELECT * FROM #DIM_SALES_AGENT
SELECT TOP 100 [MCSO#], [MCMCTL]
INTO #PRMESC
FROM interim_staging.dbo.icms_PRMESC
SELECT * FROM #PRMESC
SELECT TOP 100 [MTMTXT],[MTMCTL]
INTO #PRMEST
FROM interim_staging.dbo.icms_PRMEST
SELECT * FROM #PRMESC
--INSERT INTO #DIM_SO
SELECT a.MSOSO#, b.CUS_ID, b.CUS_ACCT_ID, b.SVC_ID, a.MSOSN,
c.SO_TYPE_ID, f.SALES_AGENT_ID, a.MSOSAG, a.MSOSDT, a.MSOSRN, b.SVC_CODE
FROM INTERIM_sTAGING.DBO.ICMS_SVORDR00 a
INNER JOIN DIM_SERVICE b ON b.SVC_CODE = a.MSOSN
INNER JOIN DIM_SO_TYPE c ON c.SO_TYPE_CODE = a.MSOTOS
INNER JOIN INTERIM_sTAGING.DBO.ICMS_PRMESC d ON a.MSOSO# = d.MCSO#
RIGHT OUTER JOIN INTERIM_sTAGING.DBO.ICMS_PRMEST e ON d.MCMCTL = e.MTMCTL
INNER JOIN DIM_SALES_AGENT f ON f.SALES_AGENT_CODE = a.MSOSAG
-- TRANSFORMATIONS
-- INSERT into DIM_SO WITH UPDATE IF EXIST
DECLARE @SOURCE INT
SET @SOURCE = 2
/*Delete rows from target, that are not there in the source table*/
DELETE dbo.DIM_SO
FROM DIM_SO AS t
WHERE NOT EXISTS
(
SELECT 1
FROM #DIM_SO AS s
WHERE s.[SO_CODE] = t.[SO_CODE]
)
/*Insert rows from source, that are not already there in target table*/
INSERT INTO dbo.DIM_SO(SO_CODE, CUS_ID,CUS_ACCT_ID, SVC_ID, SVC_NUM,
SO_TYPE_ID, SALES_AGENT_ID, SALES_AGENT_NUM, SO_CREATE_DATE,
SRV_REQ_NUM, SRV_CODE, SRC_SYS_ID)
SELECT top 100 SO_CODE, CUS_ID,CUS_ACCT_ID, SVC_ID, SVC_NUM,
SO_TYPE_ID, SALES_AGENT_ID, SALES_AGENT_NUM, SO_CREATE_DATE,
SRV_REQ_NUM, SRV_CODE, @SOURCE
FROM #DIM_SO AS s
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.DIM_SO AS t
WHERE s.SO_CODE = t.SO_CODE
)
/*Update matching rows between source and target.
(Matching primary keys, but different non-key values)*/
UPDATE DIM_SO
SET SO_CODE = s.SO_CODE,
CUS_ID = s.CUS_ID,
CUS_ACCT_ID = s.CUS_ACCT_ID,
SVC_ID = s.SVC_ID,
SVC_NUM = s.SVC_NUM,
SO_TYPE_ID = s.SO_TYPE_ID,
SALES_AGENT_ID = s.SALES_AGENT_ID,
SALES_AGENT_NUM = s.SALES_AGENT_NUM,
SO_CREATE_DATE = s.SO_CREATE_DATE,
SRV_REQ_NUM = s.SRV_REQ_NUM,
SRV_CODE = s.SRV_CODE,
SRC_SYS_ID = @SOURCE
FROM dbo.DIM_SO AS t INNER JOIN #DIM_SO AS s
ON s.SO_CODE = t.SO_CODE
WHERE (t.SO_CODE <> s.SO_CODE) OR
(coalesce(t.CUS_ID, -1) <> coalesce(s.CUS_ID, -1)) OR
(coalesce(t.CUS_ACCT_ID, -1) <> coalesce(s.CUS_ACCT_ID, -1)) OR
(coalesce(t.SVC_ID, -1) <> coalesce(s.SVC_ID, -1)) OR
(coalesce(t.SVC_NUM, -1) <> coalesce(s.SVC_NUM, -1)) OR
(coalesce(t.SO_TYPE_ID, -1) <> coalesce(s.SO_TYPE_ID, -1)) OR
(coalesce(t.SALES_AGENT_ID, -1) <> coalesce(s.SALES_AGENT_ID, -1)) OR
(coalesce(t.SALES_AGENT_NUM, -1) <> coalesce(s.SALES_AGENT_NUM, -1)) OR
(coalesce(t.SO_CREATE_DATE, -1) <> coalesce(s.SO_CREATE_DATe, -1)) OR
(coalesce(t.SRV_REQ_NUM, -1) <> coalesce(s.SRV_REQ_NUM, -1)) OR
(coalesce(t.SRV_CODE, -1) <> coalesce(s.SRV_CODE, -1))
-- DROP ALL TEMPORARY TABLES
DROP TABLE #DIM_SO
DROP TABLE #SVORDR00
DROP TABLE #DIM_SO_TYPE
DROP TABLE #PRMESC
DROP TABLE #PRMEST
DROP TABLE #DIM_SALES_AGENT
DROP TABLE #DIM_SERVICE
June 10, 2004 at 1:27 am
Hi,
Is your database option set to AUTO SHRINK.
Andy.
June 10, 2004 at 8:45 pm
yes the database option set to AUTO SHRINK
June 11, 2004 at 6:35 am
As far as I can tell, AUTO SHRINK is not a good option in most cases. It slows down the server - sometimes rather drastically - when the system decides that a huge DB should be shrunk. We have autoshrink OFF on all databases as a rule. When necessary, you can shrink a database manually, but generally it is better to leave the file as it is, with lots of free space... unless you are running out of free space, and then it is better to increase the storage space anyway, than to shrink databases - they will grow eventually anyway, and the problem will reappear at the worst possible time... e.g. when you're about to go home, or to a football match.
If the database did grow that big once, it will do so again, sooner or later. Shrinking needs time and resources, as well as growing does - so quite often it is a waste of both to attempt shrinking. Of course, there can be situations when it is necessary and it helps... IMHO these are rather rare.
HTH, Vladan
June 11, 2004 at 11:17 pm
Check you dimension for error such as count, sum. The data type from your source to destination may not be the same.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply