So I have recently inherited a (MS) SQL database that is used for generating reports from, the database size has grown I am looking for ways to trim it down. First a little background on how it works. This server gets its original data from an outside application that has a proprietary database structure, then a program (provided by the vendor) will pull data from this application and import it into SQL for us to write reports through crystal. Each table that is brought over needs to be setup in the application so its not just a blind dump of the data from the application into SQL.
The problem I am running into is that a few tables have their data being wrote over to SQL but there is no documentation as to why they were enabled and if anyone in our entire organization is generating any reports off of them. I suspect that they were turned on without any need and they are not needed however before i turn them off I need some way to know for sure.
I have looked up online to find out if there is some method to see what tables in SQL are being used however everything I have found will tell me if there are any commands ran against the table but they do no break out what type of commands. This link gives some info and tells me that the table is used however since it does not break out inserts/deletes from just select statements that would be used from a report I cant get the info i need.
So does anyone have an idea of how i can check a specific table in a database to see if any reports are occurring against it, when i do know that there are regular updates to the table from the original application?
Unfortunately just turning off the table isn't an option because if we do need the data the original source only keeps the info for a short period and we would not be able to recover the lost data from the time that it would be down.
I forgot to mention because of the way the vendor has their tool setup I do not have an option to move the tables into their own file group, everything has to stay in the primary file group.