Início / dba / Perguntas / 4356
Accepted
bernd_k
Asked: 2011-08-05 22:20:27 +0800 CST

Como usar transações com SQL Server DDL?

  • 772

Eu tenho uma tabela de login na qual todas as inserções são feitas por um único procedimento armazenado.

CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(1, 1) NOT NULL,
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log PRIMARY KEY CLUSTERED  (LogRefnr)
)
go


Create procedure DBO.LogInsert ( @Query varchar(255), @time datetime, @duration int, @SessinID int) as
begin
    Insert into LogTable ( LogRefnr, LogQuery, logQueryDuration, LogSessionID)
    Values  (@Query, @time, @duration, @SessinID);
end;
GO

Atualmente, existem cerca de 45500000 linhas nessa tabela e desejo direcionar o log para uma tabela diferente.

Minha ideia é usar o seguinte script

begin Transaction

exec sp_rename LogTable, LogTableOld;

CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go

sp_recompile LogTable;
go

Commit;

Isso funciona e tem impacto mínimo em outros procedimentos que chamam LogInsert?

sql-server-2008 transaction

1 respostas

  1. Best Answer

    Sim. As transações se aplicam a DDL e abrangem lotes.

    Eu faria algo assim. Observe o uso de ISOLAMENTO SERIALIZÁVEL para garantir isolamento total e XACT_ABORT, que forçará uma reversão em qualquer erro.

    SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin Transaction
GO
exec sp_rename LogTable, LogTableOld;
GO
CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go
EXEC sp_recompile LogTable;
go

Commit;
    • 26

relate perguntas