What are best practices for safely permanently deleting a database?

We have an "organic" environment, meaning people piled code on code for ten years with minimal oversight or documentation. The server I use has several databases which I believe are no longer being used; I'd love to delete them and leave just the three I actually use.

At the reckless extreme, I could disable these databases and wait for someone to scream; at the other I could leave them running forever "just in case". What steps have you found valuable in identifying whether a server is being used, and how?

Also, what steps would you recommend to ensure that, as one moves forward in disabling systems, that they remain conveniently reversible for a period of time (e.g., rename objects rather than deleting them outright)?

Thanks!

You could try to set up a trace that only captures connections and to what database they connect to. I would leave this running for a bit and then make sure nothing is connecting to it.

One problem with that would be if you have some code opening up on the master db but calling another DB within the code. I'm not sure how bad the code is that is pointing to your DBs.

I'd also query all your jobs and make sure none are pointing to that DB

You could also use SQL audit if you have the right version of SQL (2008 R2 enterprise).

You could also use logon triggers to update a table when someone logged on to that DB. This would show you if anything was connecting to that DB.

StackExchange Database Administrators Q#3139, answer score: 6