 |
|
| SQL Server Tips: |
|
|
|
DATABASE DEVELOPMENT
Stored procedure: Kill all processes
CADE BRYANT
06.19.2001
Rating: --- (out of 5)
|
You're a SQL Server DBA, and you notice that *something* is hogging your system's resources. You don't want to take the time to wade through dozens of processes and individually kill the ones you think might be causing the problem. Ever wish SQL Server had a "panic button" for terminating all user processes, without stopping/restarting the server? This stored procedure I created does exactly that.
Note that when you run it, it may return several error messages. That's because, as it cycles through the numbers representing process IDs, many of the numbers may not be valid user process IDs. This does not prevent the stored procedure from doing its job; it will still terminate all user processes (except your own), thus freeing up system resources.
create procedure sp_kill_all
as
declare @i int
declare @sql varchar(100)
set @i = 1
while @i < 100
begin
set @sql = 'kill ' + cast(@i as varchar(4))
exec (@sql)
if @@error = 0
begin
print 'terminated ' + cast(@i as varchar(4))
end
set @i = @i + 1
end
For More Information
|
|
Rate this Tip
|
To rate tips, you must be a member of SearchSQLServer.com.
Register now
to start rating these tips. Log in if you are already a member.
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
|
|
|
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
