Find tables unused since last SQL Server restart

  • Ryan McCauley

    Right there with Babe

    Points: 773

    Comments posted to this topic are about the item Find tables unused since last SQL Server restart

    Ryan

  • Gail Shaw

    SSC Guru

    Points: 1004484

    It's not since the last SQL start, it's since the last database start. If the DB is closed, taken offline, detached or restored, those stats are also cleared. Watch out for DBs set to autoclose

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

    Ten Centuries

    Points: 1391

    Iā€™m trying to run this t-sql but it gives me this error:

    Msg 102, Level 15, State 1, Line 4

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 9

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 14

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 19

    Incorrect syntax near '?'.

  • John McC

    Hall of Fame

    Points: 3410

    Same error here.

    -------------------------------------------------------------------------
    Normal chaos will be resumed as soon as possible. :crazy:

  • Gail Shaw

    SSC Guru

    Points: 1004484

    It looks like there are some non-printable characters that aren't spaces but look like spaces. Edit the whole thing onto a single line then re-format. Seems to work

    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
  • John McC

    Hall of Fame

    Points: 3410

    Using http://extras.sqlservercentral.com/prettifier/prettifier.aspx

    WITH LastActivity (ObjectID, LastAction) AS

    (

    SELECT OBJECT_ID AS TableName,

    last_user_seek AS LastAction

    FROM sys.dm_db_index_usage_stats u

    WHERE database_id = DB_ID(DB_NAME())

    UNION

    SELECT OBJ

    ECT_ID AS TableName,

    last_user_scan AS LastAction

    FROM sys.dm_db_index_usage_stats u

    WHERE database_id = DB_ID(DB_NAME())

    UNION

    SELECT OBJECT_ID AS TableName,

    last_user_lookup AS LastAction

    FROM sys.dm_db_index_usage_stats u

    WHERE database_id = DB_ID(DB_NAME())

    )

    SELECT OBJECT_NAME(so.OBJECT_ID) AS TableName,

    MAX(la.LastAction) AS LastSelect

    FROM sys.objects so

    LEFT

    JOIN LastActivity la

    ON so.OBJECT_ID = la.ObjectID

    WHERE so.TYPE = 'U'

    AND so.OBJECT_ID > 100

    GROUP BY OBJECT_NAME(so.OBJECT_ID)

    ORDER BY OBJECT_NAME(so.OBJECT_ID)

    -------------------------------------------------------------------------
    Normal chaos will be resumed as soon as possible. :crazy:

  • jfordyce

    SSC Journeyman

    Points: 84

    Is there a version of this script that would show the last time a SP was executed?? I'm really in need of that one šŸ™‚

  • fjmorales

    Ten Centuries

    Points: 1391

    Thanks!!!

  • Ryan McCauley

    Right there with Babe

    Points: 773

    Thanks for re-formatting the query - I'll see if I can get that fixed on the posted version of the script.

    As far as Stored procs, it looks like you can do the same kind of thing as long as it's still in the cache, but not if the server has been restarted or the cache flushed since it was last run (much as with this script):

    SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),

    sys.dm_exec_query_stats.*

    FROM sys.dm_exec_query_stats

    CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)

    WHERE sys.dm_exec_sql_text.dbid = db_id()

    AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'yoursp'

    Ryan

  • David-CH

    SSC Enthusiast

    Points: 164

    copy the script onto Notepad. Remove all those tiny rectangular shapes (which were interpreted as spaces which cause errors), then copy the whole script back onto SSMS, parse again. You should get no error this time.:-)

  • Gail Shaw

    SSC Guru

    Points: 1004484

    There are a number of things that can cause a stored proc's plan to be dropped from cache. Stats updates, index rebuilds, table alterations, etc. Be very careful relying on what's in sys.dm_exec_cached_plans

    http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/

    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
  • Chris Holding

    Newbie

    Points: 5

    This works for SQL 2005 and above but I'm having problems with SQL 2000; can anyone rewrite for SQL 2000?

    Is there and equivalent for sys.dm_db_index_usage_stats etc in SQL 2000?

    Thanks in advance

  • Mo-455357

    Grasshopper

    Points: 16

    Is there to find unused functions and procedures as well? Or time since last call to these objects?

    Thanks

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Chris Holding (11/4/2010)


    This works for SQL 2005 and above but I'm having problems with SQL 2000; can anyone rewrite for SQL 2000?

    No, because the information is not there in SQL 2000.

    If you want this kind of info in SQL 2000, you'll need a trace (SQL Trace)

    Is there and equivalent for sys.dm_db_index_usage_stats etc in SQL 2000?

    No

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

    Old Hand

    Points: 382

    So I added a bit to it and made it so it will run aginst every database on the server. Hope this helps someone.

    CREATE TABLE #LastAccess (DatabaseName sysname,

    TableName sysname,

    LastSelect datetime)

    DECLARE @SQL varchar(max)

    SET @SQL = 'USE ?; '

    + 'WITH LastActivity (ObjectID, LastAction) AS '

    + '( '

    + 'SELECT object_id, '

    + 'last_user_seek as LastAction '

    + 'FROM sys.dm_db_index_usage_stats '

    + 'WHERE database_id = db_id(db_name()) '

    + 'UNION SELECT object_id, '

    + 'last_user_scan as LastAction '

    + 'FROM sys.dm_db_index_usage_stats '

    + 'WHERE database_id = db_id(db_name()) '

    + 'UNION SELECT object_id, '

    + 'last_user_lookup as LastAction '

    + 'FROM sys.dm_db_index_usage_stats '

    + 'WHERE database_id = db_id(db_name()) '

    + ') '

    + 'INSERT INTO #LastAccess '

    + 'SELECT DB_NAME() AS DatabaseName, '

    + 'OBJECT_NAME(so.object_id) AS TableName, '

    + 'MAX(la.LastAction) as LastSelect '

    + 'FROM sys.objects so '

    + 'LEFT JOIN LastActivity la '

    + 'ON so.object_id = la.ObjectID '

    + 'WHERE so.type = ''U'' '

    + 'AND so.object_id > 100 '

    + 'GROUP BY OBJECT_NAME(so.object_id) '

    + 'ORDER BY OBJECT_NAME(so.object_id) '

    EXEC sp_MSforeachdb @SQL

    SELECT * FROM #LastAccess

Viewing 15 posts - 1 through 15 (of 19 total)

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