May 7, 2012 at 12:53 am
I need to migrate an old database (SQL Server 7 on Windows Server 2000) with about 70 tables, 150 views, and 20 stored procedures. I'm sure that most of it isn't even being used anymore, but parts of it are critical to business planning operations. The end users only know the names of a few of the tables or views and aren't very tech-savy.
I would like to be able to assess the various objects and procedures in preparation for decommissioning. Is there a way to query the database to find the last time each object was used (e.g. last write to each table, last read from each table, last read from each view, last execution of each SP) ?
Thanks!
May 25, 2012 at 5:28 am
There's no way to do that. However you can set up a trace and leave it running for a while to see which objects are accessed.
May 25, 2012 at 7:51 am
Yeah there is no sure fire way to do that. One recommendation is to rename the objects instead of deleting them. For example if you have table called "Employees" that you think is no longer needed rename it to "Employees_delete". That way anything that tries to access it will fail but recovering it is nearly instant.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 8:05 am
We could configure a job which runs sp_who2 at certain intervals. Periodically review the output file of the job and verify the contents of the file. By this we could check if there is any real activitiy happening on this server.
M&M
May 25, 2012 at 8:32 am
mohammed moinudheen (5/25/2012)
We could configure a job which runs sp_who2 at certain intervals. Periodically review the output file of the job and verify the contents of the file. By this we could check if there is any real activitiy happening on this server.
sp_who2 will not help in any way in this case..unless I'm missing something
May 25, 2012 at 8:40 am
clayman (5/25/2012)
mohammed moinudheen (5/25/2012)
We could configure a job which runs sp_who2 at certain intervals. Periodically review the output file of the job and verify the contents of the file. By this we could check if there is any real activitiy happening on this server.sp_who2 will not help in any way in this case..unless I'm missing something
Agreed, but atleast we could see if there are any relevant connections to this database.
M&M
May 27, 2012 at 3:44 pm
Thanks, guys.
I'll try the renaming approach. (I got no hits for "sp_who2" in SQL Server 7 Help.)
Regards,
Al
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply