Post reply

How to know what tables are not used?

  • March 23, 2009 at 7:45 am

    #132431

    Hello

    Do you have any idea how to determine which tables are not used?

    Thanks

  • March 23, 2009 at 7:47 am

    #964415

    Run a trace on the database. Have it record which tables are accessed. From that, you can compare it to sys.tables, and find which ones aren't.

    Just be sure to run it for a good, long time, because you don't want to drop a table that's critical for monthly or quarterly reports, just because it wasn't used this afternoon.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 23, 2009 at 7:49 am

    #964417

    Thanks, but is the only way to do this?

  • March 23, 2009 at 7:50 am

    #964419

    the way described above is the only way.

    SQL Server does not keep track of when a table was accessed, only a trace can be used to determine that.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 23, 2009 at 7:58 am

    #964426

    Ok

    SQLServer has information for index that are not used.

    And for stored procedures, views or other objects?

  • March 23, 2009 at 8:00 am

    #964430

    Same deal. Run a trace. It'll tell you exactly what's being used.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 23, 2009 at 8:10 am

    #964436

    Oscar Fernandez (3/23/2009)

    SQLServer has information for index that are not used.

    That info's only since the last start of the SQL service. The data in the index usage DMV is not persisted across restarts.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 23, 2009 at 9:46 am

    #964541

    Perfect.

    Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply