How To Kill All Database Processes in SQL Server
SQl management studio provides lot of facilities to manage SQL processes.
The “Activity monitor screen” which will show you some very useful and important information about SQL processes.
It will show you all active processes, there status, number of open transactions for each process, the application by which the process is generated, Wait time CPU and memory utilisation, and the host name, means the host or the machine by which the process is initiated etc.
If a process is hanging, it will show the process icon with an hour glass.
Refresh it several times, sometimes the icon will turn to sleep mode if the process completes its execution.
If the icon remains an hour glass, the process may be hanging. Use other info as well to decide if the process is really hanging.
If you double click on the process ID it will show the SQL stamen subjected to the execution of the process.
If a process is hanging you can identify which application has originated the process, which SQL stamen is hanging and even more from which machine or the host the process is initiated
If some application users are complaining about uneven slowness, uneven timeouts etcetera, the reason behind may be one or more processors are hanging.
What can you do now? You can restart the SQL server or the service which will disconnect all databases and all applications disturbing all the users.
Or otherwise you can take the database to off line and bring it back to online. Or you can disconnect all connections to the database. The disturbance may be lesser than the previous case but still lot of applications and users can be disturbed, especially in an industrial setup.
And still you do not know the real reason for the slowness or the malfunctioning as well.
So what can you do?
Go and examine the processors. Identify exactly which process is hanging. And most probably you will be identifying how and why.
So you can find permanent solution.
But the immediate solution is you can kill the troubling process or the few. It will solve the issue with minimum disturbance immediately.
After identifying the correct process, you can right click on the process ID and click on ‘Kill process’ button. It will kill the selected process.
You can kill processors using SQL statements and, using SQL commands you can kill all processors at once as well.
Code for Killing Processes Individually
USE master
go
DECLARE @dbname sysname
SET @dbname = 'YourDB'
EXECUTE ('KILL 53')
Code for killing all Processes
USE master
go
DECLARE @dbname sysname
SET @dbname = 'YourDB'
DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid @spid
END
Auf dieser Seite können Sie das Online-Video How To Kill All Database Processes in SQL Server mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Rohan Mehtha 16 Februar 2016 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 12,323 Mal angesehen und es wurde von 30 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
