Someone asked the question lately about archiving old data. This person wanted to purge data from old tables and was wondering if there was a way in SQL Server 2005 to detect the last time a table was accessed. While there is some data kept on tables being accessed (plans, potentially missing indexes, etc.) this data is not preserved forever and often is cleared in restarts.
With no reliable method for determining when someone last accessed a table, what can you do? It's a question that DBAs face regularly now, and will face more often in the future. With data sets growing quickly, and storage budgets growing more slowly, the need to archive data becomes more prevalent all the time.
I hope at some point the metadata about table access will get built into SQL Server, but until that time you are on your own. If you can change the application code, you might be able to log this, but ultimately I think that you might end up making some type of guess about whether or not a table, or subset of a table, is still being used.
Once you do that, there are multiple ways to archive off the data, but no matter how you build a process, make sure that you can do one thing: restore the data. Someone can understand you making a mistake in which data to archive, but they won't likely understand if you make a mistake in restoring it.
Steve Jones
The Voice of the DBA Podcasts
- Windows Media Podcast - 18.8MB WMV
- iPod Video Podcast - 13.7MB MP4
- MP3 Audio Podcast - 3.2MB MP3
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed:
or now on iTunes!
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
You can also follow Steve Jones on Twitter:
