Last Time Object Used

I have not been able to find anything remotely close to doing what I need.
I've been told that nothing like this exists in SQL Server, but I'm sure
there has to be something, somewhere, somehow to do this.
I need to find out when the last time an object was used.
The object could be a table, view, stored proc, etc.
I have a SQL Server than I am trying to maintain, and along with that I need
to get rid of all of the things from the past that having been used in some
time, and aren't part of the business model anymore.
If anyone could please help me, I would really appreciate it. I would
rather not have to go object by object and having to discuss it individually
to determine if it's been used recently or not.
Thank You.
- AnthonyYou can setup SQL Trace to audit the use of the objects in a database. Let
the trace(s) run for a sufficient period of time, and then analyze the trace
file to see what objects have been used.
But personally, I would not rely on this method to determine what objects I
can get rid of. Rather, I'd do what you rather not have to do, that is, to
discuss object by object with the application folks, vendors, or whoever may
own the object, and get their approval before dropping anything. If I can't
find the owner, I'd rather leave the object there than drop a wrong object
and face the wrath of its user. You just never know. For instance, you may
have some objects that will only be used durign the critically important
year-end processes, and your trace/audit may not find them to be used even
if you audit your system for six months.
Linchi
"ALaRiva" <ALaRiva@.discussions.microsoft.com> wrote in message
news:EFA01E76-B5BF-4423-AEF1-E3359EDD57DC@.microsoft.com...
>I have not been able to find anything remotely close to doing what I need.
> I've been told that nothing like this exists in SQL Server, but I'm sure
> there has to be something, somewhere, somehow to do this.
> I need to find out when the last time an object was used.
> The object could be a table, view, stored proc, etc.
> I have a SQL Server than I am trying to maintain, and along with that I
> need
> to get rid of all of the things from the past that having been used in
> some
> time, and aren't part of the business model anymore.
> If anyone could please help me, I would really appreciate it. I would
> rather not have to go object by object and having to discuss it
> individually
> to determine if it's been used recently or not.
> Thank You.
> - Anthony|||Create an event notification to queue to an audit table. F the SQL Profile
solution.
"ALaRiva" <ALaRiva@.discussions.microsoft.com> wrote in message
news:EFA01E76-B5BF-4423-AEF1-E3359EDD57DC@.microsoft.com...
>I have not been able to find anything remotely close to doing what I need.
> I've been told that nothing like this exists in SQL Server, but I'm sure
> there has to be something, somewhere, somehow to do this.
> I need to find out when the last time an object was used.
> The object could be a table, view, stored proc, etc.
> I have a SQL Server than I am trying to maintain, and along with that I
> need
> to get rid of all of the things from the past that having been used in
> some
> time, and aren't part of the business model anymore.
> If anyone could please help me, I would really appreciate it. I would
> rather not have to go object by object and having to discuss it
> individually
> to determine if it's been used recently or not.
> Thank You.
> - Anthony|||Thank You Both!
Yes. It looks like my only option will be to go through each object, one by
one. We do know that there are alot of objects that haven't been used in
years, which are the ones that I was aiming for, however, a trace/audit won't
help because I can't wait 2 years to make sure it's not a year-end thing.
The cleanup and optimization is in preparation for possibly going to SQL
Server 2005. We are currently running SQL 7, and I'm the sole person
responsible for SQL and deciding on whether we should go to 2005 or not.
Looks like I have alot of calling to do. If/Once we go to 2005, I'm only
going to import object by object. We won't be getting rid of the current SQL
Server, so if something turns up to be required later in the future, we could
go back and get it.
Thank You everyone for the suggestions.
- Anthony