How do I identify a specific process? 1

[softhemc]

I've spent the last couple of hours using google, unfortunately to no avail.

I know the name of the database (and the server), but I need to Kill any active processes on that database when I run a specific SSIS package. (The Kill action will be an SQL task within the package)

Can anyone show me how to identify the active processes on a particular database?

Thanks in advance.

 

[gmmastros]

you can use "sp_who2" to get a list of processes. This will return many rows of data. Anything with a SPID less than 50 is an internal process that shouldn't be messed with.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[softhemc]

Thanks George, but I don't understand how to use sp_who or sp_who2, as I don't seem to be able to use select to extract the required data.

Visually I can see what I need, but how through a query?

 

[gmmastros]

sp_who2 is a procedure so you wouldn't want to select from it. Instead, you'll want to execute it. Since you are doing this from within SSIS, I may not be able to help you any further because I don't use SSIS. What I mean is... I'm sure there is a way to do it, but I don't know how. Try googling "SSIS execute stored procedure" and see if it helps.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

Start with this:

SELECT spid 
FROM master.dbo.sysprocesses
WHERE status = 'sleeping' AND cmd = 'AWAITING COMMAND'

Gather the SPID(s) into a temporary table and then loop through the table to KILL each SPID. You can also use login_time if you want to only kill those that are sleeping and have been logged in for a specific time (ie: > 30 minutes).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

SQLBill, what is the difference between Master.dbo.sysprocesses and sys.sysprocesses?

Thanks


P.S. Sorry softhemc for hi-jacking your thread but it seemed a good place to ask the question.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[simian336]

You can also insert sp_who in to a table.


CREATE TABLE myspwho (
spid int
, ecid int
, [status] varchar(50)
, loginame varchar(255)
, hostname varchar(255)
, blk varchar(50)
, dbname varchar(255)
, cmd varchar(255)
, request_id int
)
GO

INSERT INTO myspwho EXEC sp_who

SELECT * FROM myspwho
GO

Simi

 

[SQLBill]

They are the same, but in different versions. dbo.sysprocesses is the SS2000 version and sys.sysprocesses is SS2005/SS2008.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[djj55]

Thanks

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[softhemc]

I haven't been in the office for the last few days.

Thank you all for your help.

djj555, no problem - I'm glad you found this thread useful.

SQLBill, I had found sysprocesses, however it doesn't seem to contain the database name, which is needed for this task. I need to Kill all processes on a particular database.


simian336, that is exactly what I was looking for. Thanks again.

 

[softhemc]

I've just managed to get this to work, after thoroughly testing it.

KILL process id as a variable doesn't work, however using dynamic SQL does.

 

[softhemc]

From the point of view of Killing the offending processes, everything works perfectly.

However, there is a consequence. If one or more of the processes was because a user had been logged in via a WebSphere Portal - the entire portal is crashed when the process is killed.

Is there is any other way in which I can solve this problem?

Basically I am trying to backup a database from one physical SQL Server and attempting to restore it to another physical SQL Server - through an automated SSIS Package. The backup is straightforward as is moving the backup file. Restore is the problem when there are connections to the target database - even if the connections are inactive. This is why I needed the help on finding which processes to kill.

 

[simian336]

2 thoughts...

1 there is software out there that will let you do an online backup.

2 it might be possible to stop the webshere service.

Simi

 

[SQLBill]

You've got to kill the connections before you can restore the database - you already know this, it is the 'how' that is the issue.

Your options are:

1-Kill the processes, which then crashes the portal.
2-Find a time where the database is not use and only restore it then.
3-stop the portal services (as Simi suggested), but that could affect other users.

In the perfect world, all applications would drop connections when they are no longer in use and users would actually close out applications/connections when they aren't using them. But that is going to happen.

#2 is your best bet, however, if there is no 'down time' or you have to restore during different times of the day, then you have to live with killing connections (by the kill command or stopping the portal services) and taking the chance of crashing the portal....and if you stop the services you might kill active connections to other databases.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[softhemc]

The workaround that we have at the moment is to manually stop the offending processes, then run the package. Not perfect, but at least it prevents the WebSphere Portal from crashing.

 

[gmmastros]

Perhaps you can try setting the database to single user mode prior to the restore and then setting it back to multi-user mode when the restore is done.

Take a look here for ideas on how to do that:
thread183-1239542

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[softhemc]

Thank you, gmmastros.

I can't test this now, but I should be able to on Thursday. I'll report back then.

 

[gmmastros]

Of course.... that thread was talking about droping a database, which you certainly don't want to do.

Denis had a link in that thread to a blog post he wrote a bunch of years ago. The link is:

http://sqlservercode.blogspot.com/2006/01/kill-all-connections-to-your-sql.html

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom