Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
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

1 Answer 1

up vote 8 down vote accepted

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;
share|improve this answer
    
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
1  
@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
    
It works in the case of CREATE TABLE, but ALTER TABLE would not. As metadata is not versioned. If you wanted to do ALTER TABLE in a transaction then you couldn't use SERIALIZABLE isolation. See: msdn.microsoft.com/en-us/library/ms189122%28SQL.105%29.aspx –  David Roussel Jul 19 '12 at 10:01
    
@DavidRoussel: that says "snapshot" isolation disallows ALTER. "SERIALIZABLE" is OK... I've been using it for DDL for 10 years+ with Red Gate SQL Compare –  gbn Jul 19 '12 at 11:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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