By Jim Hudson, Senior Technology Evangelist – OakTree Staffing & Training

“How do I find who is using a particular database from the command line?”

There are going to be a number of times when you need to know, quick and dirty, who has a open connection to a SQL Server database. You may want everyone out so you can work on it, or you may just be curious who is using it. The following command will work from any windows computer that has the SQL Tools installed on it:
sqlcmd /Scomputername /Q “sp_who2” | findstr /I databasename
At the risk of pointing out the obvious, replace “computername” and “databasename” with the appropriate values.
For example, running that on my server and looking for database pubs created the following output:
C:\tmp>sqlcmd /S. /Q “sp_who2” | findstr /I pubs
63 sleeping CONTOSO\administrator CONTOSO-DC . pubs AWAITING COMMAND 0 0 07/26 07:46:06 .Net SqlClient Data Provider 63 0
No, it isn’t beautiful – but I know who, what and when they last ran a command. I also know the spid (63). If I know the spid, I can make them go away. More on that next time.
If you’re not following me on Twitter yet, feel free to follow me
@Hudson_OakTree. I’ll be periodically be providing tech tips to hopefully help you out of some binds. Don’t forget to say hi!

__________________________________________________________________________________________________________

If your team could use instructor-led Microsoft Certified IT training that can be taken from absolutely anywhere, look no further than OakTree Staffing & Training.

Learn more about OakTree

Leave a Reply