Results 1 to 5 of 5

Thread: Kill Process

  1. #1
    Join Date
    Oct 2006
    Posts
    17

    Question Unanswered: Kill Process

    Hi Peepz,

    Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.

    Thanks all

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Keezeg
    Hi Peepz,
    Uh, hi, I think...

    Quote Originally Posted by Keezeg
    Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.

    Thanks all
    Sounds like an application using connection pooling (like ASP.NET, WebLogic or any of a myriad of others). I'll use WebLogic as an example: it grabs a set minimum number of connections when the app starts. It periodically tests the connections to verify that they are still "working". It then hands the connection off to a process to complete a database task. When the task is complete, the connection is returned to the pool to be "loaned" out to another thread/process.

    The advantage here is that connection pooling cuts down a lot of overhead associated with building and tearing down connections. The disadvantage is that it can be really hard (from a dba perspective) to tell which SPID is really consuming a lot of resources at the current point in time (since the statistics that are displayed in EM, sp_who and sp_who2 are cumulative from the time the connection is established).

    Instead of looking at the processes, you need to run trace files and examine your locked processes/objects views to determine if there are specific SPIDs that are struggling.

    Regards...er...regardz,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Oct 2006
    Posts
    17
    LOL.. Yea. honestly this is my first time to handle a large set-up for database management. i always see a user still connected and using large amount of resources even that the person is out of the office for more than a day. i also see users that have logged out but their username is still present. by the way how would i create a trace file?

    Thanks and Regards

    Keezeg

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Keezeg
    LOL.. Yea. honestly this is my first time to handle a large set-up for database management.
    Welcome! This is a great place to get answers. Read the sticky posts at the top if you haven't had a chance yet, then head over to the lounge to learn how to make the perfect Margarita (I find that DB problems come into better focus after about the third or fourth!). Pick up any reading materials you can about administration; Kalen Delaney's Inside SQL Server is a good one and SAM's SQL Server 2000 DBA Survival Guide is my favorite.

    Quote Originally Posted by Keezeg
    i always see a user still connected and using large amount of resources even that the person is out of the office for more than a day.
    This sounds a bit different than what I was trying to describe. In general, application servers that use connection pooling would use a login that is not tied to a particular user. Is it possible to drop by the user's workstation and figure out what apps are running?

    Quote Originally Posted by Keezeg
    i also see users that have logged out but their username is still present.
    Ditto the comment above.

    Quote Originally Posted by Keezeg
    by the way how would i create a trace file?
    Use SQL Profiler. You can read about it in SQL Books on Line (be sure to get the updated version from www.microsoft.com/sql). Basically, you start, SQL Profiler, pick the database you want to trace, select the statements you want to log and hit start (or is it run?). Then either save the trace as a file or into a db table (my favorite; but preferably on a non-production server and in a new database). Then you can query the trace file to see what each SPID is doing (and see which queries are taking too long).

    Welcome aboard!


    Regards,

    13AP3
    Last edited by hmscott; 10-05-06 at 08:49.
    Have you hugged your backup today?

  5. #5
    Join Date
    Oct 2006
    Posts
    17
    Quote Originally Posted by hmscott
    Welcome! This is a great place to get answers. Read the sticky posts at the top if you haven't had a chance yet, then head over to the lounge to learn how to make the perfect Margarita (I find that DB problems come into better focus after about the third or fourth!). Pick up any reading materials you can about administration; Kalen Delaney's Inside SQL Server is a good one and SAM's SQL Server 2000 DBA Survival Guide is my favorite.

    Thanks for the references.. i will read the sticky posts.. hahaha i think a couple of shots could focus me better to the problem. like having a date with a not so beautiful chick... having a few drinks she would be looking super hot. hehehehe


    Quote Originally Posted by hmscott
    This sounds a bit different than what I was trying to describe. In general, application servers that use connection pooling would use a login that is not tied to a particular user. Is it possible to drop by the user's workstation and figure out what apps are running?

    basically the applications running was created in VB, its a Monitoring System more on reports generation, data extraction and record entry. i think the application is not disconnecting properly on the database thats why im asking the Application team to check how or what way the programs disconnects when the user exits. there is quite many users of this application and they all do the same thing with the program. heavy report generation.


    Quote Originally Posted by hmscott
    Use SQL Profiler. You can read about it in SQL Books on Line (be sure to get the updated version from www.microsoft.com/sql). Basically, you start, SQL Profiler, pick the database you want to trace, select the statements you want to log and hit start (or is it run?). Then either save the trace as a file or into a db table (my favorite; but preferably on a non-production server and in a new database). Then you can query the trace file to see what each SPID is doing (and see which queries are taking too long).
    i will be going to do this. atleast i can manipulate the data. i can easily query out the data that i wanted to see from table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •