SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Last accessed time for a partition		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlusr1
Starting Member

USA
6 Posts
Posted - 06/28/2012 :  14:50:21  Show Profile  Reply with Quote
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

USA
15558 Posts
Posted - 06/28/2012 :  15:56:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
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?
Go to Top of Page

sqlusr1
Starting Member

USA
6 Posts
Posted - 06/28/2012 :  16:46:23  Show Profile  Reply with Quote
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
Go to Top of Page

sqlusr1
Starting Member

USA
6 Posts
Posted - 06/28/2012 :  16:59:28  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15558 Posts
Posted - 06/28/2012 :  17:06:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

sqlusr1
Starting Member

USA
6 Posts
Posted - 06/29/2012 :  14:04:51  Show Profile  Reply with Quote
Thank you robvolk
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000