The Developer DBA toolkit

Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:

  1. It saves a lot of time because it means that you don’t always have to remote on to the server to view debugging information or execute local commands. e.g. Is XYZ service running? Who’s logged on to the box? Run that SSIS package.
  2. You can view results from a different security context which is often the key for successfully debugging connectivity issues.

I always keep a script just for the purpose which I consider to be an essential part of any database developer/DBA toolkit. This is a selection of tasks that crop up again and again and will often save you a lot of time. Especially if you use xp_cmdshell.

This post list a selection of tasks that come up again and again together with the necessary command lines to speed up the process!

1. Who’s remotely logged on to the server?

How many times do you try to log on to a remote server to administer it, only to find there is someone already logged on. The command you need to run from a windows server is:

QUERY SESSION /SERVER:MY-SERVER-NAME

The result will look something like this:

SESSIONNAME       USERNAME                 ID  STATE   TYPE
 rdp-tcp#32        Don.Duck                  0  Active  rdpwd               
 rdp-tcp                                 65536  Listen  rdpwd               
 console                                     2  Conn    wdcon               
 rdp-tcp#26        Mick.Mouse                1  Active  rdpwd               
                   Daffy.Duck                4  Disc    rdpwd

2. I need to kick off an inactive (or active) session

So you’ve discovered from the step above that there is a disconnected session (id 4) on the server and want to reset it:

RESET SESSION 4 /SERVER:MY-SERVER-NAME

3. What AD groups do I belong to?

Why does your colleague have access to something, but you don’t? What group membership does the service account have? A simple way to get a list of Group memberships:

gpresult > c:\outputfile.txt

And another way, but this one truncates the groupnames so if your organisation uses a long naming convention, it can be tricky:

net user /DOMAIN Mick.Mouse

4. What about local group membership?

net localgroup ADMINISTRATORS

5. What services are running on your server?

tasklist /svc

This will output a list something like:

Image Name                     PID Services                                    
========================= ======== ============================================
System Idle Process              0 N/A                                         
System                           4 N/A                                         
smss.exe                       376 N/A                                         
csrss.exe                      432 N/A                                         
winlogon.exe                   460 N/A                                         
services.exe                   508 Eventlog, PlugPlay                          
lsass.exe                      520 HTTPFilter, Netlogon, PolicyAgent,          
                                   ProtectedStorage, SamSs 

6. Stop service XYZ?

NET STOP XYZ

7. Start service XYZ?

NET START XYZ

There are obviously many more useful commands than this. What do you use on a regular basis?

Cheers
Frank

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.