Kill Process (sp_who2)

Great article on how to kill blocking processes.

When killing any running SQL Server process, it is the durable part of the ACID test we care most about. The durability criterion requires that when a process is killed without completing, all data changed by any uncompleted transactions must be returned to the state it was in before the transaction started. The process of returning data to its original state is called rollback.

What this means is that when you kill a running process in SQL Server, all the uncommented changes made by this process are going to be undone. In most cases, the rollback process happens very quickly and does not have a major impact. In cases where there are a large number of uncompleted transactions, the rollback process can take some time and have a major impact on the performance of the database during that time.

Chronicle's of a Geek...

Identify SPID
To check which processes are affecting the server performance, run the following command:

This shows all the sessions that are currently in the database. These are shown as SPID’s or server process Id’s. The first 50 records that are returned are are system SPIDs and user processes are SPID numbers after 50. When looking into the performace of the server, these SPIDs are most likely causing the issue.

Kill Process
To kill a process just enter the word kill followed by the SPID:

The field Blkby will identify the SPID that is causing any blocks.

Lock Info
To view more details on the lock process just enter the follwing:

View original post

Advertisements

How to run a Windows Service in Console Mode

Run Windows Service as a console program

Visual Studio and the .NET framework make it really easy to create Windows Services. All you have to do is create a new project, select “Windows Service” as your project type and you’re all set. However, debugging Windows Services in Visual Studio can be a big pain. The recommended way is to use InstallUtil to install them, and then restart the service and attach the debugger everytime you want to debug it. I wanted to be able to debug it without the hassle, so here’s what I came up with:

using System;
using System.ServiceProcess;
 
public partial class DemoService : ServiceBase
{
    static void Main(string[] args)
    {
        DemoService service = new DemoService();
 
        if (Environment.UserInteractive)
        {
            service.OnStart(args);
            Console.WriteLine("Press any key to stop program");
            Console.Read();
            service.OnStop();
        }
        else
        {
            ServiceBase.Run(service);
        }
 
    }
    public DemoService()
    {
        InitializeComponent();
    }
 
    protected override void OnStart(string[] args)
    {
        // TODO: Add code here to start your service.
    }
 
    protected override void OnStop()
    {
        // TODO: Add code here to perform any tear-down
        //necessary to stop your service.
    }
}

This will allow you to use your program as either a normal console program or a windows service, with no special builds, #DEBUG directives, command line parameters or anything like that. What it does is in the Main method it checks the “Environment.UserInteractive” property. This will be true when it is run from Visual Studio, or when you just click on the .exe file, but false if it’s being run as a service. When it’s run from Visual Studio or as a standalone program it will keep running until you press a key, then it will call your OnStop method and then terminate.

Two things to watch out for:

  1. You’ll have to right click on your project in Visual Studio, choose Properties and select the Output type as “Console application” for this to work.
  2. If your Main method is not in your service class, you’ll have to add public methods to your class that can start and stop it, for instance add a public void StartConsole(string[] args) that just calls your OnStart, since OnStart and OnStop are protected methods and as such not accessible from other classes.