Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query is consuming hoge transaction log


Query is consuming hoge transaction log

Author
Message
Knowledge Hunter
Knowledge Hunter
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 403
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44344
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, MVP, M.Sc (Comp Sci)
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


Knowledge Hunter
Knowledge Hunter
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 403
Thanks Gail for the quick response.
But since the script is running fine in production why is creating such a mess in Development?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44344
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search