Stored Procedure causing the log file to grow rapidly

Hello experts. I have implemented a new stored procedure onto a server that causes the log file to grow unmanageable.

The store procedure does a few things:
   -Retrieves data from a separate server
   -Changes the data to match the format of our tables
   -Creates a cursor to pass back updates made from our tables back to the other server

Right now without reducing the size of the log file the database will eat up the local disk space. Any ideas to avoid having to truncate the log file everyday?
USE [SafetyOxford]
GO
/****** Object:  StoredProcedure [dbo].[RunM5SOTTTransaction]    Script Date: 05/27/2009 08:12:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RunM5SOTTTransaction]
AS
BEGIN  
 
/* Trouble Tickets and Service Order "Transaction" Load - Safety First Oct. 15, 2008 */
 
--Trouble Tickets--
SET Context_Info 0x55555 
 
DELETE FROM TROUBLE_INTERFACE
 
INSERT INTO M5.dbo.TROUBLE_INTERFACE
                                          (TI_TRANS
                                          , ICON_CODE 
                                          , TI_TYPE         
                                          , TI_TR_ID  
                                          , TI_TRC_ID       
                                          , TI_TRC_CODE     
                                          , TI_TRC_DESC           
                                          , TI_TRR_ID 
                                          , TI_TRR_CODE           
                                          , TI_TRR_DESC     
                                          , TI_PRIORITY_ID        
                                          , TI_PRIORITY_DESC      
                                          , TI_PREFIX_ID    
                                          , TI_PREFIX       
                                          , TI_PRE_DESC     
                                          , TI_TRPT_ID      
                                          , TI_TRPT_CODE    
                                          , TI_TRPT_DESC    
                                          , TI_TRPD_ID            
                                          , TI_TRPD_CODE    
                                          , TI_TRPD_DESC    
                                          , TI_ADD_UID      
                                          , TI_ADD_PLANT_ID       
                                          , TI_ADD_1        
                                          , TI_ADD_2  
                                          , TI_CITY   
                                          , TI_STATE  
                                          , TI_ZIP    
                                          , TI_ZIP4   
                                          , TI_APPOINT_CODE 
                                          , TI_APPOINT_DESC 
                                          , TI_COMP_NUM     
                                          , TI_ACCT_ID      
                                          , TI_CUST_ID            
                                          , TI_NET_ID       
                                          , TI_ACCT_NUM     
                                          , TI_ACCT_NAME    
                                          , TI_CUST_NAME    
                                          , TI_NETNUM 
                                          , TI_NT_CODE      
                                          , TI_CON_NUM            
                                          , TI_TR_NUM       
                                          , TI_NET_ACTIVE_DT      
                                          , TI_NET_INACTIVE_DT    
                                          , TI_NET_BUSRES   
                                          , TI_NET_CAS_STATE      
                                          , TI_NET_DISRECON 
                                          , TI_TR_TAKEN_BY        
                                          , TI_TR_TAKEN_DT        
                                          , TI_TR_PICKUP_BY 
                                          , TI_TR_PICKUP_DT       
                                          , TI_TR_CLEAR_BY  
                                          , TI_TR_CLEAR_DT        
                                          , TI_TR_APPT_TIME       
                                          , TI_TR_FOLLOW_BY 
                                          , TI_TR_FOLLOW_DT       
                                          , TI_TR_TEXT            
                                          , TI_TR_DEVICE_ID       
                                          , TI_TR_CANCEL_BY 
                                          , TI_TR_CANCEL_DT 
                                          , TI_TR_ASSIGNED_TO     
                                          , TI_TR_CAT_ID    
                                          , TI_TRS_ID 
                                          , TI_SPAC_NO            
                                          , TI_SPAC_TERMPT1 
                                          , TI_SPAC_TERMPT2 
                                          , TI_SPAC_TERMPT3 
                                          , TI_SPAC_TERMPT4 
                                          , TI_CREATE_BY
                                          , TI_CREATE_DT    
                                          , TI_STATUS 
                                          , TI_DIRECTION    
                                          , TI_FREEFORMNM1        
                                          , TI_FREEFORMVAL1 
                                          , TI_FREEFORMNM2        
                                          , TI_FREEFORMVAL2 
                                          , TI_CHG_BY
                                          , TI_CHG_DT)
    SELECT                          TI_TRANS
                                          , ICON_CODE 
                                          , TI_TYPE         
                                          , TI_TR_ID  
                                          , TI_TRC_ID       
                                          , TI_TRC_CODE     
                                          , TI_TRC_DESC           
                                          , TI_TRR_ID 
                                          , TI_TRR_CODE           
                                          , TI_TRR_DESC     
                                          , TI_PRIORITY_ID        
                                          , TI_PRIORITY_DESC      
                                          , TI_PREFIX_ID    
                                          , TI_PREFIX       
                                          , TI_PRE_DESC     
                                          , TI_TRPT_ID      
                                          , TI_TRPT_CODE    
                                          , TI_TRPT_DESC    
                                          , TI_TRPD_ID            
                                          , TI_TRPD_CODE    
                                          , TI_TRPD_DESC    
                                          , TI_ADD_UID      
                                          , TI_ADD_PLANT_ID       
                                          , TI_ADD_1        
                                          , TI_ADD_2  
                                          , TI_CITY   
                                          , TI_STATE  
                                          , TI_ZIP    
                                          , TI_ZIP4   
                                          , TI_APPOINT_CODE 
                                          , TI_APPOINT_DESC 
                                          , TI_COMP_NUM     
                                          , TI_ACCT_ID      
                                          , TI_CUST_ID            
                                          , TI_NET_ID       
                                          , TI_ACCT_NUM     
                                          , TI_ACCT_NAME    
                                          , TI_CUST_NAME    
                                          , TI_NETNUM 
                                          , TI_NT_CODE      
                                          , TI_CON_NUM            
                                          , TI_TR_NUM       
                                          , TI_NET_ACTIVE_DT      
                                          , TI_NET_INACTIVE_DT    
                                          , TI_NET_BUSRES   
                                          , TI_NET_CAS_STATE      
                                          , TI_NET_DISRECON 
                                          , TI_TR_TAKEN_BY        
                                          , TI_TR_TAKEN_DT        
                                          , TI_TR_PICKUP_BY 
                                          , TI_TR_PICKUP_DT       
                                          , TI_TR_CLEAR_BY  
                                          , TI_TR_CLEAR_DT        
                                          , TI_TR_APPT_TIME       
                                          , TI_TR_FOLLOW_BY 
                                          , TI_TR_FOLLOW_DT       
                                          , TI_TR_TEXT            
                                          , TI_TR_DEVICE_ID       
                                          , TI_TR_CANCEL_BY 
                                          , TI_TR_CANCEL_DT 
                                          , TI_TR_ASSIGNED_TO     
                                          , TI_TR_CAT_ID    
                                          , TI_TRS_ID 
                                          , TI_SPAC_NO            
                                          , TI_SPAC_TERMPT1 
                                          , TI_SPAC_TERMPT2 
                                          , TI_SPAC_TERMPT3 
                                          , TI_SPAC_TERMPT4 
                                          , TI_CREATE_BY
                                          , TI_CREATE_DT    
                                          , TI_STATUS 
                                          , TI_DIRECTION    
                                          , TI_FREEFORMNM1        
                                          , TI_FREEFORMVAL1 
                                          , TI_FREEFORMNM2        
                                          , TI_FREEFORMVAL2 
                                          , TI_CHG_BY
                                          , TI_CHG_DT 
FROM JAC2k184.CustMast251.dbo.TROUBLE_INTERFACE
WHERE TI_DIRECTION = 'O' and TI_STATUS = 'PEND' 
 
UPDATE JAC2k184.CustMast251.dbo.TROUBLE_INTERFACE  
SET TI_STATUS = 'COMP' 
      , TI_CHG_BY = 'MAPCOM DISPATCH'
FROM JAC2k184.CustMast251.dbo.TROUBLE_INTERFACE as JACTROUBLE_INTERFACE
WHERE JACTROUBLE_INTERFACE.TI_TRANS -1 < (SELECT max(M5.dbo.TROUBLE_INTERFACE.TI_TRANS)
                                                               FROM M5.dbo.TROUBLE_INTERFACE)
 
UPDATE TROUBLETICKET
SET	TroubleTrans = (SELECT max(TI_TRANS) 
					FROM TROUBLE_INTERFACE 
					WHERE TROUBLE_INTERFACE.TI_TR_NUM = TROUBLETICKET.TICKETNUMBER)
 
INSERT INTO TROUBLETICKET (id
                          , TroubleTrans
                          , Flag) 
SELECT DISTINCT TI_TR_NUM
                    , max(TI_TRANS)as TI_TRANS1 
                    , 'PEND'
FROM TROUBLE_INTERFACE
WHERE NOT EXISTS (SELECT TROUBLETICKET.id 
                        FROM TROUBLETICKET 
                        WHERE TROUBLETICKET.id = TROUBLE_INTERFACE.TI_TR_NUM) 
GROUP BY TI_TR_NUM
 
UPDATE TROUBLETICKET 
SET Flag = 'PEND'
FROM TROUBLE_INTERFACE
WHERE EXISTS (SELECT TROUBLETICKET.id 
                        FROM TROUBLETICKET 
                        WHERE TROUBLETICKET.TROUBLETRANS = TROUBLE_INTERFACE.TI_TRANS) 
 
UPDATE TROUBLETICKET    
SET               TicketNumber = TROUBLE_INTERFACE.TI_TR_NUM 
                  , Category = 8
                  --, ServingArea 
                  , CustomerID = TROUBLE_INTERFACE.TI_CUST_ID
                  , TimeOpened = TROUBLE_INTERFACE.TI_TR_TAKEN_DT
                  --, TimeAssigned = TROUBLE_INTERFACE.TI_TR_PICKUP_DT
                  , TimeCleared = TROUBLE_INTERFACE.TI_TR_CLEAR_DT
                  , TimeDisabled = TROUBLE_INTERFACE.TI_TR_CANCEL_DT
			      , CreatedBy = TROUBLE_INTERFACE.TI_TR_TAKEN_BY
                  , ReportedBy = TROUBLE_INTERFACE.TI_TR_PICKUP_BY 
                  , AssignedTo = TROUBLE_INTERFACE.TI_TR_ASSIGNED_TO
                  , JACPlantCode = TROUBLE_INTERFACE.TI_TRPT_ID
                  , JACPlantDetail = TROUBLE_INTERFACE.TI_TRPD_ID 
                  , JACReportedCode = TROUBLE_INTERFACE.TI_TRR_CODE
                  , JACReportedDesc = TROUBLE_INTERFACE.TI_TRR_DESC
                  , JACCompanyCode = TROUBLE_INTERFACE.TI_COMP_NUM
                  , JACNetworkCode = TROUBLE_INTERFACE.TI_NT_CODE
                  , PlantLink = TROUBLE_INTERFACE.TI_ADD_UID
				  , JACTicketID = TROUBLE_INTERFACE.TI_TR_ID
				  , JACPrefix = TROUBLE_INTERFACE.TI_PREFIX_ID
FROM TROUBLETICKET, TROUBLE_INTERFACE
WHERE TROUBLE_INTERFACE.TI_TRANS = TROUBLETICKET.TROUBLETRANS 
      and TROUBLETICKET.Flag = 'PEND' 
 
DELETE FROM SO_NETWORK_INT
 
--SO_NETWORK_INT
INSERT INTO TESTM5.dbo.SO_NETWORK_INT
(
	SONI_SEQ,
	SOI_COMP_NUM,
	SOI_TRANS,
	SONI_CUST_NAME,
	SONI_NET_ID,
	SONI_NETNUM,
	SONI_NT_CODE,
	SONI_NET_DESC,
	SONI_ADD_UID,
	SONI_ADD_1,
	SONI_ADD_2,
	SONI_CITY,
	SONI_ST,
	SONI_ZIP,
	SONI_ZIP4,
	SONI_CAS_STATE,
	SONI_DISRECON   
)
SELECT                              
	SONI_SEQ,
	SOI_COMP_NUM,
	SOI_TRANS,
	SONI_CUST_NAME,
	SONI_NET_ID,
	SONI_NETNUM,
	SONI_NT_CODE,
	SONI_NET_DESC,
	SONI_ADD_UID,
	SONI_ADD_1,
	SONI_ADD_2,
	SONI_CITY, 
	SONI_ST,
	SONI_ZIP,
	SONI_ZIP4,
	SONI_CAS_STATE,
	SONI_DISRECON   
FROM JAC2k184.CustMast251.dbo.SO_NETWORK_INT
 
DELETE FROM TroubleReportCodes
 
INSERT INTO TroubleReportCodes
(
            CODE
            , Description
)
SELECT DISTINCT
      JACReportedCode
      , JACReportedDesc
FROM TROUBLETICKET
WHERE NOT EXISTS (SELECT TroubleReportCodes.Code
					FROM TroubleReportCodes
					WHERE TROUBLETICKET.JACReportedCode = TroubleReportCodes.Code)
AND JACReportedDESC IS NOT NULL AND JACReportedCode IS NOT NULL 
 
INSERT INTO M5.dbo.TROUBLE_INT_TR_COM
                                          (TITC_SEQ
                                          , TI_TRANS
                                          , TIC_TRCOM_ID
                                          , TIC_TRC_TEXT
                                          , TIC_TRC_GRP)
SELECT                                    TITC_SEQ
                                          , TI_TRANS
                                          , TIC_TRCOM_ID
                                          , TIC_TRC_TEXT
                                          , TIC_TRC_GRP
FROM JAC2k184.CustMast251.dbo.TROUBLE_INT_TR_COM as JACTROUBLE_INT_TR_COM
WHERE JACTROUBLE_INT_TR_COM.TI_TRANS + 1 > (SELECT min(M5.dbo.TROUBLE_INTERFACE.TI_TRANS)
                                                               FROM M5.dbo.TROUBLE_INTERFACE
															   WHERE M5.dbo.TROUBLE_INTERFACE.TI_STATUS = 'PEND')
 
INSERT INTO TICKETNOTES(SEQ
						, TicketType
						, UserName
						, NoteType 
						, TroubleTrans
						, Note) 
	SELECT DISTINCT (TITC_SEQ) as TITC_SEQ 
					, 1
					, 'M5/JAC Data Int' 
					, 'NOTE'
					, TI_TRANS
					, TIC_TRC_TEXT
	FROM TROUBLE_INT_TR_COM
	WHERE NOT EXISTS (SELECT TICKETNOTES.id 
                        FROM TICKETNOTES
                        WHERE TICKETNOTES.SEQ = TROUBLE_INT_TR_COM.TITC_SEQ)
	ORDER BY TITC_SEQ 
 
UPDATE TICKETNOTES
SET M5LINKDBKEY = TROUBLETICKET.TicketNumber
FROM TROUBLETICKET
WHERE TICKETNOTES.TroubleTrans = TROUBLETICKET.TroubleTrans
 
UPDATE TroubleTicket
SET TroubleTicket.PRIORITY = 1 
FROM TROUBLETICKET, TROUBLE_INTERFACE
WHERE TROUBLE_INTERFACE.TI_TRANS = TroubleTicket.TROUBLETRANS 
            and TROUBLE_INTERFACE.TI_PRIORITY_ID = 'LOW'
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
SET TroubleTicket.PRIORITY = 2
FROM TROUBLETICKET, TROUBLE_INTERFACE
WHERE TROUBLE_INTERFACE.TI_TRANS = TroubleTicket.TROUBLETRANS 
            and TROUBLE_INTERFACE.TI_PRIORITY_ID = 'MED'
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
SET TroubleTicket.PRIORITY = 3
FROM TROUBLETICKET, TROUBLE_INTERFACE
WHERE TROUBLE_INTERFACE.TI_TRANS = TroubleTicket.TROUBLETRANS 
            and TROUBLE_INTERFACE.TI_PRIORITY_ID = 'HIGH'
            and TROUBLETICKET.Flag = 'PEND'
 
UPDATE TroubleTicket
SET TroubleTicket.PRIORITY = 2
WHERE TroubleTicket.PRIORITY IS NULL OR TroubleTicket.PRIORITY = 0
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
SET [Status] = 1 
WHERE TimeOpened IS NOT NULL
			and TimeOpened <> ''
            and (TimeAssigned = '' or TimeAssigned IS NULL) 
            and (TimeDispatched = '' or TimeDispatched IS NULL)
            and (TimeCleared = '' or TimeCleared IS NULL) 
            and (TimeClosed = '' or TimeClosed IS NULL)
            and (TimeDeferred = '' or TimeDeferred IS NULL)
            and (TimeDisabled = '' or TimeDisabled IS NULL)
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
SET [Status] = 2
WHERE TimeAssigned IS NOT NULL 
			and TimeAssigned <> ''
		    and (TimeDispatched = '' or TimeDispatched IS NULL)
            and (TimeCleared = '' or TimeCleared IS NULL) 
            and (TimeClosed = '' or TimeClosed IS NULL)
            and (TimeDeferred = '' or TimeDeferred IS NULL)
            and (TimeDisabled = '' or TimeDisabled IS NULL)
			
UPDATE TroubleTicket
SET [Status] = 3
WHERE TimeDispatched IS NOT NULL
			and TimeDispatched <> ''
            and (TimeCleared = '' or TimeCleared IS NULL) 
            and (TimeClosed = '' or TimeClosed IS NULL)
            and (TimeDeferred = '' or TimeDeferred IS NULL)
            and (TimeDisabled = '' or TimeDisabled IS NULL)
 
UPDATE TroubleTicket
SET [Status] = 4 
WHERE TimeCleared IS NOT NULL 
            and TimeCleared <> ''
            and (TimeClosed = '' 
				or TimeClosed IS NULL )
            and (TimeDisabled = '' 
				or TimeDisabled IS NULL) 
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
SET [Status] = 5
WHERE TimeClosed IS NOT NULL 
            and TimeClosed <> ''
            and (TimeDisabled = '' or
			TimeDisabled IS NULL)
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
SET [Status] = 7
WHERE       TimeDisabled IS NOT NULL 
            and TimeDisabled <> ''
            and TROUBLETICKET.Flag = 'PEND' 
 
UPDATE TroubleTicket
      SET ReportedCode = TroubleReportCodes.[ID] 
      FROM  TroubleReportCodes
      WHERE rtrim(ltrim(TroubleTicket.JACReportedCode)) = rtrim(ltrim(TroubleReportCodes.Code))
 
 
UPDATE TROUBLE_INTERFACE
	SET TI_STATUS = 'COMP'
	FROM TROUBLETICKET
	WHERE TROUBLE_INTERFACE.TI_TRANS = TROUBLETICKET.TROUBLETRANS
 
 
INSERT INTO M5.dbo.TROUBLE_INT_ERROR
(
	TIERR_SEQ	
	, TIERR_TRANS	
	, TIERR_DT	
	, TIERR_MESSAGE
	, TIERR_NOT_TO	
	, TIERR_NOT_DT	
)
SELECT                                    
	TIERR_SEQ	
	, TIERR_TRANS	
	, TIERR_DT	
	, TIERR_MESSAGE
	, TIERR_NOT_TO	
	, TIERR_NOT_DT	
FROM JAC2k184.CustMast251.dbo.TROUBLE_INT_ERROR as JACTROUBLE_INT_ERROR
WHERE JACTROUBLE_INT_ERROR.TIERR_TRANS + 1 > (SELECT min(M5.dbo.TROUBLE_INTERFACE.TI_TRANS)
                                                               FROM M5.dbo.TROUBLE_INTERFACE
															   WHERE M5.dbo.TROUBLE_INTERFACE.TI_STATUS = 'PEND')
 
UPDATE TroubleTicket
      SET TROUBLETICKET.Flag = 'COMP'
      WHERE TROUBLETICKET.Flag = 'PEND' 
 
---TROUBLETICKETJAC---
 
DELETE FROM TROUBLETICKETJAC
WHERE EXISTS (SELECT TROUBLETICKETJAC.id 
                    FROM TROUBLE_INTERFACE 
                    WHERE TROUBLETICKETJAC.TICKETNUMBER = TROUBLE_INTERFACE.TI_TR_NUM 
                              and TROUBLETICKETJAC.TroubleTrans < TROUBLE_INTERFACE.TI_TRANS)
 
INSERT INTO TROUBLETICKETJAC (id
                                     , TroubleTrans
                                     , Flag) 
SELECT DISTINCT TI_TR_NUM 
                    , MAX(TI_TRANS)as TI_TRANS1 
                    , 'PEND'
FROM TROUBLE_INTERFACE 
GROUP BY TI_TR_NUM 
 
UPDATE TROUBLETICKETJAC
SET TicketNumber = ID
 
 
--TR_ID
UPDATE TroubleTicketJAC
      SET TR_ID = JACTicketID
	  FROM TroubleTicket
      WHERE TroubleTicketJAC.TicketNumber = TroubleTicket.TicketNumber 
		and TroubleTicketJAC.Flag = 'PEND'
 
 
--Assigned 
UPDATE TroubleTicketJAC
      SET Assigned_To = Technician.technicianId 
      FROM Technician 
      WHERE TroubleTicketJAC.TicketNumber = (SELECT TicketID
                                                                  FROM Schedule 
                                                                  WHERE TroubleTicketJAC.TicketNumber = Schedule.TicketID 
                                                                        and Schedule.TechID = technician.ID)
				and TroubleTicketJAC.Flag = 'PEND'
 
UPDATE TroubleTicketJAC
      SET Assigned_To = RIGHT('00' + Assigned_To, 2 )
	  WHERE cast(Assigned_To as int) < 99
				and TroubleTicketJAC.Flag = 'PEND'
 
 
--Picked_Up_by (JAC) / Dispatched (M5)
 
UPDATE TroubleTicketJAC
 SET Picked_Up_Dt = TroubleTicket.TimeDispatched
 FROM TroubleTicket
 WHERE TroubleTicket.TicketNumber = TroubleTicketJAC.TicketNumber 
                  and TroubleTicket.Status = 3
				  and TroubleTicketJAC.Assigned_To IS NOT NULL
				  and TroubleTicketJAC.Flag = 'PEND' 
 
UPDATE TroubleTicketJAC
      SET Picked_Up_By = Technician.Description 
      FROM Technician 
      WHERE TroubleTicketJAC.TicketNumber = (SELECT TicketID
                                                                  FROM Schedule 
                                                                  WHERE TroubleTicketJAC.TicketNumber = Schedule.TicketID 
                                                                        and Schedule.TechID = technician.ID
																		and TroubleTicketJAC.Picked_Up_Dt IS NOT NULL
																		and TroubleTicketJAC.Assigned_To IS NOT NULL)
				and TroubleTicketJAC.Flag = 'PEND'
 
--Plant Code and Detail for clear
UPDATE TroubleTicketJAC
      SET Plant_Code_ID = TroubleTicket.Plant_Code_ID
		, Plant_Detail_ID = TroubleTicket.Plant_Detail_ID
	  FROM TroubleTicket
      WHERE TroubleTicket.TicketNumber = TroubleTicketJAC.TicketNumber 
                  and TroubleTicket.Status = 4 
                  and TroubleTicket.Plant_Code_ID IS NOT NULL  				 
				  and TroubleTicket.Plant_Detail_ID IS NOT NULL
				  and TroubleTicketJAC.Flag = 'PEND'
 
UPDATE TroubleTicketJAC
      SET Plant_Code_ID = TroubleTicket.JACPlantCode
		, Plant_Detail_ID = TroubleTicket.JACPlantDetail
      FROM TroubleTicket
      WHERE TroubleTicket.TicketNumber = TroubleTicketJAC.TicketNumber 
                  and TroubleTicket.Status = 4 
                  and TroubleTicket.JACPlantCode IS NOT NULL  				 
				  and TroubleTicket.JACPlantDetail IS NOT NULL
				  and TroubleTicketJAC.Flag = 'PEND'
 
--Cleared
UPDATE TroubleTicketJAC
      SET Cleared_By = Technician.Description 
      FROM Technician 
      WHERE TroubleTicketJAC.TicketNumber = (SELECT TroubleTicket.TicketNumber
                                                                  FROM TroubleTicket 
                                                                  WHERE TroubleTicketJAC.TicketNumber = TroubleTicket.TicketNumber
																		and TroubleTicket.ModifiedBy = technician.UserName 
                                                                        and TroubleTicket.Status = 4)
			and TroubleTicketJAC.Flag = 'PEND'
UPDATE TroubleTicketJAC
      SET Cleared_Dt = TroubleTicket.TimeCleared
      FROM TroubleTicket
      WHERE TroubleTicket.TicketNumber = TroubleTicketJAC.TicketNumber 
                  and TroubleTicket.Status = 4 
				  and TroubleTicketJAC.Flag = 'PEND'
 
 
 
--Disabled / Canceled 
UPDATE TroubleTicketJAC
      SET Canceled_By = Technician.Description 
      FROM Technician 
      WHERE TroubleTicketJAC.TicketNumber = (SELECT TroubleTicket.TicketNumber
                                                                  FROM TroubleTicket 
                                                                  WHERE TroubleTicketJAC.TicketNumber = TroubleTicket.TicketNumber
																		and TroubleTicket.ModifiedBy = technician.UserName 
                                                                        and TroubleTicket.Status = 7)
					and TroubleTicketJAC.Flag = 'PEND'
 
UPDATE TroubleTicketJAC
      SET Canceled_Dt = TroubleTicket.TimeDeferred
      FROM TroubleTicket
      WHERE TroubleTicket.TicketNumber = TroubleTicketJAC.TicketNumber 
            and TroubleTicket.Status = 7 
			and TroubleTicketJAC.Flag = 'PEND'
 
--ApptTime 
UPDATE TroubleTicketJAC
      SET  Appointment_Dt = Schedule.ApptDate + ' ' + Schedule.ApptTime 
      FROM Schedule
      WHERE TroubleTicketJAC.TicketNumber = Schedule.TicketId 
			and TroubleTicketJAC.Flag = 'PEND'
 
--Cause Code
UPDATE TroubleTicket
      SET JACCauseCode = TroubleCodes.[ID] 
      FROM  TroubleCodes
      WHERE TroubleTicket.FoundCode = TroubleCodes.ID 
            AND TroubleTicket.FoundCode IS NOT NULL
 
UPDATE TroubleTicketJAC
      SET TRC_ID = TroubleTicket.JACCauseCode
      FROM TroubleTicket
      WHERE TroubleTicketJAC.TicketNumber = TroubleTicket.TicketNumber 
            AND TroubleTicket.FoundCode IS NOT NULL
 
 
--Notes
UPDATE TroubleTicketJAC
	SET NotesID = (SELECT MAX(ID)
	FROM TicketNotes
	WHERE TicketNotes.M5LinkDBKey = TroubleTicketJAC.TicketNumber)
WHERE EXISTS
	(SELECT *
	FROM TicketNotes
	WHERE TicketNotes.M5LinkDBKey = TroubleTicketJAC.TicketNumber)
			
 
UPDATE TroubleTicketJAC
	SET Comments = TicketNotes.Note
	FROM TicketNotes
	WHERE TicketNotes.ID = TroubleTicketJAC.NotesID
  
--Service orders--
DELETE FROM SO_INTERFACE
 
--SO_INTERFACE
INSERT INTO M5.dbo.SO_INTERFACE
                                    (
                                    SOI_COMP_NUM      
                                    , SOI_TRANS 
                                    , ICON_CODE 
                                    , SOI_PURPOSE     
                                    , SOI_TYPE  
                                    , SOI_SO_ID 
                                    , SOI_SO_NUM      
                                    , SOI_SO_CLASS_ID 
                                    , SOI_SO_EFF_DT   
                                    , SOI_SO_TAKEN_BY
                                    , SOI_SO_TAKEN_DT 
                                    , SOI_SO_CAN_BY   
                                    , SOI_SO_CAN_DT   
                                    , SOI_CREATE_BY   
                                    , SOI_CREATE_DT   
                                    , SOI_STATUS
                                    , SOI_DIRECTION   
                                    , SOI_COMPLETED_BY      
                                    , SOI_COMPLETED_DT      
                                    , SOI_WG_CODE     
                                    , SOI_WSCH_DT     
                                    , SOI_WSCH_UNITS  
                                    , SOI_WSCH_AM_PM  
                                    , SOI_CHG_BY
                                    , SOI_CHG_DT      
                                    )
SELECT                              SOI_COMP_NUM      
                                    , SOI_TRANS 
                                    , ICON_CODE 
                                    , SOI_PURPOSE     
                                    , SOI_TYPE  
                                    , SOI_SO_ID 
                                    , SOI_SO_NUM      
                                    , SOI_SO_CLASS_ID 
                                    , SOI_SO_EFF_DT   
                                    , SOI_SO_TAKEN_BY
                                    , SOI_SO_TAKEN_DT 
                                    , SOI_SO_CAN_BY   
                                    , SOI_SO_CAN_DT   
                                    , SOI_CREATE_BY   
                                    , SOI_CREATE_DT   
                                    , SOI_STATUS
                                    , SOI_DIRECTION   
                                    , SOI_COMPLETED_BY      
                                    , SOI_COMPLETED_DT      
                                    , SOI_WG_CODE     
                                    , SOI_WSCH_DT     
                                    , SOI_WSCH_UNITS  
                                    , SOI_WSCH_AM_PM  
                                    , SOI_CHG_BY
                                    , SOI_CHG_DT      
FROM JAC2k184.CustMast251.dbo.SO_INTERFACE
WHERE SOI_STATUS = 'PEND' AND SOI_DIRECTION = 'O'
 
UPDATE JAC2k184.CustMast251.dbo.SO_INTERFACE  
SET SOI_STATUS = 'COMP' 
, SOI_CHG_BY = 'MAPCOM DISPATCH' 
 
DELETE FROM ServiceOrder
WHERE EXISTS (SELECT ServiceOrder.id 
                    FROM SO_INTERFACE 
                    WHERE ServiceOrder.TICKETNUMBER = SO_INTERFACE.SOI_SO_NUM 
                              and ServiceOrder.ServiceTrans < SO_INTERFACE.SOI_TRANS)
 
DELETE FROM ServiceOrder
WHERE EXISTS (SELECT ServiceOrder.id 
                        FROM SO_INTERFACE 
                        WHERE ServiceOrder.TICKETNUMBER = SO_INTERFACE.SOI_SO_NUM 
                              and ServiceOrder.ServiceTrans = SO_INTERFACE.SOI_TRANS)
 
INSERT INTO ServiceOrder (id
                                     , ServiceTrans
									 , Flag) 
SELECT DISTINCT SOI_SO_NUM
                    , MAX(SOI_TRANS)as SOI_TRANS1 
					, 'PEND'
FROM SO_INTERFACE 
GROUP BY SOI_SO_NUM 
 
UPDATE ServiceOrder     
SET               TicketNumber = SO_INTERFACE.SOI_SO_NUM
                  , Category = 8
                  , TimeOpened = SO_INTERFACE.SOI_SO_TAKEN_DT
                  , CreatedBy = SO_INTERFACE.SOI_SO_TAKEN_BY
                  , [Status] = 1 
FROM ServiceOrder, SO_INTERFACE
WHERE ServiceOrder.ServiceTRANS = SO_INTERFACE.SOI_TRANS
 
UPDATE ServiceOrder
SET   JAC_ADD_UID = SONI_ADD_UID
FROM ServiceOrder, SO_NETWORK_INT
WHERE ServiceOrder.ServiceTRANS = SO_NETWORK_INT.SOI_TRANS
 
UPDATE ServiceOrder
SET CustomerID = TIERI_CUST_ID 
FROM ServiceOrder, TIER_INTERFACE
WHERE ServiceOrder.JAC_ADD_UID = TIER_INTERFACE.TIERI_ADD_UID
 
----
DELETE FROM SO_INT_SO_COM
 
INSERT INTO M5.dbo.SO_INT_SO_COM
(
	SOISOC_SEQ,
	SOI_COMP_NUM,
	SOI_TRANS,
	SOISOC_GRP,
	SOISOC_TEXT           
)
SELECT                              
	SOISOC_SEQ,
	SOI_COMP_NUM,
	SOI_TRANS,
	SOISOC_GRP,
	SOISOC_TEXT                                      
FROM JAC2k184.CustMast251.dbo.SO_INT_SO_COM
 
INSERT INTO TICKETNOTES(SOSEQ
						, TicketType
						, UserName
						, NoteType 
						, ServiceTrans
						, Note) 
	SELECT DISTINCT (SOISOC_SEQ) as SOISOC_SEQ 
					, 0
					, 'M5/JAC Data Int' 
					, 'NOTE'
					, SOI_TRANS
					, SOISOC_TEXT
	FROM SO_INT_SO_COM
	WHERE NOT EXISTS (SELECT TICKETNOTES.id 
                        FROM TICKETNOTES
                        WHERE TICKETNOTES.SOSEQ = SO_INT_SO_COM.SOISOC_SEQ)
	ORDER BY SOISOC_SEQ
 
UPDATE TICKETNOTES
SET M5LINKDBKEY = ServiceOrder.TicketNumber
FROM ServiceOrder
WHERE TICKETNOTES.ServiceTrans = ServiceOrder.ServiceTRANS
 
UPDATE SERVICEORDER
      SET SERVICEORDER.Flag = 'COMP'
      WHERE SERVICEORDER.Flag = 'PEND' 
 
 
--Dispatch: Cursor to call a stored procedure once for each row in a table that has a pending value  
--Date: Feb. 10, 2009 Author: Safety First
--installed Apr 29 from TestM5 
 
DECLARE @MergeDate Datetime
DECLARE @TR_ID int, 
                  @TRC_ID int, 
                  @Appointment_Status CHAR(5), 
                  @Appointment_Dt DATETIME, 
                  @Plant_Code_ID INTEGER, 
                  @Plant_Detail_ID INTEGER, 
                  @Picked_Up_By VARCHAR(20), 
                  @Picked_Up_Dt DATETIME, 
                  @Cleared_By VARCHAR(20), 
                  @Cleared_Dt DATETIME, 
                  @Follow_Up_By VARCHAR, 
                  @Follow_Up_Dt DATETIME, 
                  @Canceled_By VARCHAR(20), 
                  @Canceled_Dt DATETIME, 
                  @Assigned_To VARCHAR(20), 
                  @Category CHAR(5), 
                  @Solution_Code CHAR(5), 
                  @Comments VARCHAR(254)  
 
SELECT @MergeDate = GetDate()
 
DECLARE ticket_cursor CURSOR FAST_FORWARD FOR SELECT TR_ID 
                  , TRC_ID 
                  , Appointment_Status 
                  , Appointment_Dt 
                  , Plant_Code_ID 
                  , Plant_Detail_ID 
                  , Picked_Up_By 
                  , Picked_Up_Dt 
                  , Cleared_By 
                  , Cleared_Dt 
                  , Follow_Up_By 
                  , Follow_Up_Dt 
                  , Canceled_By 
                  , Canceled_Dt 
                  , Assigned_To 
                  , Category 
                  , Solution_Code 
                  , Comments 
FROM TROUBLETICKETJAC
WHERE IsMerged = 0
 
OPEN ticket_cursor
 
FETCH NEXT FROM ticket_cursor INTO  @TR_ID  
				  , @TRC_ID
                  , @Appointment_Status 
                  , @Appointment_Dt
                  , @Plant_Code_ID
                  , @Plant_Detail_ID 
                  , @Picked_Up_By 
                  , @Picked_Up_Dt 
                  , @Cleared_By
                  , @Cleared_Dt 
                  , @Follow_Up_By 
                  , @Follow_Up_Dt
                  , @Canceled_By 
                  , @Canceled_Dt 
                  , @Assigned_To
                  , @Category
                  , @Solution_Code 
                  , @Comments
 
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [JAC2k184].[CustMast251].[dbo].[dsp_AI_TROUBLE_update_TROUBLE_INT_Ticket_Inbound] 
@TR_ID,
			@TRC_ID,
			@Appointment_Status,
			@Appointment_Dt,
			@Plant_Code_ID,
			@Plant_Detail_ID,
			@Picked_Up_By,
			@Picked_Up_Dt,
			@Cleared_By,
			@Cleared_Dt,
			@Follow_Up_By,
			@Follow_Up_Dt,
			@Canceled_By,
			@Canceled_Dt,
			@Assigned_To,
			@Category,
			@Solution_Code,
			@Comments
 
DECLARE @UPDATESTATUS char(4)
DECLARE @TI_TRANS int
 
select @Ti_Trans = max(ti_trans)
from JAC2k184.CustMast251.dbo.trouble_interface m
where m.TI_TR_ID = @TR_ID
 
select @UpdateStatus = TI_STATUS
from JAC2k184.CustMast251.dbo.TROUBLE_INTERFACE m
WHERE @TR_ID = m.TI_TR_ID
and m.TI_Trans = @Ti_Trans
						
UPDATE TROUBLETICKETJAC
SET
IsMerged = 1,
MergeDate = @MergeDate
WHERE @TR_ID  = TR_ID
AND @UpdateStatus = 'COMP'
                              
FETCH NEXT FROM ticket_cursor INTO  @TR_ID  
				  , @TRC_ID
                  , @Appointment_Status 
                  , @Appointment_Dt
                  , @Plant_Code_ID
                  , @Plant_Detail_ID 
                  , @Picked_Up_By 
                  , @Picked_Up_Dt 
                  , @Cleared_By
                  , @Cleared_Dt 
                  , @Follow_Up_By 
                  , @Follow_Up_Dt
                  , @Canceled_By 
                  , @Canceled_Dt 
                  , @Assigned_To
                  , @Category
                  , @Solution_Code 
                  , @Comments
END
 
CLOSE ticket_cursor
DEALLOCATE ticket_cursor
 
 
UPDATE TroubleTicketJAC
      SET Flag = 'COMP'
      WHERE Flag = 'PEND' 
 
END

Open in new window

adagray42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
since you are not using transactions, it means that you have statements that update many rows and you must break those statement
start off by replacing each delete from table_name to truncate table table_NAME
if that does not help you will need to break the update and insert statements into little pieces

the log files grows since you have statements that require writing big amounts of data to the log,
if each statement will effect less rows, less data will be written each time
0
momi_sabagCommented:
and since the log works in a circular way, if less data is written, the log should not increase it's size significantly
0
BrandonGalderisiCommented:
That's because the following command:
DELETE FROM TROUBLE_INTERFACE

logs individual row deletes from the table.  If you are deleting ALL records, then issue the truncate command.

truncate table TROUBLE_INTERFACE

this will log a truncate of the table instead of individual row deletes.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Indeed your procedure has lot of Operations involved which makes Server to log more.

Steps to resolve this issue would be to

1. Create a maintenance plan to run every one hour or so depending upon the runs of this procedure to take a transactional log backup.
2. Once transactional log backup is taken, shrink the log file using

DBCC SHRINKFILE (urdbname_Log, 1000)

where 1000 specifies 1000 MB which is the size of the log file.
0
BrandonGalderisiCommented:
rrjegan:

shrinking a log file, which will grow again will do nothing other than cause it to fragment on disk.  Ideally the log file should be backed up at regular intervals (as suggested) or set to simple recovery mode.

If you would like more information on managing your log file properly, see:

http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx
0
Aneesh RetnakaranDatabase AdministratorCommented:
replace these tables with temp Tables
TROUBLE_INTERFACE
SO_NETWORK_INT
TroubleReportCodes
TROUBLETICKETJAC
SO_INTERFACE
ServiceOrder
SO_INT_SO_COM

REPLACE 'DELETE FROM ' with ' TRUNCATE TABLE  '
0
BrandonGalderisiCommented:
aneesh.

I think it's a little risky to suggest replacing a bunch of tables with temp tables when you don't know if/how they are used after this process completes executing.  

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Brandon,
  Hope that is what I suggested him to take transactional log backups in step no 1 and then shrink log files in step no 2. I mentioned him to do steps both 1 and 2 not 2 alone right.
0
BrandonGalderisiCommented:
You shouldn't be shrinking the logs though.  You should determine their appropriate size for your needs, configure the backups around that, and monitor for the need to expand them.  Shrinking and growing repeatedly will cause the files to fragment on the physical disk.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Brandoni,
> think it's a little risky to suggest replacing a bunch of tables with temp tables when you don't know if/how they are used after this process completes executing.  

if you check the code, you can see, it is doing a 'DELETE FROM ' operation on those tables , thats why i made that suggession.
I think he need to make use of more temp tables / table variables before performing operations on the real tables.



Aneesh
0
BrandonGalderisiCommented:
But they are doing a delete/insert.  Not an insert/delete.  So that means that once this code is done, the data still exists in those tables.  Who are we to suggest a change that would cause that data to not be accessible?
0
adagray42Author Commented:
I should note this stored procedure runs at the top of every minute.

Momi / Brandon, I will utilize truncate where delete from was used before

Aneesh, some of the tables are used by a separate application as Brandon feared but the majority of those could be used as temporary tables as you suggested. The other major goal is to make this SP more efficient, which I imagine this change would be a good first step.

Brandon, I will set up a job to back up the log file at regular intervals with only concern of how often it would need to run in order to keep the size in check.
0
BrandonGalderisiCommented:
"with only concern of how often it would need to run in order to keep the size in check."

That's a trial/error to see what works.  

Let me ask this.  First I will say that I did not look at all the code.  There's just too much "stuff".  If this is being run every minute, presumably so that it is available for select, why not doing the calculations in the select.
0
adagray42Author Commented:
It's a lot to look at but rightfully as it does many different things, some much needed comments would help some.

I don't have access to modify the application that uses the tables for select. All the data from the foreign database must be changed to the format the application expects, sometimes for example using an id from a look up table. At the same time I must keep a record of the ticket update to date in a separate table with the same format the external database uses so I can pass it back and update their database.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.