March 31, 2009 at 10:58 am
How do i tell when the database was last used
March 31, 2009 at 11:13 am
Have you had a look at sys.dm_db_index_usage_stats? this might be useful, it will tell you about all reads and writes. have a look at it and let us know if you can't find a way to work on it.
April 1, 2009 at 8:26 am
Hi
using Windows PowerShell you can do this.
get-childitem *.mdf |
sort-object LastWriteTime |
format-table Name, LastWriteTime
This statement assumes database is in AutoClose mode.
Thanks -- Vijaya Kadiyala
April 1, 2009 at 8:26 am
You may want to refer to the below link
http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic45049.aspx
Thanks -- Vijaya Kadiyala
April 1, 2009 at 9:24 am
If you think this database might not be used, then set AutoClose and when someone accesses it, an entry will go into the error log.
Note that you don't want this set if databases are being used as it takes time to open them up for use.
April 1, 2009 at 9:46 am
Steve Jones - Editor (4/1/2009)
If you think this database might not be used, then set AutoClose and when someone accesses it, an entry will go into the error log.
Hi Steve
I think the "Auto Close" should only be used in desktop database solutions.
Greets
Flo
April 1, 2009 at 9:55 am
Have you found a way yet?
You can use this query:
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 1, 2009 at 9:57 am
I'd disagree. You don't want to set this on databases that are being used, but if you suspect that there is a database not used, or rarely used, I'd set it. This is a low impact way to see if it's really being used.
Note that I'm not sure if a backup would set this off. It might.
The other thing to do is detach this database and see who complains 😉
April 1, 2009 at 10:10 am
Steve Jones - Editor (4/1/2009)
I'd disagree. You don't want to set this on databases that are being used, but if you suspect that there is a database not used, or rarely used, I'd set it. This is a low impact way to see if it's really being used.
I don't know if SQL Server automatically frees the database from memory if it is not used. If it doesn't and the database is rarely used sure it might be an option. But it causes a recompilation of all execution plans (I just had a look to BOL 😉 ).
Note that I'm not sure if a backup would set this off. It might.
I have no idea.
The other thing to do is detach this database and see who complains 😉
A feature which I use to figure out which of the "I shortly need my own to test something and will remove it after"-Databases are still used and which aren't. 😀
Greets
Flo
April 1, 2009 at 11:27 am
there is one good line article from Steve Jones on this..
http://www.sqlservercentral.com/articles/Administration/autoclosefordatabases/891/
Thanks -- Vijaya Kadiyala
April 6, 2009 at 12:22 pm
not working, in what database am i suppose to run this in?
error is Invalid object name 'sys.dm_db_index_usage_stats'.
April 6, 2009 at 12:28 pm
paul.starr (4/6/2009)
not working, in what database am i suppose to run this in?error is Invalid object name 'sys.dm_db_index_usage_stats'.
Is it SQL 2000 or 2005 you are running?
Any database.
April 6, 2009 at 12:30 pm
sql 2000
April 6, 2009 at 12:32 pm
paul.starr (4/6/2009)
sql 2000
Oops you are in the wrong forum then. it works only for 2005.
I am not so sure how you can implement in 2000, lets wait for other members.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply