Category: How-To

Articles that provide step by step instructions on how to perform a task.

Posted on

A Microsoft SQL Server Database Administrator’s Daily Bread: Killing Processes

A few years ago, I started on a path to become a database administrator and read many articles on the Internet on how to break into that field.  There were a lot of articles out there, but I don’t remember finding many that offered practical knowledge (i.e. advice on what problems database administrators might encounter on a day-to-day basis).  Please note that at the time I am writing this article, I am not officially a database administrator within my organization, but I do perform database related duties and wanted to share my experience with you.

Working for a hosting company, our customers do not have complete access/control over their databases which is understandable since you are offloading management duties to the hosting company.  Learning how to correctly kill a process can be essential to getting a database back in working order, and here’s a list of the occasions where I’ve had to do it:

1) The database had open transactions causing a database lock.
2) The database somehow got set to SINGLE_USER mode, the customer was still logged in, and it needed to be set back to MULTI_USER mode (This works 9 times out of 10.  Sometimes, the person is logged in with SQL Server Management Studio, and you won’t be able to kill the process.  In that case, you just ask the customer politely to log off.)
3) Trying to bring a database online/offline, and it gets stuck.
4) The database Auto-Close setting got set to true or on.
5) Database Attach/Backup/Restore jobs which get stuck.

There are probably many ways to do this, but I found this to be the simplest to understand:

1) Make sure you’re logged in with an account that has SQL Server Administrator privileges using SQL Server Management Studio.
2) Open a New Query window and type in the following replacing DatabaseName with the name of the database.

USE MASTER
GO

SELECT DB_ID(N'DatabaseName') AS [Database ID]

3) It should return a number in the Results windows.

DatabaseID Results Window

4) Enter the next set of T-SQL commands in the same or new window replacing # with the Database ID number from the Results window.  If you are doing this in the same window, remember to highlight only the following code and then hit F5 or the Execute button so that you will not be executing other T-SQL statements.

USE MASTER
GO

SELECT * FROM SYSPROCESSES WHERE DBID = #

5) You should get a set of results like the one below.

SPIDs

6. To kill the process, look at the spid number and type in the following in the Query window replacing SPID# with the actual number.

KILL SPID#

For open transactions, if the number is greater than 0, then that most likely is the process that is causing the problem, and you can just kill that one.  Familiarize yourself with the column names and their meanings as they will help you identify which processes you should be removing.  Sometimes, I’ve had to clear all the processes associated with the database so that I could execute other commands to perform maintenance on it.  As with anything, use the kill command with great caution and care as you don’t want to be killing system related processes that could crash the SQL Server engine or cause corruption to your databases.

Posted on

Installing MongoDB 2.6 and Configuring It as a Windows Service (Windows 8.1)

It took me a few days to figure it out after reading many blog posts that were only helpful in setting up prior versions and appeared to be carbon copies of each other, but I hope my post will prove to be a little more accurate than the others.  Please note these instructions are specific to MongoDB version 2.6 and Windows 8.1, and this is what worked for me.

1. Download the source files from The Official MongoDB website and get the Windows 64-bit zip or msi version.
2. Extract the contents of the zip file or use the msi installer to place the files in C:\MongoDB
3. Create a data directory to store the database data (e.g. C:\MongoDB\data\db).
4. Create a log directory to store the log files (e.g. C:\MongoDB\log).
5. Using Notepad or any text editor, create a configuration file containing the following and save it in C:\MongoDB\mongod.cfg

systemLog:
  destination: file
  path: C:\MongoDB\log\mongo.log
net:
  bindIp: 127.0.0.1
  port: 27017
storage:
  dbPath: C:\MongoDB\data\db
  directoryPerDB: true

If you want to learn more about your configuration options, the official documentation can be found at the MongoDB website.  Note that it is only starting with version 2.6 that you can use YAML markup.  Prior versions use a different syntax.

6. Contrary to what other blog posts tell you and even MongoDB’s official documentation, you cannot install version 2.6 via this syntax:

C:\MongoDB\bin\mongod.exe --config C:\MongoDB\mongod.cfg --install

because of a bug.  I found a work around thanks to this post in the MongoDB User Forum.  You can use Windows’ native sc.exe command to create the Windows service and here’s the exact syntax I used to create it successfully:

sc create MongoDB binPath= "C:\MongoDB\bin\mongod.exe --config=C:\MongoDB\mongod.cfg --service" displayname= "MongoDB 2.6 Standard Server" start= auto

Note that you will need to run this command at the command prompt with a user that has Administrator privileges.  Make sure you right click the command prompt icon and select Run as Administrator if you are launching it from the desktop.  You can remove the service at any time by typing in this command at the command prompt:

sc delete MongoDB

7. To start the service, type this in at the command prompt:

net start MongoDB

To stop the service, type this in at the command prompt:

net stop MongoDB

Or type in services.msc at the command prompt to bring up the Services GUI.

MongoDB Windows Service Screen Shot

You can Start/Stop/Restart the service from here.  If you get an Error 109: The pipe has ended when stopping the service, you can safely ignore it.  It’s just a cosmetic error according to my research on the web, and I noticed that I only get it if I start and stop the service immediately.  I didn’t get the error after the service had been running for a while.  You should also be able to use the same methodology if you’re having problems with other versions of Windows and MongoDB as well.

[Update October 2016] I just wanted to provide an update that these instructions (general guidelines) still work for the latest version, 3.2.10 and Windows 10. Also, if you get the error The service is not responding to the control function, you might want to check the following:

1) The \data\db directory exists and is being pointed to correctly in the configuration file.
2) The \log directory exists and is being pointed to correctly in the configuration file.
3) You do not use sc create to install to a path that has spaces. This is a bug that was brought to my attention on StackOverflow and documented in JIRA. Note: A day later, I discovered this official documentation on MongoDB’s website. I haven’t tested it yet, but you can try escaping the string by adding “\ to encapsulate the spaces in the path if you do want to try to install to a path with spaces (e.g. Program Files).