Lists all databases, in an instance, that are not accessed before a given date

Question

Post reply

Lists all databases, in an instance, that are not accessed before a given date

mesgetachew

SSC Veteran

Points: 289
More actions
January 10, 2015 at 9:36 pm

#296594

Would appreciate if anyone knows a query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63501 More actions · Answer 1

all dbs not accessed before (or do you mean since) a given date...

Exercise your GoogleFu.

Found this pretty quickly:

http://dinesql.blogspot.com/2010/05/when-was-my-sql-server-database-last.html

I tweaked it a little, but I may have misunderstood the filter you wanted, but that should be easy to fix... here's my version:

DECLARE @Target_Date DATE = '20-Jan-2015';

SELECT DatabaseName, database_id, MAX(LastAccessDate) LastAccessDate

FROM

(SELECT

DB_NAME(database_id) DatabaseName

, database_id

, last_user_seek

, last_user_scan

, last_user_lookup

, last_user_update

FROM sys.dm_db_index_usage_stats) AS PivotTable

UNPIVOT

(LastAccessDate FOR last_user_access IN

(last_user_seek

, last_user_scan

, last_user_lookup

, last_user_update)

) AS UnpivotTable

WHERE LastAccessDate < @Target_Date

GROUP BY DatabaseName, database_id

HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb');

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

sys.dm_db_index_usage_stats is also where I would think to look. Just remember that a reboot will zero out those metrics, so you may see databases that haven't been accessed, but that actually were. Other than that, this is the kind of metric that you may need to gather on your own using extended events or querying and storing in a table.

"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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

mesgetachew (1/10/2015)
Would appreciate if anyone knows a query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?

BEFORE? Any database online as of December 30th will have been accessed BEFORE December 31 but you won't be able to tell unless you've kept every log it ever produced. Are you sure that you don't mean AFTER???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mesgetachew SSC Veteran Points: 289 More actions · Answer 4

It is BEFORE. The whole purpose was to identify all databases not touched/accessed for the past six months, if any, and then do something/research about them. I agree with what you say. That is, need to keep every log to answer this and other similar questions.

mesgetachew SSC Veteran Points: 289 More actions · Answer 5

Thank you! I tried it but it is not giving me the desired result. The whole purpose was to identify all databases not touched/accessed for the past six months, if any, and then do something/research about them. It looks like that we need keep every log to answer this and other similar questions.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

Jeff Moden (1/11/2015)
mesgetachew (1/10/2015)
Would appreciate if anyone knows a query that lists all databases, in an instance, that are not accessed before a given date (e.g., not accessed before December 31, 2014)?
BEFORE? Any database online as of December 30th will have been accessed BEFORE December 31 but you won't be able to tell unless you've kept every log it ever produced. Are you sure that you don't mean AFTER???

It is possible that a database could have been accessed 6 months and 1 day before December 31st which would still qualify as having been accessed BEFORE December 31st. If you want to check for access in the last six months, you have to check for anything that has not been accessed AFTER July 1st.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)