19

I have a login table into which all inserts are done by a single stored procedure.

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

Currently there are about 45500000 rows in that table and I want to direct logging to a different table.

My idea is to use the following 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;

Does this work and has minimal impact to other procedures calling LogInsert?

  • 2
    You don't need the sp_recompile. The procedure cache for any objects which use the object dbo.LogTable will be expired automatically when you rename the object. – mrdenny Aug 5 '11 at 7:11
24

Yes. Transactions apply to DDL and span batches.

I'd do something like this. Note the use of SERIALIZABLE ISOLATION to ensure full isolation and XACT_ABORT which will force a rollback on any error.

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;
  • 1
    Can you link to a reference showing SQL Server transactions apply to DDL and span batches? The obvious BOL pages do not mention this. – Nick Chammas Aug 5 '11 at 17:03
  • 2
    @Nick: I've never looked for one. I know it works as I use it all the time. Up to you to believe me or disprove me or try it for yourself. Of course, transactions are per connection like various the set statements I use. A connection consists of multiple batches. What more do you need? – gbn Aug 5 '11 at 17:10
  • I believe you, but I was hoping for some "official" documentation that enumerates all the actions that are bound to explicit transactions and which ones are not. For example, table variables are not affected by transaction rollbacks (which, to me, violates the Principle of Least Surprise). – Nick Chammas Aug 5 '11 at 18:00
  • 1
    Nearly the same code, but transaction does not work: stackoverflow.com/questions/47868213/… – aleyush Dec 19 '17 at 6:56

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.