September 10, 2013 at 2:27 pm
I'm trying to determine approximately the last time a database was "touched" by users. Thus far, sys.dm_db_index_usage_stats seems to be about the best option. Can't really use the "Last modified" on the MDF / LDF files, as those will change based on SQL accessing them.
I'm not trying to use this to determine what indexes might be droppable, just to get an estimate of about the last time a user accessed the DB. The column I'm looking at especially is the last_user_update result.
Thanks,
Jason
September 10, 2013 at 3:39 pm
Interesting. I'd be more inclined to use a Server Side trace or XEvents to see if a database is used.
I don't think using the last_user_update column would be the best way to go because that only for changes to the index not use of the index. I'd go with the most recent of last_user_seek, last_user_scan, last_user_lookup. What if the database is only around for reporting and is essentially read only?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 10, 2013 at 4:17 pm
Jack Corbett (9/10/2013)
Interesting. I'd be more inclined to use a Server Side trace or XEvents to see if a database is used.I don't think using the last_user_update column would be the best way to go because that only for changes to the index not use of the index. I'd go with the most recent of last_user_seek, last_user_scan, last_user_lookup. What if the database is only around for reporting and is essentially read only?
I was also looking at those, but figured update would indicate if someone made changes. Truthfully, all the DBs I needed to find this out for showed user seeks / scans / lookups recently as well.
What should make this fun is, I was asked to find this out so that our group can appropriately charge users of the DB services. Well, several of the DBs in question are showing recent use, while the customer is saying they're not in use...
😀
Thanks!
Jason
September 11, 2013 at 4:36 am
If you have maintenance on the servers, that will show up when looking at the index usage stats.
I'm with Jack, set up extended events to track connections to the database. That's going to be about the single most lightweight, yet accurate, method.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 11, 2013 at 5:06 am
I'm going to take a look at XE going forward for this, but it sounds like in the short-term (as in, need a number / date yesterday) I did the best I could with the tools that were already in place...
Thanks!
Jason
September 11, 2013 at 6:30 am
I believe the answer to this next question is yes, but I'd like to be sure:
If I create an XE session on SQL2012 (for the GUI) and save it as a script, I can copy that script and run it on a SQL2008R2 system, yes?
Currently at work, we are not using SQL2012, or I'd have likely already whipped up a starter XE for this...
Trying to work my way through the TSQL to create one, and finding myself making (what I now see are) dumb mistakes (like trying to capture an Action rather than an Event...)
Thanks,
Jason
September 11, 2013 at 6:52 am
jasona.work (9/11/2013)
I believe the answer to this next question is yes, but I'd like to be sure:If I create an XE session on SQL2012 (for the GUI) and save it as a script, I can copy that script and run it on a SQL2008R2 system, yes?
Currently at work, we are not using SQL2012, or I'd have likely already whipped up a starter XE for this...
Trying to work my way through the TSQL to create one, and finding myself making (what I now see are) dumb mistakes (like trying to capture an Action rather than an Event...)
Thanks,
Jason
Maybe. There are several differences with XEvents between 2008 and 2012. I recommend getting the Extended Events Manager[/url] add-in for 2008 from codeplex. It is very similar to what MS did for 2012.
Edit: Actually put in the URL to Extended Events Manager
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 11, 2013 at 7:16 am
Jack Corbett (9/11/2013)
jasona.work (9/11/2013)
I believe the answer to this next question is yes, but I'd like to be sure:If I create an XE session on SQL2012 (for the GUI) and save it as a script, I can copy that script and run it on a SQL2008R2 system, yes?
Currently at work, we are not using SQL2012, or I'd have likely already whipped up a starter XE for this...
Trying to work my way through the TSQL to create one, and finding myself making (what I now see are) dumb mistakes (like trying to capture an Action rather than an Event...)
Thanks,
Jason
Maybe. There are several differences with XEvents between 2008 and 2012. I recommend getting the Extended Events Manager[/url] add-in for 2008 from codeplex. It is very similar to what MS did for 2012.
Edit: Actually put in the URL to Extended Events Manager
Jack's right (of course), but mostly, yes, it works (most of the time). Test it. If you're in an area where there are differences, you can quickly find them online. The documentation for extended events is pretty good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 11, 2013 at 7:30 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply