October 31, 2012 at 10:10 pm
Hi,
Can you determine when a database was last modified by looking at its mdf files last modified date (i.e. on the filesystem)?
If not which is the best way to determine this?
October 31, 2012 at 10:55 pm
•On 2008: using the new Server Auditing feature
•On 2005: using Dynamic Management Views (DMV)
October 31, 2012 at 11:00 pm
what dmv's are you referring to?
October 31, 2012 at 11:04 pm
October 31, 2012 at 11:08 pm
thanks mate - I know what a dmv is...
Do you know which particular dmv will help me?
October 31, 2012 at 11:17 pm
Woul this help ?
USE AdventureWorks;
GO
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
October 31, 2012 at 11:22 pm
beautiful - thanks heaps!
🙂
November 1, 2012 at 4:36 am
Inbuilt "Schema Changes History" report can also help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply