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
+1
A:
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.
Unsliced
2009-02-16 10:36:54
+1 to the 'zzz_OldName' "Scream Test". We suffix the table name with the date so that, say, one month later we know which tables we can now physically drop. Ditto for any "just in case" tables which we name TEMP_MyTableName_YYYYMMDD - so all alpha-grouped by TEMP*
Kristen
2009-02-16 11:11:36
A:
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.
Alex Reitbort
2009-02-16 10:39:39
If your data is stored into tables by SProcs I'm not sure that Profiler will help with what actual tables were impacted - but I could be wrong on that!
Kristen
2009-02-16 11:12:40
It won't but at least it will show which SP's are used and which not, and that will help you to find unused tables.
Alex Reitbort
2009-02-16 11:14:19
A:
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
marc_s
2009-02-16 11:12:05
A:
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!
Kristen
2009-02-16 11:15:09