1

On production server we have 125 GB free space. The SSIS package is doing some delete in Transaction. e.g. Insert 3 columns which are mainly business keys into a staging table and then deleting the data from facts while joining with staging table so it is an Execute Sql task with four delete queries.

On server there is 125 GB free space. Once package execution has started and execution comes at point where delete happens, the disk space gets full due to log file increased from 84 MB to 125 GB and then the package fails with message

login failed for user as well as not able to undo that delete operation.

Question

  1. Is the huge increase in log file is due to transaction we implemented or the Delete statement in SQL Server itself logs a huge amount of data ?

  2. How to solve this problem? what changes can i make in SSIS to improve this behavior ?

Below are the details of size of two tables out of four using sp_spaceused

rows                
    7361248                 
reserved
    28744520 KB
data
    9072848 KB
index_size
    19444600 KB
unused
    227072 KB
------------    
rows                
    932549              
reserved
    1038752 KB
data
    979192 KB
index_size
    45920 KB
unused
    13640 KB

Query:

Delete fpi from DWH.Factptimg fpi
inner join DWH.Dimpt dp on fpi.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer

GO 

Delete fph from DWH.Facttxhstry fph
inner join DWH.Dimpt dp on fph.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer

GO 


Delete fph from DWH.DimTxtran fph
Inner join DWH.Dimcourse dc on dc.DimcourseID = fph.DimcourseID
inner join DWH.Dimpt dp on dc.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer

GO


Delete df from DWH.DimFld df
Inner join DWH.Dimcourse dc on dc.DimcourseID = df.DimcourseID
inner join DWH.Dimpt dp on dc.DimptID = dp.DimptID
Inner Join [DWH].stgTrtchanges stg on stg.PatientSer = dp.ctrPatientSer
  • Radhi how large are the various tables that are involved. – Lawrage Mar 3 '16 at 7:55
  • @Lawrage i added details for two tables in question. – Radhi Mar 3 '16 at 8:48
  • Can you provide the SQL from Execute Sql task? The row counts does make it seem like the log file shouldn't have to grow very much, but the way the query is written can make a huge difference. – nateirvin Mar 4 '16 at 15:40
  • @nateirvin the query added in question – Radhi Mar 9 '16 at 6:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.