[Bug] Stop-DbaProcess fails to stop processes on a database with in-memory OLTP enabled

Torak1980 commented 4 years ago

Environmental information

PowerShell version:

Name Value

PSVersion 5.1.14409.1005
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14409.1005
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

dbatools Module version:

Name : dbatools Path : C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.105\dbatools.psd1 Version : 1.0.105

Name : dbatools Path : C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.92\dbatools.psd1 Version : 1.0.92

SQL Server:

Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

Report

We have an automated process that restores a database everyday. The database has in-memory OLTP enabled. As part of the script I run stop-dbaprocess against this database to ensure there are no active connections. However, i get this error WARNING: [07:00:20][Stop-DbaProcess] Couldn't kill spid 6. | Only user processes can be killed.

What is running on this database to cause the restore to fail every morning ComputerName : XXXXXXXXXXX InstanceName : SQL2016 SqlInstance : XXXXXXXX Spid : 6 Login : sa LoginTime : Host : Database : GIA_Staging BlockingSpid : 0 Program : Status : background Command : XTP_OFFLINE_CKPT Cpu : 0 MemUsage : 0 LastRequestStartTime : LastRequestEndTime : MinutesAsleep : ClientNetAddress : NetTransport : EncryptOption : AuthScheme : NetPacketSize : ClientVersion : HostProcessId : IsSystem : True LastQuery :

Host used

[X ] powershell.exe

Errors Received

WARNING: [07:00:20][Stop-DbaProcess] Couldn't kill spid 6. | Only user processes can be killed.

Steps to Reproduce

create a database with in memory OLTP enable. Backup that database and restore to a separate instance. Leave the database alone for 24 hours run stop-dbaprocess -Sqlinstance XXXXX -Database XXXXX

Expected Behavior

All user process to stop

Actual Behavior

An error, as above

wsmelton commented 4 years ago

Can you kill those sessions using other methods?

Torak1980 commented 4 years ago

Hi Shawn,

I've tried several difference ways and no, I can't.

However, deleting the database through SQL Server management studio works even though the process is still running

On 2020-04-30 17:30, Shawn Melton wrote:

Can you kill those sessions using other methods?

-- You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub [1], or unsubscribe [2].

Links:

[1] https://github.com/sqlcollaborative/dbatools/issues/6507#issuecomment-621927464 [2] https://github.com/notifications/unsubscribe-auth/APM2TALGC6YZ5D7FZA2WOYTRPGKPVANCNFSM4MVTZXEQ

wsmelton commented 4 years ago

This would be by-design as the error you are getting is coming from SQL Server. SQL Server does not allow you to kill processes that are system owned, just as the message states.

The process involved is likely internal threads that are used by SQL Server for the in-memory feature working with that database particularly. I don't use this feature and have not looked at it in some time. Quick search on the command you show running and it does seem to be something to do with that feature.

Overall the command itself is doing what it should be doing and letting you know why we couldn't kill that process.

In regards to the process you use for restoring I would recommend looking at other options when you are working with those databases. You might try setting the database offline.

dataplat / dbatools