IS this table Used?

  • 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

  • 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.

  • 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?

  • 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)

  • 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.

  • 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

  • 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.

  • 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).

  • 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:.

  • 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? 😀

  • Not quite... drop 1200 tables and leave the 100ish used tables.

    Something tells me that it might be a bad idea :hehe:.

  • 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