April 6, 2009 at 1:10 pm
I would like to know the last activity date on a database. Its not the date backed up , i would like to know last Read or Write on a database.
For all the databases in a server.
April 6, 2009 at 1:30 pm
Please be more specific.
"Don't limit your challenges, challenge your limits"
April 6, 2009 at 1:32 pm
Thats all i can say. Looking for a date when was the last DML activity occured on a database.
April 6, 2009 at 1:54 pm
Not unless you have some kind of auditing setup on the database. You can look at the default trace, but the information in those traces can for a very limited amount depending on how active your server is.
Alternately, you can setup a server-side trace and filter all activity for the database you need this information for. If you do decide to do this be careful about the events you monitor, since you could easily fill up your hard drive with unwanted information.
April 6, 2009 at 2:10 pm
yes that is the problem you can try to look if there is any chance from any kind of DMV's which give you that information.
April 6, 2009 at 2:13 pm
You can also look up the data itself if there is any columns with date time information.
April 6, 2009 at 2:22 pm
I still believe there should be some way from the sys databases or some where else to find this information.
I am not an expert about the system information but how can sql server is not tracking all this stuff.
April 6, 2009 at 2:26 pm
I don't believe DMV's would give you this information since most of the information the DMV's return is not persisted. The information that you're looking for would have to be stored some table to make it accessible over extended periods of time.
April 6, 2009 at 6:51 pm
Usage :
This will give you when was database last accessed , selected, inserted, updated, deleted all information.
Compatibility :
This works in SQL Server 2005
Note :
Also. Information returned from this DMV is refreshed after you restart SQL Server. Meaning if you restart SQL Server, you will loose all information.
use database_name -- Replace Database Name here
selectt.name,
user_seeks,
user_scans,
user_lookups,
user_updates,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
fromsys.dm_db_index_usage_stats i
JOIN sys.tables t ON (t.object_id = i.object_id)
wheredatabase_id = db_id()
~ IM
April 6, 2009 at 6:56 pm
How about this one:
SELECT DB_NAME(database_id), LastRead = MAX(CASE
WHEN last_user_seek > last_user_scan AND last_user_seek > last_user_lookup
THEN last_user_seek
WHEN last_user_scan > last_user_seek AND last_user_scan > last_user_lookup
THEN last_user_scan
ELSE last_user_lookup
END
), LastWrite = MAX(last_user_update) FROM
(
SELECT
database_id,
last_user_seek = COALESCE(last_user_seek, '19000101'),
last_user_scan = COALESCE(last_user_scan, '19000101'),
last_user_lookup = COALESCE(last_user_lookup, '19000101'),
last_user_update = COALESCE(last_user_update, '19000101')
FROM sys.dm_db_index_usage_stats
) x
GROUP BY DB_NAME(database_id)
ORDER BY 1;
April 7, 2009 at 7:02 am
I reboot my servers every week, that means i think these queries will not give correct info.
Ok, i persume that last modifed date od an mdf or log file may give right information, am i right?
But may be every time i backup the database even the modified date of the files change to the backup/restore date.
April 7, 2009 at 11:48 am
If i had a server side trace for auditing, which events i need to look into to get this information.
April 7, 2009 at 11:52 am
Look at the events that are available for tracing in the link
http://msdn.microsoft.com/en-us/library/ms186265.aspx
and the filters that can be used at
http://msdn.microsoft.com/en-us/library/ms174404.aspx
Be careful with not tracing too much information, since depending on the server activity you could end up using all disk space.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply