I Executed SQL on database that looked like this:
SET IDENTITY_INSERT table1 ON
GO
BEGIN TRANSACTION
GO
INSERT INTO table1 SELECT ... FROM table2
GO
DELETE FROM table2 ...
GO
COMMIT TRANSACTION
GO
SET IDENTITY_INSERT table1 OFF
GO
Problem is my table was locked on last statement SET IDENTITY_INSERT table1 OFF
, all previous statements took around 6 hours, but my table is locked on this last statement for 1 day now.
In Activity Monitor it has connection that dont execute any command but is head blocker of locking chain, i killed it but nothing changed only that is in rollback state but it's still eating all I/O.
My question is what this session is doing and how to avoid it? It looks like IDENTITY_INSERT is almost costly like full table recreation for some reason, i don't know how this could cause any downsides on performance side.
Additional info: there was not identity collision during insert, bcs i insert operated on lower identity values than initial set on table1
Table design:
CREATE TABLE [dbo].[table1] (
[id_table1] BIGINT IDENTITY NOT NULL,
[id_table3] INT NOT NULL,
[some_id] BIGINT NOT NULL,
[some_time2] DATETIME2 NOT NULL,
[some_time] DATETIME2 NOT NULL,
[some_value] FLOAT (53) NOT NULL,
[some_value2] FLOAT (53) NOT NULL,
[some_value3] TINYINT NULL,
CONSTRAINT [FK_table1_table3] FOREIGN KEY ([id_table3]) REFERENCES [dbo].[table3] ([id_table3]),
INDEX [IX_table1] CLUSTERED ([id_table3] ASC, [some_time] ASC, [id_table1] ASC) WITH (DATA_COMPRESSION = PAGE)
) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE INDEX [IX_table1_some_time] ON [dbo].[table1]
([some_time] ASC, [id_table1] ASC) INCLUDE ([id_table3], [some_value], [some_value2], [some_value3]) WITH (DATA_COMPRESSION = PAGE)
GO
Best Answer
SET IDENTITY_INSERT
is just a session option allowing you to insert identity values explicitly in a subsequent statement. It doesn't do anything to the transaction log. I presume your first transaction wasn't actually committed and now is rolling back.This could happen if you have
implicit_transactions
onOr a possibly more likely scenario is that you were already in an open transaction unknowingly.
This could happen if your original script contained an error between the
begin tran
andcommit
. Executing could then terminate execution leaving an open transaction (If you had usedSET XACT_ABORT ON
any error would have rollbacked the transaction rather than leaving it open.)Then after fixing the error and rerunning the script in the same session the
begin tran
would be called again and increment@@trancount
and theCOMMIT
would just decrement it down to 1, leaving the transaction still open.Rather than killing the session and causing transaction rollback the correct thing to do in that case (assuming you were happy that everything was OK and the original error condition hadn't caused changes that you wouldn't want to commit) would have been to check
@@trancount
andcommit
again as many times as needed (I.e. in the event that there were multiple executions before the error was fixed) until that reached zero.