Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

determing when a database was last modified Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 10:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:44 PM
Points: 12, Visits: 61
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?
Post #1379607
Posted Wednesday, October 31, 2012 10:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:55 PM
Points: 54, Visits: 83
•On 2008: using the new Server Auditing feature
•On 2005: using Dynamic Management Views (DMV)
Post #1379614
Posted Wednesday, October 31, 2012 11:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:44 PM
Points: 12, Visits: 61
what dmv's are you referring to?
Post #1379617
Posted Wednesday, October 31, 2012 11:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:55 PM
Points: 54, Visits: 83
Please refer :

http://msdn.microsoft.com/en-us/library/ms188754(v=sql.90).aspx
Post #1379618
Posted Wednesday, October 31, 2012 11:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:44 PM
Points: 12, Visits: 61
thanks mate - I know what a dmv is...

Do you know which particular dmv will help me?

Post #1379619
Posted Wednesday, October 31, 2012 11:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:55 PM
Points: 54, Visits: 83
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;




Post #1379620
Posted Wednesday, October 31, 2012 11:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:44 PM
Points: 12, Visits: 61
beautiful - thanks heaps!
Post #1379621
Posted Thursday, November 1, 2012 4:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 8:13 AM
Points: 13, Visits: 157
Inbuilt "Schema Changes History" report can also help
Post #1379720
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse