Ygthkb

Why would the Pakistan airspace closure cancel flights not headed to Pakistan itself?

Can a person refuse a presidential pardon?

Word or phrase for showing great skill at something without formal training in it

How to explain planetary rings pulsating?

If I sold a PS4 game I owned the disc for, can I reinstall it digitally?

Dilemma of explaining to interviewer that he is the reason for declining second interview

Does Improved Divine Smite trigger when a paladin makes an unarmed strike?

What creature do these Alchemical Homunculus actions target?

Am I a Rude Number?

How to acknowledge an embarrassing job interview, now that I work directly with the interviewer?

How should I handle players who ignore the session zero agreement?

How do you funnel food off a cutting board?

How would a Dictatorship make a country more successful?

We are very unlucky in my court

What's a good word to describe a public place that looks like it wouldn't be rough?

Why is working on the same position for more than 15 years not a red flag?

Process to change collation on a database

Quenching swords in dragon blood; why?

Groups acting on trees

Why doesn't "auto ch = unsigned char{'p'}" compile under C++ 17?

Can a dragon be stuck looking like a human?

Why zero tolerance on nudity in space?

Cryptic with missing capitals

Why does a metal block make a shrill sound but not a wooden block upon hammering?

SQL session that is 'never end', always stay in a running status but does not return a result set (Until I kill it)

SQL Server Express acting strangePageIOLatch_Sh waits noticed after tuning an indexGetting 'specified network password is not correct' when trying to change passwordSPIDs stuck indefinitely in suspended stateSQL server 2014 locks up on insert query, while SQL server 2000 runs fineSql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'?SQL Server uses too much CPUIndex rebuild suspended due to IO_COMPLETION wait typeTruncating TEMP table before insert leads to big amount of logical reads on TEMP TABLEVersion store blowing up, but responsible sessions have no open transactions

1

I am a beginner SQL DBA and I experienced a weird thing in SQL server.

Symptom:

1. Session is in Running state (not runnable, not suspended).


2. CPU time is zero, or if it is not, after a while it stops growing.


3. ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.


4. No result set that is returned.


5. They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)

More info:

• This is static database.


• Indexes are not fragmented.


• Statistics are updated


• CPU is not under stress, used 10% only


• Memory is more than enough


• SQL Server 2016 is used

Is there any idea what can be the reason for this behaviour?

I'm very grateful for all the answers.

sql-server sql-server-2016

share|improve this question

edited Dec 7 '17 at 10:55

hot2use

8,35752055

asked Dec 7 '17 at 10:35

MonaMona

61

bumped to the homepage by Community♦ 3 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My bet is with poison waits.
  
  – Erik Darling
  Dec 7 '17 at 10:58
  

  
  
  
  

add a comment | 

1

I am a beginner SQL DBA and I experienced a weird thing in SQL server.

Symptom:

1. Session is in Running state (not runnable, not suspended).


2. CPU time is zero, or if it is not, after a while it stops growing.


3. ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.


4. No result set that is returned.


5. They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)

More info:

• This is static database.


• Indexes are not fragmented.


• Statistics are updated


• CPU is not under stress, used 10% only


• Memory is more than enough


• SQL Server 2016 is used

Is there any idea what can be the reason for this behaviour?

I'm very grateful for all the answers.

sql-server sql-server-2016

share|improve this question

edited Dec 7 '17 at 10:55

hot2use

8,35752055

asked Dec 7 '17 at 10:35

MonaMona

61

bumped to the homepage by Community♦ 3 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My bet is with poison waits.
  
  – Erik Darling
  Dec 7 '17 at 10:58
  

  
  
  
  

add a comment | 

I am a beginner SQL DBA and I experienced a weird thing in SQL server.

Symptom:

1. Session is in Running state (not runnable, not suspended).


2. CPU time is zero, or if it is not, after a while it stops growing.


3. ReadsWritesLogical Reads are zero, or if is not, after a while they stop growing as well.


4. No result set that is returned.


5. They will run until I kill all these sessions. (These can be days, but these queries run within 3 mins usually)

More info:

• This is static database.


• Indexes are not fragmented.


• Statistics are updated


• CPU is not under stress, used 10% only


• Memory is more than enough


• SQL Server 2016 is used

Is there any idea what can be the reason for this behaviour?

I'm very grateful for all the answers.

sql-server sql-server-2016

share|improve this question

edited Dec 7 '17 at 10:55

hot2use

8,35752055

asked Dec 7 '17 at 10:35

MonaMona

61

share|improve this question

edited Dec 7 '17 at 10:55

hot2use

8,35752055

asked Dec 7 '17 at 10:35

MonaMona

61

share|improve this question

edited Dec 7 '17 at 10:55

hot2use

8,35752055

asked Dec 7 '17 at 10:35

MonaMona

61

edited Dec 7 '17 at 10:55

hot2use

8,35752055

edited Dec 7 '17 at 10:55

hot2use

8,35752055

asked Dec 7 '17 at 10:35

MonaMona

61

asked Dec 7 '17 at 10:35

MonaMona

61

1 Answer
1

active

oldest

votes

0

I would check the following

• is the session you normally kill, waiting on something


• is the session you normally kill, being blocked


• is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]

--      ,[kpid]

      ,[blocked]

      ,[open_tran]

--      ,[waittype]

      ,[waittime]

      ,[lastwaittype]

      ,[waitresource]

      ,[name]

      ,CAST([cpu] AS NUMERIC) cpu

      ,[physical_io]

      ,[loginame]

      ,[hostname]

      ,[program_name]

--      ,[hostprocess]

      ,[cmd]

--      ,[nt_domain]

--      ,[nt_username]

--      ,[net_address]

      ,[net_library]

--      ,[uid]

      ,[memusage]

      ,[login_time]

      ,[last_batch]

--      ,[ecid]

      ,[status]

--      ,[sid]

--      ,[context_info]

      ,[sql_handle]

--      ,[stmt_start]

--      ,[stmt_end]

--      ,[request_id]

        ,[text]

FROM [master].[sys].[sysprocesses] p

JOIN SYS.databases d

    ON p.dbid = d.database_id

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

--WHERE status = 'runnable' 

--WHERE name = 'runnable' 

--ORDER BY blocked DESC

--ORDER BY cpu DESC

ORDER BY waittime DESC

share|improve this answer

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you heard of sp_WhoIsActive?
  
  – Erik Darling
  Dec 7 '17 at 18:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
  
  – Mona
  Dec 12 '17 at 10:31
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f192611%2fsql-session-that-is-never-end-always-stay-in-a-running-status-but-does-not-re%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

I would check the following

• is the session you normally kill, waiting on something


• is the session you normally kill, being blocked


• is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]

--      ,[kpid]

      ,[blocked]

      ,[open_tran]

--      ,[waittype]

      ,[waittime]

      ,[lastwaittype]

      ,[waitresource]

      ,[name]

      ,CAST([cpu] AS NUMERIC) cpu

      ,[physical_io]

      ,[loginame]

      ,[hostname]

      ,[program_name]

--      ,[hostprocess]

      ,[cmd]

--      ,[nt_domain]

--      ,[nt_username]

--      ,[net_address]

      ,[net_library]

--      ,[uid]

      ,[memusage]

      ,[login_time]

      ,[last_batch]

--      ,[ecid]

      ,[status]

--      ,[sid]

--      ,[context_info]

      ,[sql_handle]

--      ,[stmt_start]

--      ,[stmt_end]

--      ,[request_id]

        ,[text]

FROM [master].[sys].[sysprocesses] p

JOIN SYS.databases d

    ON p.dbid = d.database_id

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

--WHERE status = 'runnable' 

--WHERE name = 'runnable' 

--ORDER BY blocked DESC

--ORDER BY cpu DESC

ORDER BY waittime DESC

share|improve this answer

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you heard of sp_WhoIsActive?
  
  – Erik Darling
  Dec 7 '17 at 18:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
  
  – Mona
  Dec 12 '17 at 10:31
  
  
  

  
  
  
  

add a comment | 

0

I would check the following

• is the session you normally kill, waiting on something


• is the session you normally kill, being blocked


• is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]

--      ,[kpid]

      ,[blocked]

      ,[open_tran]

--      ,[waittype]

      ,[waittime]

      ,[lastwaittype]

      ,[waitresource]

      ,[name]

      ,CAST([cpu] AS NUMERIC) cpu

      ,[physical_io]

      ,[loginame]

      ,[hostname]

      ,[program_name]

--      ,[hostprocess]

      ,[cmd]

--      ,[nt_domain]

--      ,[nt_username]

--      ,[net_address]

      ,[net_library]

--      ,[uid]

      ,[memusage]

      ,[login_time]

      ,[last_batch]

--      ,[ecid]

      ,[status]

--      ,[sid]

--      ,[context_info]

      ,[sql_handle]

--      ,[stmt_start]

--      ,[stmt_end]

--      ,[request_id]

        ,[text]

FROM [master].[sys].[sysprocesses] p

JOIN SYS.databases d

    ON p.dbid = d.database_id

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

--WHERE status = 'runnable' 

--WHERE name = 'runnable' 

--ORDER BY blocked DESC

--ORDER BY cpu DESC

ORDER BY waittime DESC

share|improve this answer

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you heard of sp_WhoIsActive?
  
  – Erik Darling
  Dec 7 '17 at 18:35
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank for all the answers. I tried lots of queries to figure out what the problem is. All of them havehad runnable status (I checked all of suspicious sessions in sys.dm_exec_sessions but they were running, because there is no runnable status in it, only running). But I experienced that this happens only when I use sqlcmd to run a query. If I use a simple declaration there is no problem. Why is this? Maybe different driver? Thx,
  
  – Mona
  Dec 12 '17 at 10:31
  
  
  

  
  
  
  

add a comment | 

I would check the following

• is the session you normally kill, waiting on something


• is the session you normally kill, being blocked


• is the session you normally kill, in a transaction that someone forgot to end?

you can use the below query to check the first 2. You may be able to check the third if the query gets picked up in the last column.

SELECT [spid]

--      ,[kpid]

      ,[blocked]

      ,[open_tran]

--      ,[waittype]

      ,[waittime]

      ,[lastwaittype]

      ,[waitresource]

      ,[name]

      ,CAST([cpu] AS NUMERIC) cpu

      ,[physical_io]

      ,[loginame]

      ,[hostname]

      ,[program_name]

--      ,[hostprocess]

      ,[cmd]

--      ,[nt_domain]

--      ,[nt_username]

--      ,[net_address]

      ,[net_library]

--      ,[uid]

      ,[memusage]

      ,[login_time]

      ,[last_batch]

--      ,[ecid]

      ,[status]

--      ,[sid]

--      ,[context_info]

      ,[sql_handle]

--      ,[stmt_start]

--      ,[stmt_end]

--      ,[request_id]

        ,[text]

FROM [master].[sys].[sysprocesses] p

JOIN SYS.databases d

    ON p.dbid = d.database_id

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

--WHERE status = 'runnable' 

--WHERE name = 'runnable' 

--ORDER BY blocked DESC

--ORDER BY cpu DESC

ORDER BY waittime DESC

share|improve this answer

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424

SELECT [spid]

--      ,[kpid]

      ,[blocked]

      ,[open_tran]

--      ,[waittype]

      ,[waittime]

      ,[lastwaittype]

      ,[waitresource]

      ,[name]

      ,CAST([cpu] AS NUMERIC) cpu

      ,[physical_io]

      ,[loginame]

      ,[hostname]

      ,[program_name]

--      ,[hostprocess]

      ,[cmd]

--      ,[nt_domain]

--      ,[nt_username]

--      ,[net_address]

      ,[net_library]

--      ,[uid]

      ,[memusage]

      ,[login_time]

      ,[last_batch]

--      ,[ecid]

      ,[status]

--      ,[sid]

--      ,[context_info]

      ,[sql_handle]

--      ,[stmt_start]

--      ,[stmt_end]

--      ,[request_id]

        ,[text]

FROM [master].[sys].[sysprocesses] p

JOIN SYS.databases d

    ON p.dbid = d.database_id

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

--WHERE status = 'runnable' 

--WHERE name = 'runnable' 

--ORDER BY blocked DESC

--ORDER BY cpu DESC

ORDER BY waittime DESC

share|improve this answer

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

edited Dec 8 '17 at 2:55

Michael Green

14.7k83060

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424

answered Dec 7 '17 at 14:17

Chris LumnahChris Lumnah

51424