I have a SQL box that I want to decommission. Before doing so, I want to understand all the applications that may be relying on the machine, rather than just turning it off and hearing people scream.

What is the best way to do this?

I know I can run a SQL trace for a while to get login info etc - is there any other way?

I'd like to know as much about the caller including, IP address or machine name that the request is coming from, the app name, credentials. Mostly I want to somehow know if many apps might be running on a single box calling with similar credentials. I'd also like to be able to know that there are 2+ apps (for example) rather than them just look like one.

If SQL Server itself doesn't give me this, are there other tools at the server level that I can leverage?


You might want to take a look at WireShark. You can install it on the SQL box, and monitor all the incoming requests to the SQL server. I'd narrow the filter in wireshark down to just things making requests on your SQL ports. I'm not sure how long you want it to run for - but once you are done you can then take that trace and run queries against it via filters to get things back like distinct IP's etc...

| improve this answer | |
  • By the way - I failed to mention that wireshark will show ALL traffic from the request. So you will get the IP of the machine, the credentials it's using and then the requests it's actually making - it will look a lot like a SQL trace but you'll get more network information from it. Once you are done running it you can save the whole trace as a file and then either browse through it on that server, or copy it to your machine to go through. (Wireshark must be installed locally if you choose to do that.) – Dave Holland Feb 12 '10 at 2:33
  • That's rather more heavy-weight than running a SQL trace, though, don't you think? – mfinni Feb 12 '10 at 3:44
  • No, I don't. It's about the same weight as running a SQL trace. A big difference is that SQL takes a performance hit (I've seen it be pretty hefty in fact) while running a SQL trace - since SQL server is the one having to perform the operation. Moving the work off of SQL and on to a tool that was designed to do this, along with the results being filterable, searchable, etc would cause me to go this route instead, but that's just me. – Dave Holland Feb 12 '10 at 3:59
  • The last thing I'd recommend is move as many of the items off onto the new SQL server that you know of, and run your trace (however you do it) against it afterward, if possible, so you have less "noise" to go through. – Dave Holland Feb 12 '10 at 4:01

I think that your idea of running a trace for a while is the best idea at this point. There is no other way of finding this info out definitively - you can't know what clients "might" connect, you can only see what clients "do" connect.

Some things may help you guess though:

  1. Make a list of all the databases. Try to figure out that applications are likely to be using them.
  2. Make a list of all of the SQL logins. That may help you with the above point. If everyone is connecting as SA, well, that's one of the reasons that's not a good idea.
  3. Review any of your documentation - good documentation in the future will prevent this from happening again, that is hard to stress enough.

Also, when you decomm it : just unplug the LAN cable for a week before you shut it down. Then, it is literally a 1-second fix to bring it back up if someone DOES need something on it, you don't risk anything like an old disk refusing to spin up again. And of course, back up the whole bloody thing before you unplug it.

| improve this answer | |

What SQL Version?

Post SQL 2005 you can create a logon trigger and log all logon event into a table. The eventdata() and sys.dm_exec_connections will contain some, if not all, the detail's you're looking for.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy