Query is consuming hoge transaction log

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Query is consuming hoge transaction log

Query is consuming hoge transaction log

Rate Topic

Display Mode

Topic Options

Author

Message

Knowledge Hunter

Posted Monday, January 07, 2013 8:25 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 08, 2013 6:52 AM
Points: 82, Visits: 344

The following script is takeing hrs to complete and consuming 100GB of Transaction log in Dev environment,

DECLARE @THIS_CALENDAR_YR_START_DT INT

SET @THIS_CALENDAR_YR_START_DT = (SELECT CAST(THIS_CALENDAR_YR_START_DT AS INT)
FROM ECDI)

TRUNCATE TABLE EISRCYR
INSERT INTO EISRCYR
SELECT
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
,SUM(QUANTITY) QUANTITY
,SUM(DEALER_SALES) DEALER_SALES
FROM VERSC
WHERE TIME_SID >= @THIS_CALENDAR_YR_START_DT
GROUP BY
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,

UNION ALL

DECLARE @THIS_CALENDAR_YR_START_DT INT

SET @THIS_CALENDAR_YR_START_DT = (SELECT CAST(THIS_CALENDAR_YR_START_DT AS INT)
FROM ECDI)

select
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,
,SUM(A.QUANTITY) QUANTITY
,SUM(A.DEALER_SALES ) DEALER_SALES
FROM VEB A
,VETC B
,VECC C
,VETP D
,VECP E
WHERE A.TYPE2_CUSTOMER_SID = B.TYPE2_CUSTOMER_SID
AND B.CUSTOMER_NO = C.CUSTOMER_NO
AND A.TYPE2_PRODUCT_SID = D.TYPE2_PRODUCT_SID
AND D.PRODUCT_NO = E.PRODUCT_NO
AND C.MAJOR_BUY_GROUP_CD <> 'RET'
AND A.TIME_SID >= @THIS_CALENDAR_YR_START_DT
GROUP BY
Col1,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11,
Col12,
Col13,
Col14,
Col15,
Col16,
Col17,
Col18,
Col19,
Col20,
Col21,
Col22,
Col23,
Col24,
Col25,
Col26,

TRUNCATE TABLE EISRC
INSERT INTO EISRC
SELECT * FROM TEISRPYR
UNION ALL
SELECT * FROM EISRCYR

DELETE EISRC
WHERE TIME_SID = 1

The same query is running in production within 20 mins.

Dev environment and production both has 3 GB RAM.
Nos of processor is double in Dev environment.
Dev environment also hosts SQL 2008 instance along with SQL 2000.

Can suggest how can I fix the issue and what is the reason for the issue?

Post #1403649

GilaMonster

Posted Monday, January 07, 2013 8:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

The script you've posted doesn't even parse.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ','.
Msg 134, Level 15, State 1, Line 76
The variable name '@THIS_CALENDAR_YR_START_DT' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Line 106
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 74
Incorrect syntax near the keyword 'DECLARE'

It uses a lot of log space because you're doing a lot of inserts and deletes. Unless you can figure a way so that you don't need to insert so much and delete so much, you won't be able to do much about the log space.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403672

Knowledge Hunter

Posted Monday, January 07, 2013 8:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 08, 2013 6:52 AM
Points: 82, Visits: 344

Thanks Gail for the quick response.
But since the script is running fine in production why is creating such a mess in Development?

Post #1403678

GilaMonster

Posted Monday, January 07, 2013 9:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 37,660, Visits: 29,911

Knowledge Hunter (1/7/2013)

But since the script is running fine in production why is creating such a mess in Development?

Err... *rolls dice*

More data
Less memory
Older stats
Different recovery model
etc
etc

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403685

« Prev Topic | Next Topic »

Permissions