February 7, 2012 at 3:08 am
How we can find last accessed date time of a SQL Server 2008 database apart from using the DMV sys.dm_db_index_usage_stats because it will show result only for database tables having indexes.
Any audit or trace option...?
Regards
Vivek
February 7, 2012 at 6:21 pm
My q2uestion would be.... why do you have tables that people access that have no indexes?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2012 at 7:56 pm
February 7, 2012 at 8:00 pm
February 7, 2012 at 11:57 pm
We are in the process of retiring unused DBs. These databases and tables were created few years back.
Also, its not necessary that a table should have index.
August 29, 2012 at 9:38 am
sp_MSforeachdb @command1='use [?];
SELECT DB_NAME() as DatabaseName,
--s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time,
execution_count,
plan_generation_num,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY last_execution_time desc--,s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset
'
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy