I have several tables that I'm not sure whether they are still in use by some old tools. Not all of them have columns with timestamps so I was wondering if there is some secret internal sql-server table where I could see when the last insert (or any other operation) took place for certain tables?
Not giving away any code or anything just yet but ways to detect whether a DB is still in use by some app can be as follows: - turn the DB off (set to single user mode, set as read-only, etc) many variations to effectively get the result of someone shouting at everybody else because one of their processes stopped unexpectedly and its because of the server you just "turned off" .. personally this is my favourite approach - but I do understand that it may cause some real business issues and some people just can't deal with emotions ;).
- you could also monitor the database size and see if that changes
- you could have a small process that counts all rows from particular tables (all tables) and logs that into some other database for you.. then simply see if the row count changes
- you could monitor the log file (if anything happens in terms of INSERT, UPDATES, DELETES, then you will see that there.
- you could run sp_who(2) at regular intervals to check who's connected
These are just quick and easy to implement methods to see if someone, somewhere is still using the database.
I hope this helps,
SQL Server doesn't have any secret internal mechanism to track all data changes to all tables. There are some things that can give indicators, such as row counts or statistics, but there are edge cases that make them imprecise. For row counts, you will miss things like a user deleting 10 rows, adding 10 more, and updating 20, all between your polling of row counts. Statistics updates can be triggered by all kinds of things that don't necessarily mean an end user or application was involved.
You could easily add triggers to all the tables to capture new inserts / updates / deletes, or add timestamps to all the tables that don't have them. After a certain period of time, preferably a complete business cycle, this could help you determine which tables are used. You need to wait for a whole business cycle, because some tables may only be written to at the end of a week, month, or other period. But this won't give you any data about the past.
Be careful to not fall into a common trap:
timestamp does not enable you to see when a row changed, only whether it has changed since the last time you checked. And of course if rows get deleted, you won't be able to check their
timestamp values, because the row simply isn't there.
When you've identified the tables you think are okay to delete, a trick I've used to make sure they're not still being used is to rename them or transfer them to a different schema. You'll find out pretty quick when an application or user can't find a certain table.
The other thing you could do, which is a little more disruptive, is move the things you know are still being used (and the connection strings for their applications) into their own database. And in fact each set of related things into its own database. You shouldn't ever be in a situation where you have a mix of things that are and aren't being used in a single database. When you've moved the known things, you could do a similar trick to above: mark the old database as read only and wait for someone to complain.