September 29, 2010 at 11:42 am
Sort of a silly question, but the answer escapes me. I recently inherited a server and it has a table with time sensitive data that is outdated (I recognized the list). Point is, I even don't know if this table is being accessed (SELECT query) or not.
How can I track usage on a table in SQL2005?
Thanks a buch
September 29, 2010 at 11:44 am
sorry, should have added that the username is not sufficient info, because it is likely some automated process. I rather see the query that is performed and from which DB this request was sourced.
September 29, 2010 at 12:07 pm
Depends on what kind of complaints you are ready to receive.
You can log every query on the server for 1 to 24 months and then scan all that to find if the table is used.
Or you can rename the table and see if someone complains.
Under no ciscumstances should you delete it. Move to another server, back it up. Make sure you can restore in no time flat when needed.
Another question is why do you want to go through all that trouble?? How many GB / TB are you going to save?
September 29, 2010 at 12:14 pm
I don't want to change the table because the server frequently runs multi-day processes, so renaming/dropping/moving is out of the question because it could affect those processes.
The list itself is periodically refreshed on a different server, but not pushed to this one. If the table is actually used, I need to refresh it too. If the table is not used, there is no need for the table to exist on this server. The easiest solution would be a Trigger on SELECT (if that were to exist)
September 29, 2010 at 12:24 pm
The closest thing to that is a trace with a filter on textdata to the name of that table.
Only you can know or find out how long to run that trace to figure it out.
However if the push is fast I'd just redo it.
September 29, 2010 at 2:24 pm
Hi,
The following will work, but keep in mind that the information in sys.dm_db_index_usage_stats is only since the last service start.
SELECT t.name AS 'Table',
SUM(i.user_seeks + i.user_scans + i.user_lookups) 'Total accesses' ,
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
FROM sys.dm_db_index_usage_stats i
RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)
GROUP BY i.object_id, t.name
HAVING ISNULL(SUM(i.user_seeks + i.user_scans + i.user_lookups),0) < 2
ORDER BY [Total accesses] DESC
Regards
Parthi
Thanks
Parthi
September 29, 2010 at 2:31 pm
Won't those stats also go out the window as the server is under memory pressure?
I'm trying it on my prod servers and I'm having doubts about the results I'm seeing (100 tables used out of 1300 on a prod DB with last reboot 3 weeks ago)...
That server is under high memory pressure at the moment.
September 29, 2010 at 2:34 pm
Ninja's_RGR'us (9/29/2010)
Won't those stats also go out the window as the server is under memory pressure?I'm trying it on my prod servers and I'm having doubts about the results I'm seeing (100 tables used out of 1300 on a prod DB with last reboot 3 weeks ago)...
That server is under high memory pressure at the moment.
I've never heard of memory pressure clearing DMVs..only a service reboot, taking the database offline, detaching, or running the dbcc command to clear it (which escapes me at the moment).
September 29, 2010 at 2:36 pm
Thanks... just looked it up in BOL.
I must have had it confused with query plans.
So now what the heck do I do with that DB :w00t:.
September 29, 2010 at 2:37 pm
Ninja's_RGR'us (9/29/2010)
Thanks... just looked it up in BOL.I must have had it confused with query plans.
So now what the heck do I do with that DB :w00t:.
Drop 300 tables and save a ton of space? 😀
September 29, 2010 at 2:46 pm
Not quite... drop 1200 tables and leave the 100ish used tables.
Something tells me that it might be a bad idea :hehe:.
September 29, 2010 at 2:57 pm
Thansk Parthi, works like a charm 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply