CODEWITHSUNDEEP
sql-server-2005
jandersson
jandersson

Reputation: 1629

Check usage history on tables in SQL Server 2005

Is there a way to check statistics for table access, by update, delete or select? Im trying to find obsolete unused tables for a database cleanup.

Thanks

Upvotes: 1

Views: 5575

Answers (5)

Mike
Mike

Reputation: 1

-- shows table accesses since last MSSQL reboot via index stats.
-- NULL columns imply table wasnt accessed since the last restart.
SELECT 
  t.name AS 'Table', 
  Count(i.user_seeks) as 'Indexes',
  SUM(i.user_seeks + i.user_scans + i.user_lookups) 
    AS 'Total accesses',
  SUM(i.user_seeks) AS 'Seeks',
  SUM(i.user_scans) AS 'Scans',
  SUM(i.user_lookups) AS 'Lookups',
  SUM(i.user_updates) AS 'Updates'
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
ORDER BY [Total accesses] DESC

Upvotes: 0

Kristen
Kristen

Reputation: 4311

Could also put a Trigger on the table to store TableName + GetDate() in a "WasFoundToBeUsedAfterAll" table

You probably want to UpSert any rows already there, and maybe only if existing Date is already 24 hours old, or more, rather than insert multiple rows - which will just become a maintenance problem all of its own!

Upvotes: 0

marc_s
marc_s

Reputation: 755381

You could check your DMV's (Dynamic Management Views). If your tables all have a primary key, there's a primary key index, and if that index is never being read, your table is not being used at all.

But mind you: these are dynamic management views - they get reset to 0 each time SQL Server starts up. You might want to look into either the SQL Server 2008 Performance DAta Collector or the SQL DMV Stats project for SQL Server 2005 to capture DMV data over an extended period of time before dropping a table :-)

Marc

Upvotes: 0

Alex Reitbort
Alex Reitbort

Reputation: 13706

As far as I know, there is no build in statistics for such data in Sql Server. Although you can use Sql Profiler to get usage data over a period of time.

Upvotes: 0

Unsliced
Unsliced

Reputation: 10552

Yes - you can create a trace, MSDN - Data Access Tracing in SQL Server 2005.

Alternatively, if you know what's accessing them (e.g. via stored procedures), you can leave trace information in them (adding rows to a table), then you can see what is actually being run. That's less foolproof - you're proving a negative, because you'll find it harder to catch the occasional hits.

One method we use is to just rename the tables, e.g. to 'zzz_OldName' and just leave them there for a while, periodically pruning that area of the schema.

Upvotes: 1

Related Questions