1

Topic: Why idle time INSERT hangs up?

Good evening;
Initial data - is the project in SSDT, there we conduct basis development.
For  we do circuit comparing, then  a script, we correct and it is launched.
And so, the script fulfills normally in dev, but prod hangs up tightly. The request is fulfilled infinitely, and KILL does not help.
Also there is nothing , all is extreme simple for SSDT:
[spoiler]

CREATE TABLE [dbo]. [tmp_ms_xx_Countries] (
[id] TINYINT IDENTITY (1, 1) NOT NULL;
[short_name] NVARCHAR (2) NOT NULL;
[full_name] NVARCHAR (255) NOT NULL;
[region_id] TINYINT NOT NULL;
[region_extra_id] TINYINT NOT NULL;
[customer_id] NVARCHAR (50) NOT NULL;
[year_start] INT CONSTRAINT [DF_Countries_year_start2] DEFAULT (datepart (year, getdate ())) NOT NULL;
[timezone] VARCHAR (255) NOT NULL;
[INSERT_BY] VARCHAR (128) DEFAULT (original_login ()) NOT NULL;
[RV] ROWVERSION NOT NULL;
[phone_code] VARCHAR (3) NULL;
[trunk_prefix] VARCHAR (2) NULL;
CONSTRAINT [tmp_ms_xx_constraint_PK_Countries1] PRIMARY KEY CLUSTERED ([id] ASC);
CONSTRAINT [tmp_ms_xx_constraint_IX_Countries1] UNIQUE NONCLUSTERED ([short_name] ASC) WITH (IGNORE_DUP_KEY = ON);
CONSTRAINT [tmp_ms_xx_constraint_IX_Countries_11] UNIQUE NONCLUSTERED ([full_name] ASC) WITH (IGNORE_DUP_KEY = ON)
);
IF EXISTS (SELECT TOP 1 1
FROM [dbo]. [Countries])
BEGIN
SET IDENTITY_INSERT [dbo]. [tmp_ms_xx_Countries] ON;
INSERT INTO [dbo]. [tmp_ms_xx_Countries] ([id], [short_name], [full_name], [region_id], [region_extra_id], [customer_id], [year_start], [timezone], [phone_code], [trunk_prefix])
SELECT [id];
[short_name];
[full_name];
[region_id];
[region_extra_id];
[customer_id];
[year_start];
[timezone];
[phone_code];
[trunk_prefix]
FROM [dbo]. [Countries]
ORDER BY [id] ASC;
SET IDENTITY_INSERT [dbo]. [tmp_ms_xx_Countries] OFF;
END

[/spoiler]
Monitoring I see that it INSERT.
SPID,percent_complete,running_time,est_time_to_go,est_completion_time,command,blocking_session_id,DBID,LASTWAITTYPE,DBNAME,QueryText
59,0,0 hour (s): 41min: 46 sec, 0 hour (s): 0min: 0 sec, 2017-10-02 17:48:16.713,KILLED/ROLLBACK,0,5,SOS_SCHEDULER_YIELD, ferratum_intelligence, INSERT INTO...
And it already the second time, the first hung 2 days before rebooted the server, only it helped.
Dev:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 6:34:29 PM
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200:) (Hypervisor)

Prod:

Microsoft SQL Server 2012 (SP1) - 11.0.3373.0 (X64)
Jun 29 2013 9:15:12 PM
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Where to dig?

2

Re: Why idle time INSERT hangs up?

It is interesting, why sufferers save on ?

3

Re: Why idle time INSERT hangs up?

How many records are interposed?

4

Re: Why idle time INSERT hangs up?

iap;
29.

5

Re: Why idle time INSERT hangs up?

Diklevich Alexander;
And application continues to write to the table [dbo]. [Countries] in script runtime? Similar changes normally do beforehand stopping all activity in basis.

6

Re: Why idle time INSERT hangs up?

flexgen;
The table static, anybody there does not write and read even rarely.

7

Re: Why idle time INSERT hangs up?

Diklevich Alexander;
Any large table updates structure for you. All do not show.

8

Re: Why idle time INSERT hangs up?

Alexander wrote:

the table static, anybody there does not write and read even rarely.

On a screenshot transaction in state KILLED\ROLLBACK who beat it? In broad gulls of the server something is visible?

9

Re: Why idle time INSERT hangs up?

Vladislav Kolosov;
I show all.

10

Re: Why idle time INSERT hangs up?

flexgen;
So I also beat after it hung 30 minutes on INSERT.
After that began to hang in state KILLED/ROLLBACK.

11

Re: Why idle time INSERT hangs up?

Exceptional with curiosity...
What for to check record existence before ?
Like as superfluous operation with ...

IF EXISTS (SELECT TOP 1 1
FROM [dbo]. [Countries])
BEGIN
SET IDENTITY_INSERT [dbo]. [tmp_ms_xx_Countries] ON;
INSERT INTO...

12

Re: Why idle time INSERT hangs up?

Alexander wrote:

Vladislav Kolosov;
I show all.

Here all is explicit not. Explicitly still change of structure of the table, a modulation of the data reversely,  the temporary table.
Show to us all script.

13

Re: Why idle time INSERT hangs up?

So in sys.dm_os_waiting_tasks when it is all hangs?

14

Re: Why idle time INSERT hangs up?

Massa52 wrote:

it is exceptional with curiosity...
What for to check record existence before ?
Like as superfluous operation with ...

IF EXISTS (SELECT TOP 1 1
FROM [dbo]. [Countries])
BEGIN
SET IDENTITY_INSERT [dbo]. [tmp_ms_xx_Countries] ON;
INSERT INTO...

This script is generated by means of a Visual Studio, option Schema Compare was used.

15

Re: Why idle time INSERT hangs up?

CrazHunt wrote:

not all Here is explicit. Explicitly still change of structure of the table, a modulation of the data reversely,  the temporary table.
Show to us all script.

All script in the first post.

16

Re: Why idle time INSERT hangs up?

Alexander wrote:

flexgen;
So I also beat after it hung 30 minutes on INSERT.
After that began to hang in state KILLED/ROLLBACK.

Here still the question - and what is done by function original_login ()?

[INSERT_BY] VARCHAR (128) DEFAULT (original_login ()) NOT NULL;

Where she addresses?

17

Re: Why idle time INSERT hangs up?

flexgen wrote:

it is passed...
Here still the question - and what is done by function original_login ()?

[INSERT_BY] VARCHAR (128) DEFAULT (original_login ()) NOT NULL;

Where she addresses?

ORIGINAL_LOGIN (Transact-SQL)

18

Re: Why idle time INSERT hangs up?

As they say - a century live, a century study. Did not know that there is a such.

19

Re: Why idle time INSERT hangs up?

flexgen;
Since 2005 very necessary thing.
For now it is possible all successively .
More shortly, in all DDL-triggers I recommend to include;
Whether and that is not enough who of whom  and from his name soils

20

Re: Why idle time INSERT hangs up?

CrazHunt;
I launch this piece and it hangs up.

21

Re: Why idle time INSERT hangs up?

Alexander wrote:

CrazHunt;
I launch this piece and it hangs up.

If in the table of only 29 records, full does not complicate to make you  dataful?

22

Re: Why idle time INSERT hangs up?

I remember, once for a long time we had such glitch.
Any request was sent on the server, there there was any error;
The message on it showed Windows on the server in the form of a modal window.
And while the administrator does not close this window, all sit something wait.
When such disgrace was found out, the server overloaded, and is remembered, all was corrected.
Thin particulars I do not remember. It was very much, very much for a long time...
And! Recalled that I had a remote access on the north, and I continually pushed there button Ok in a modal window smile))