SQL Server - How to List and Drop Windows Processes Using CLR (C #)

Views: 850
Reading Time: 5 minutes

Hello people,
Good Morning!

In this post, I will demonstrate how to list and eliminate processes that are running on the SQL Server server using CLR (C #). These features can be especially useful for quickly identifying which user processes are running, which processes are using the most memory or CPU usage, for example.

While developing a PowerShell script, where I was converting Excel (XLSX) files to PDF, the processes were “crashing” waiting for some screen interaction, which as I was running via the command line back on the server, I couldn't finish this interaction. Therefore, with each test, a new process was running on the server waiting for this interaction.

As a result, many processes were running and doing nothing. For this reason, I chose to develop a Stored Procedure in CLR to eliminate these processes from the server. If you would like to know more about using the Process library, see more by visiting my post. SQL Server - How to Run PowerShell and Prompt-DOS (MS-DOS) Scripts Using CLR (C #).

How to list processes in SQL Server using lib Process

In the Stored Procedure below, I use the Process library and the GetProcesses method to return information about processes running on the server in question.

sql-server-how-to-list-kill-windows-process-with-clr-csharp

How to list processes in SQL Server using binary tasklist

This time, I will use the Process library again to execute the binary tasklist, which is native to Windows, and export the information returned from the MS-DOS prompt to CSV and handle the data in C #.

sql-server-how-to-list-kill-windows-process-clr-csharp-2

How to delete processes in SQL Server using lib Process

To eliminate a process from the server to which I am currently connected, we can use the Process library and the Kill () method, informing the process number to be terminated on the server where the Stored Procedure is being executed.

sql-server-how-to-list-kill-windows-process-clr-csharp-3

How to remotely delete processes in SQL Server using the binary taskkill

Unlike the Stored Procedure above, in this example I will use the Process library to execute the native Windows binary, taskkill.exe, to eliminate a process via the PID entered remotely, ie on any server.

sql-server-how-to-list-kill-windows-process-clr-csharp-4

As you can see, in some Stored Procedures I use the Return class for displaying CLR alert messages and database error messages. The source code of this class is available in my post. SQL Server - How to send warnings and error messages to the bank through CLR (C #).

I hope you enjoyed the post and see you soon!

sql server clr c # csharp windows process process list view list view report report kill kill process

sql server clr c # csharp windows process process list view list view report report kill kill process