Every now and then I get an email asking me if I can remove some of the databases on server ‘X’ that have not been used in quite some time. I am always curious as to how someone has already ascertained that the database has not been used so I ask them the question and I have lost count of the number of times I have been greeted with this response:
“Oh, that’s simple, I just look at the modified date for the data files in Windows.”
Um…what? Look, let’s leave the database administration to the professionals and you can just go back to whatever it was you were doing before. I beg of you, do not delete or drop a database based upon the modified date on the data files in Windows. Then again, go ahead, and drop me an email when you need me to help you clean things up, my rates are quite reasonable.
Just to be clear on this: that date only changes on specific actions. If the file was recently expanded (or auto-grow), or shrunk (shudder), or perhaps the database was restored (or reattached like a finger). All of those actions would change the modified date in Windows.
SELECT statements would not alter the modified date. Nor would a DELETE, or an UPDATE, but an INSERT might if it caused the file to expand. I wish I had a comprehensive list of actions that would cause the modified date to be changed but it is easier to remember the things that do not change that date and go from there.
So, bottom line is that the modified date has nothing to do with actual database usage. If you are thinking of removing databases based upon the modified date…well…please don’t.
Probably a good idea to rename the database first by pre or postfixing it with ‘deprecate’ and then wait a while to see if anyone comes screaming why the app stopped working
Even that really old database with the mssqlsystemresource.mdf File?!? I mean those files haven’t changed in years!!
😉
NO don’t really delete those
good point, i’ll clean those up later today.
More proof that the first thing a good DBA should say when someone makes an ad hoc request:
“What is it that you are trying to accomplish”.
Works for every request ever invented. Often the request is not what the person wants, but their first attempt to assess HOW they want their request to be fulfilled.
that is a very good way to approach most situations. the difficulty is getting the requester to understand that there are different ways of doing things, or getting things done, and that the obvious way may not be what they need. being able to separate wants from needs is a soft skill that many good DBA’s have in their possession.
The second they walked away I’d start investigating how someone who has that little of a clue has access to the file system where my databases reside!
not a bad thought, but there is much to a server OS that i don’t know and i wouldn’t want them to remove my remote access as a result!