TSQL Stored Procedure rollback without any warning/error?

Question

This is more of a curiosity, but I noticed a strange behavior today.

Note - there are no explicitly defined transactions in this code.

I have a stored procedure that takes in a user defined table type variable and a string containing another table name.

ALTER proc [TLog].[ClearRecords]
(
    @storesAndDates TLog.DeleteList READONLY,
    @table varchar(max)
)
as
begin

set nocount on;

declare @cmd nvarchar(max)

if @table = 'Coupon'
delete t from Tlog.Coupon t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'DepartmentSale'
delete t from Tlog.DepartmentSale t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'Discount'
delete t from Tlog.Discount t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'FullLineItem'
delete t from Tlog.FullLineItem t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'Media'
delete t from Tlog.Media t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'PriceOverride'
delete t from Tlog.PriceOverride t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'Promotion'
delete t from Tlog.Promotion t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate


else if @table = 'SaleExtension'
delete t from Tlog.SaleExtension t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'Tax'
delete t from Tlog.Tax t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'TicketTotal'
delete t from Tlog.TicketTotal t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'EJInfoAgeID'
delete t from Tlog.EJInfoAgeID t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate

else if @table = 'Info2PromotionTax'
delete t from Tlog.Info2PromotionTax t
    join @storesAndDates sd
        on t.StoreNumber = sd.StoreNumber
        and t.SaleDate = sd.SaleDate
end

I then key off the table name to delete from a particular table using values from the table variable.

This has been running fine in production, being called from an SSIS package using a script task. The data is coming from a RecordSet destination object variable.

 public void Main()
    {
        var table = "TicketTotal";
        var variable = "User::" + table + "Cleanup";

        var da = new System.Data.OleDb.OleDbDataAdapter();
        var dt = new DataTable();
        da.Fill(dt, Dts.Variables[variable].Value);

        var connVal = Dts.Variables["ConnectionString"].Value as string;
        var connStr = connVal.Replace("Provider=SQLNCLI11.1;", "");
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            // Open the connection
            if (conn.State != ConnectionState.Open)
                conn.Open();

            // Prepare a stored procedure command to execute
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[TLog].[ClearRecords]";

            // Add the parameters for the command
            cmd.Parameters.AddWithValue("@storesAndDates", dt);
            cmd.Parameters.AddWithValue("@table", table);

            // Execute the stored procedure
            cmd.ExecuteNonQuery();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }

Today I tried to run it for many days worth of data, so rather than 70 or so records being passed in, almost 900 were passed in. When running from SSMS, the package error'd with Exception has been thrown by the target of an invocation.

When I ran the same package from Visual Studio, I watched the record count and activity from SQL. I saw the delete command running and the records were slowly decreasing. They started at around 1.5 million. At around 500k, the count started going up again until it reached the original amount. At this point Visual Studio just kept spinning thinking it was still running. I let it sit but ended up killing it.

I finally cleared the records by manually calling the stored procedure.

Any ideas as to what happened here? Thanks.

Answer 1

It was the timeout - thanks for the help guys. I should have known better.