October 31, 2003 at 10:30 am
I currently have an outdated table and a replacement table that contain basically the same information. I'm trying to transition the database code to use the new table instead of the old one, so that I can eventually drop the old table. I've used sp_search_code (great procedure, by the way - http://vyaskn.tripod.com/code/search_stored_procedure_code.txt) to determine that there are no more procedures in my databases that use this outdated table. The problem, though, is that I have no way of telling if any applications my company uses are calling this old table without crawling through thousands of lines of code. I was hoping that I could do something like setup a TRIGGER ON SELECT for the table, but SQL Server doesn't support anything like that. Is there another way to track select usage on a table without crawling through the code? Any help is appreciated.
I'm using SQL Server 2000, if that would affect the answer.
Ryan
October 31, 2003 at 12:05 pm
Profiler seems the only tools you can track this down and you have to run it for a period of time longer enough to decide there are no applications using the table.
October 31, 2003 at 1:36 pm
Profiler does seem to have what I'm looking for, but I'm a little unclear about how to use it. I thought I had it setup correctly, but I just get hundreds of locks and unlocks for other objects in the database, not the one I want to be monitoring. When defining my profile, I used the following details:
1. For Events, I selected "Locks" and "Scans" - is that all I need to watch, or is there another event to watch that might serve my purposes better? I want to see if any data is selected from this table through any means - select, cursor, join, etc.
2. In Filters, I entered the database name as like "CDW" (the database this table resides in) and the object name as like "calendar" (the table name). However, Profiler seems to list events for other objects as well - am I forgetting something? Also, should "Exclude System IDs" be checked as well? It's currently unchecked.
Thanks for the help, and this definitely seems like the right direction to go. I've never used Profiler before, and it seems like a really promising tool, if only I can get it to monitor what I want it to instead of everything all at once. Thanks for the help!
-------------------------------
Ryan McCauley
Business Systems Engineer
Peter Piper, Inc.
(480) 609-6456
-------------------------------
Ryan
October 31, 2003 at 2:36 pm
If you select "Locks" you will get smacked. I'd get just TSQL and RPC, either start or finished and let if run. The scan the results (the textdata column) for the table name
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 31, 2003 at 3:02 pm
Thanks for the pointer on locks - that definitely made things a little easier to read. I made the changes you recommended, and it looks to be capturing the appropriate data now. I also got the databaseID from master and the objectID from the database and used both as filtering criteria, and that looks to narrow it down to just that table. The databasename and objectname fields weren't populating correctly, but with the added filtering criteria, only actions performed on the calendar table are recorded - it seems as though filtering on the object and databases names wasn't working, but using the IDs seems to work just fine. Thanks for the help!
-------------------------------------------------------
Ryan McCauley
Business Systems Engineer
Peter Piper, Inc.
(480) 609-6456
-------------------------------------------------------
Ryan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply