Monitoring of idle databases
How do you determine if a database is no longer needed? How would you determine the last access dates for databases.
July 3rd, 2013 1:56pm

very access date in sys.databases
Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2013 11:45pm

1) In SQL Server 2005 and onwards you can use the sys.dm_db_index_usage_stats data 
management view, look at the column last_user_update, you can also see when 
the table was last accessed (last_user_seek and last_user_scan - do a MAX on 
them).

SELECT
    last_user_seek = MAX(last_user_seek),
    last_user_scan = MAX(last_user_scan),
    last_user_lookup = MAX(last_user_lookup),
    last_user_update = MAX(last_user_update)
FROM
    sys.dm_db_index_usage_stats
WHERE
    [database_id] = DB_ID()
    -- if you want to leave out system objects, uncomment the next line:
    -- AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

2) Rename the database and see if you start getting complaints from users :-)))))

July 4th, 2013 1:17am

Thank you Uri.

New to SQl Server and am on a learning curve.

Free Windows Admin Tool Kit Click here and download it now
July 4th, 2013 4:16am

Thank you Rajendra.

July 4th, 2013 4:18am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics