March 17, 2015 at 8:24 am
Hi there ..
I am using this query to find out the database name and when the last time that offline database is been used. i tried to look by log but log not was never configured and hence i cant get that information from there.. i wrote a query but its resulting in by showing me all the online databases not the offline database..
Can someone help me in this please .. Here is the query
select @@servername AS Server_name, a.Name, a.database_id,a.state_desc,b.login_time, b.last_batch,b.[status],
c.last_user_seek,
c.last_user_scan,c.last_user_update
from sys.databases a
left join sysprocesses b
on a.database_id = b.dbid
join sys.dm_db_index_usage_stats c
on a.database_id = c.database_id
and a.database_id not between 1 and 4
and c.last_user_scan < '2015-03-17'
order by name
March 17, 2015 at 8:37 am
There are certain events that cause index usage stats to be reset, and it looks as if taking a database offline is one of them. If you want to rely on index usage stats, I would recommend that you collect and store them regularly, perhaps every 30 minutes.
John
March 17, 2015 at 8:40 am
i just want to know when these databases used last time so ill take their backup and detached them thats why i am using the index usage..
you think if i should use something else then i am open
March 17, 2015 at 8:45 am
John suggested what you should do.
John Mitchell-245523 (3/17/2015)
If you want to rely on index usage stats, I would recommend that you collect and store them regularly, perhaps every 30 minutes.
Set up something to record the stats for several months (make sure you include the year-end process), then you'll have good information about what gets used and what doesn't
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2015 at 8:45 am
I'm not saying that you should necessarily use something else. I'm saying that it's possible for index usage stats to be cleared between the last time the database was used and when you run your query. That's why you should take a regular snapshot of the index usage stats if you want to rely on them.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply