Last accessed time for a partition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Last accessed time for a partition

Author

Topic

sqlusr1
Starting Member

6 Posts

Posted - 2012-06-28 : 14:50:21

Hi ! I have a requirement to create a report to show business users what partioned data that is not actively being used. So that I can archive this data.can anyone please let me know how to query for that data.

siri

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-28 : 15:56:25

The sys.dm_db_index_usage_stats DMV will give you access times but only at an index level. You can try applying this to the sys.dm_db_index_operational_stats function to associate it with partitions and look for any that have range_scan_count and singleton_lookup_count equal to zero:

SELECT DB_NAME(ios.database_id) DB, OBJECT_NAME(ios.object_id,ios.database_id) OBJECT, * 
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) ios 
ON ius.database_id=ios.database_id AND ius.index_id=ios.index_id
WHERE COALESCE(ius.last_user_lookup, ius.last_user_seek, ius.last_user_scan) IS NULL
AND ios.range_scan_count=0 AND ios.singleton_lookup_count=0 AND ios.database_id>4

Note: this isn't exact (or even correct for all I know), but it's about the only starting point I can think of.

By the way, how do you intend to "archive" the data? How is it partitioned now? And why doesn't the partition scheme have a partition for such data to be archived to?

sqlusr1
Starting Member

6 Posts

Posted - 2012-06-28 : 16:46:23

Thank you robvolk , I never did partition before and this is my new job , the previous DBA did 20 partitions of the table , until 2010 the data is going to arcdata file group and from 2010 the data is going primary file group.So my manager is asking if no one is accessing any of the arcdata partitions why to keep them on disk. So I am trying find where to start. And also some of the tables were partitioned on different columns using different partition schemes.So I am a bit confused and looking for some pointers

siri

sqlusr1
Starting Member

6 Posts

Posted - 2012-06-28 : 16:59:28

I am trying to understand why we need to create several partition schemes for one table.20 partitions were created base on date, sixteen partitions were created on another column like that there are 9 partition schemes for the same tables.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-28 : 17:06:02

Unless you need to recover the disk space used, there's no harm in leaving the data on those partitions. If you do need to recover disk space, deleting/archiving data based on partition usage is not an effective strategy. Someone could be doing a lot of historical analysis which would access those old partitions; likewise, new data could be inserted once into the new partition and never accessed again. Archiving based on date or some other data values is more sensible, and should not be impacted by the partitioning scheme at all.

sqlusr1
Starting Member

6 Posts

Posted - 2012-06-29 : 14:04:51

Thank you robvolk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources