January 8, 2007 at 8:05 am
I'm currently supporting 53 servers with over 400 databases (SQL Server 2000 and 2005). We are being asked to provide the date that each database was last used. How can this be accomplished? Can this be pulled from a system table? Any help would be greatly appreciated. and thanks in advance.
January 8, 2007 at 1:19 pm
Unless you've been running a trace I think you're pretty much out of luck with the sql server 2000 instances.
With 2005 the situation is a little better since you can query the sys.dm_db_index_usage_stats view to get an idea of what indexes were used and when.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 8, 2007 at 1:23 pm
I forgot to mention that if you are auditing logins (successfull) you might be able to put together a WAG. Since most logins tend to be tied to one database (or a small number of them) just by looking at the activity for each Login you might be able to provide something.
Once again, if you haven't been auditing successful login attemts, I think you are hosed.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 8, 2007 at 5:41 pm
As DCPeterson mentioned there is no builtin functionality in sql but if your tables has inserted/updated data columns which inserts the datetime when the row inserted/updated take a look those table...
In sql server 2005 by default sql runs the backup trace which writes 10 trace file to log folder check those files...In 2000 there is none...
MohammedU
Microsoft SQL Server MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply