April 21, 2010 at 11:02 am
Hi ,
i need to find the tables that are not using since long time in the database
it would be great if you can post the scrpit if any ...
thanks in advance
regards,
kris
April 21, 2010 at 12:05 pm
i use a nice script from sqlmag:
SELECT
t.name AS 'Table',
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'
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
http://www.sqlmag.com/Article/ArticleID/53878/sql_server_53878.html
April 21, 2010 at 12:16 pm
This could work, but keep in mind that the information in sys.dm_db_index_usage_stats is only since the last service start. If your SQL service just started yesterday there won't be any value, but if it started 3 months ago then you can likely get useful information. Here's a version with a filter:
SELECT t.name AS 'Table',
SUM(i.user_seeks + i.user_scans + i.user_lookups) 'Total accesses' ,
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
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
HAVING ISNULL(SUM(i.user_seeks + i.user_scans + i.user_lookups),0) < 2
ORDER BY [Total accesses] DESC
April 21, 2010 at 10:21 pm
Is there any script which you could share with us for use in SQL Server 2000
M&M
April 22, 2010 at 2:18 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply