How can a make a sproc execute without a transaction?

I have a clean up process that needs to delete around 8 million rows in a table each day (sometimes more). This process is written in C#, and uses SMO to query the schema for table indexes, disabling them before executing an sproc that deletes in batches of 500K rows.

My problem is that the entire operation is living inside a transaction. The sproc is executed inside a TransactionScope that's configured with the TransactionScopeOption.Suppress (this runs along other things that each start a new TransactionScope), which I thought would not allow a Transaction, and there are explicit commit points inside the sproc.

The C# part of the process could be summarized as this:

        try {
            DisableIndexes(table);
            CleanTable(table);
        }
        finally {
            RebuildIndexes(table);
        }

And the sproc has a loop inside that's basically:

DECLARE @rowCount bigint = 1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

WHILE @rowCount <> 0 BEGIN
    DELETE TOP (@rowsPerBatch) Table 
    WHERE
        ID <= @maxID 

    SET @rowCount = @@rowcount
END

Last night this process timed out half an hour after it started, took half an hour to rollback, and another half hour of index rebuilding...too much down time for zero work...=(

Update: I've run the process on a small sample database (and with a small timeout), and it's not as I thought. Evidently the process is correctly removing rows and making progress as I want. Still, the log is getting consumed. As I'm in the SIMPLE database mode, shouldn't the log not grow in this case? Or is the deletion sproc so 'fast', that I'm not giving the process that actually deletes rows the time it needs to keep the log clean?

---

Depending on your circumstances you may be able to use partitioned views instead of partitioned tables. Something like:

create table A
(
    X int,
    Y int,
    Z varchar(300),
    primary key (X,Y),
    check (X = 1)
)

create table B
(
    X int,
    Y int,
    Z varchar(300),
    primary key (X,Y),
    check (X = 2)
)

create table C
(
    X int,
    Y int,
    Z varchar(300),
    primary key (X,Y),
    check (X = 3)
)

go

create view ABC
as
select * from A
union all
select * from B
union all
select * from C

go

insert abc (x,y,z)
values (1,4,'test')

insert abc (x,y,z)
values (2,99,'test'), (3,123,'test')

insert abc (x,y,z)
values (3,15125,'test')

select * from abc

truncate table c

select * from abc

---

如果我正确理解你的问题，你想要的是一个自动滑动窗口和分区表。

链接地址: http://www.djcxy.com/p/62244.html

上一篇: 计算方法的执行时间

下一篇: 如何在没有事务的情况下执行sproc？