April 14, 2017 at 9:10 am
Hello Team,
Can you please let me know how to find when all databases on SQL Server were last used. Is there a query to get the output
Thanks
April 14, 2017 at 9:57 am
atulyan.aries - Friday, April 14, 2017 9:10 AMHello Team,Can you please let me know how to find when all databases on SQL Server were last used. Is there a query to get the output
Thanks
SQL Server doesn't really track this so there isn't a straight forward, totally accurate way to tell. You can setup some auditing with extended events and other things but that is after the fact. If you want to know without setting anything up in advance, check Lowell's query in this thread - it's about as close as I've seen to get that information. And read the comments he has as well:
last date table was updated
Sue
April 14, 2017 at 3:38 pm
In addition to index usage stats, it is sometimes helpful to also query the statistics in the database.
Personally, I use Extended Events to figure out this sort of information. As Sue mentioned, that means you have to set it up before the fact. That said, you can run an XE session for a month to determine if the database is in use.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2017 at 6:10 pm
Heh... more fun to do the BSOFH thing to find out if a database is being used. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 17, 2017 at 6:39 am
It's likely to be WILDLY inaccurate, but you can also query the sys.dm_exec_query_stats to see if there's mention of the database in any of the queries that are currently in cache. Assuming it's been accessed recently, you'll see it there. However, there's no way to know that it wasn't accessed yesterday and the query has been flushed from cache or maybe had a recompile hit so it was never in cache or even the server was rebooted. Any of these (and several other things) could prevent this from giving you an accurate picture.
I agree with others, setting up Extended Events is the best way to be sure. Although, Jeff's idea isn't necessarily a bad one. It will be the most 100% accurate option.
"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
April 17, 2017 at 10:18 am
Jeff Moden - Friday, April 14, 2017 6:10 PMHeh... more fun to do the BSOFH thing to find out if a database is being used. 😉
This is phase two after the XE session is evaluated. hehe
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 17, 2017 at 4:13 pm
SQLRNNR - Monday, April 17, 2017 10:18 AMJeff Moden - Friday, April 14, 2017 6:10 PMHeh... more fun to do the BSOFH thing to find out if a database is being used. 😉This is phase two after the XE session is evaluated. hehe
Agreed and that's actually the way I do things. I determine a strong likelihood that the database isn't being used. Right after the 1st of the month, I give everyone a 2 week notice that, unless someone can vouch for the use of the database, that I'm going to "take it down" in 2 weeks. If no one steps up to verify it needs to continue as an active database, I do a Tail Log backup and then take it off line at the end of the 2 weeks (or so). I keep the database in an offline state for at least a month (normally 3 to span a quarter). If no one squawks during that timeframe, then I archive that last backup string I had (up to an including the Tail Log backup) and then I drop the database, backup history, etc. I've never had to do a restore after such a takedown in the past 2 decades. In fact, I've never had to bring one back online, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2017 at 9:47 am
Jeff Moden - Monday, April 17, 2017 4:13 PMAgreed and that's actually the way I do things. I determine a strong likelihood that the database isn't being used. Right after the 1st of the month, I give everyone a 2 week notice that, unless someone can vouch for the use of the database, that I'm going to "take it down" in 2 weeks. If no one steps up to verify it needs to continue as an active database, I do a Tail Log backup and then take it off line at the end of the 2 weeks (or so). I keep the database in an offline state for at least a month (normally 3 to span a quarter). If no one squawks during that timeframe, then I archive that last backup string I had (up to an including the Tail Log backup) and then I drop the database, backup history, etc. I've never had to do a restore after such a takedown in the past 2 decades. In fact, I've never had to bring one back online, either.
I have had to bring one database back online after following similar routine. Database was used once a year. Even so, it wasn't hard to bring it back because of the process (very similar to yours).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply