Finding unused databases?

Question

Finding unused databases?

GregLyon

SSCommitted

Points: 1946
More actions
January 22, 2007 at 11:39 am

#171063

I've been searching (here and elsewhere) to find best practices regarding indentifying unused databases on a server, and what to do with them once identified. So far I've come up short. I'd sure appreciate advice or links you can offer me.
My case: I've been put in charge of a server which was obviously a development server that was never cleaned up...just put into production. I guess that 20 of the 25 databases are not used (they're all backed up to disk and most of the hourly transaction logs are the same size for a given db). Yet I don't want to find out after the fact that the 'real' database is using one or more of these others.
What would you do under these circumstances? If not for space issues I'd consider just leaving it all alone...
Thanks,
Greg.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you don't want to actually delete anything, because dev databases might contain the most cutting edge stored proc some developer has been working on or something, so you want to keep backups of everything, but still trim the herd.

here's what I do which is a pretty definitive way, because it causes complaints::

backup everything, and then do the following.

sp_who2 and see what databases are in use right now.

any not on the list, detach them. wait a week or two and see which databases people complain about. re-attach any that someone complains about.

after waiting a decent amount of time for developers to return back to their projects, vacations, etc, anything that was detatched and noone whines about, zip the mdf's and put them away for long term storage.

after they are there for 6 months, since they were developement db's, you could delete them or dump them to tape backup.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GregLyon SSCommitted Points: 1946 More actions · Answer 2

Thank you for the quick reply Lowell. That looks good enough for me. I'm guessing that I'll hear pretty quickly if I detach the wrong db

Greg.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

Better than detaching them, I would just take them offline. Are there any current users in the databases. The other thing you can do is setup a profiler trace and see if anyone ever connects to the databases.

Tom

GregLyon SSCommitted Points: 1946 More actions · Answer 4

The more I think about this the more I realize I need to do something like what you've suggested Tom. I know the following: There is no active development taking place currently. There is a 'demo' database which often sits idle but sees occasional use. And the others...I'm not sure.

I found/modified the following SQL statement to have a look at what's going on. I suppose I could set it up to run periodically, dumping results to a table, or I could do a SQL trace. I haven't done a trace in quite a while, not sure which would be easier to implement.

SELECT

cpu

, hostname

, nt_username

, loginame

, program_name

, [name] as 'dbname'

, [filename]

FROM master..sysprocesses sp LEFT OUTER JOIN master..sysdatabases sd ON sp.dbid = sd.dbid

where [name] not in ('master','model','msdb','tempdb','')

order by [cpu] DESC

I was also able to do some other cleanup of the disk so there's not such a rush to get rid of these things.

Thanks again!

Greg.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 5

I usually just fire up profiler on my local machine and give it the databases that I want to look at. You'll have to add the dbid to the default profiler object. Another thing that you can look at are the users that have access to the databases, and what users have those db's as default db's. If there are only a handfull in each db I would take access from them away and change all default db's.

Tom

GregLyon SSCommitted Points: 1946 More actions · Answer 6

I probably should have mentioned right off the bat that this SQL Server is also a webserver, and that the only users that regularly connect are the web site itself...and me. It's also behind it's own firewall, so my only access to it is via RDP or http.

To complicate everything, there are maybe a half dozen potential developers (though I believe they're not active), but they would all connect the same way as me...rdp (even using the same login) or http to test...All of which hobbles the detective process somewhat!

MohammedU SSCoach Points: 19644 More actions · Answer 7

create a sql trace using extended procedure and fire it as a job and write the output to a file...

Upload the trace file to a table and validate dbs accesses by users..

MohammedU
Microsoft SQL Server MVP

Roust_m SSCoach Points: 17410 More actions · Answer 8

I use a job that logs all runnable processes into a table every minute. Later I query the table and this helps not only to find out what databases are not used but also what activity typicaly happens on each database (read/write), which processes consume to much cpu and when etc. etc.