Report specific Database usage by user

Is there a report or other mode to discover whether a database (on a multi database server) has been accessed by a user or not in a specified period of time (like 3 months)? We have enterprise servers that contain 40-50 databases and sometimes the business doesn't let us know that they are no longer using the database, so we would like to have a proactive way of listing the databases on those servers and know wheher it's been used by specific logins or not within a period of time.

Parents

0 Darren Mallette 4 months ago

hi Dwayne,

Assumption that this is for SQL Server. We have a user-defined collection in our lab that checks the last access for each database. It's not by specific login, just that anyone has accessed it.

This is the script, note the remarks at the top on server restart.

--Script to show when a database was last accessed
--If a server/service was rebooted/restarted, data will get cleared out

WITH results
     AS (SELECT   name,
                  last_access =
                     (SELECT X1 = MAX (LA.xx)
                        FROM (SELECT xx = MAX (last_user_seek)
                               WHERE MAX (last_user_seek) IS NOT NULL
                              UNION ALL
                              SELECT xx = MAX (last_user_scan)
                               WHERE MAX (last_user_scan) IS NOT NULL
                              UNION ALL
                              SELECT xx = MAX (last_user_lookup)
                               WHERE MAX (last_user_lookup) IS NOT NULL
                              UNION ALL
                              SELECT xx = MAX (last_user_update)
                               WHERE MAX (last_user_update) IS NOT NULL) LA)
             FROM    master.dbo.sysdatabases sd
                  LEFT OUTER JOIN
                     sys.dm_db_index_usage_stats s
                  ON sd.dbid = s.database_id
            WHERE     name NOT IN
                         ('master',
                          'model',
                          'msdb',
                          'tempdb',
                          'distribution',
                          'DBA',
                          'SSISDB',
                          'SSISConfigDB')
                  AND name NOT LIKE 'Report%Server%'
                  AND name NOT LIKE '%foglight%'
         GROUP BY sd.name)
SELECT name,
       CASE
          WHEN ISNULL (last_access, DATEADD (dd, -11, GETDATE ())) <
                  DATEADD (dd, -10, GETDATE ())
          THEN
             'No Database Access in last 10 days'
          ELSE
             CONVERT (VARCHAR, last_access, 121)
       END
          AccessDateDetails,
       DATEDIFF (dd, GETDATE (), last_access) AS days_since,
       last_access
  FROM results;

Reply

0 Darren Mallette 4 months ago

hi Dwayne,

Assumption that this is for SQL Server. We have a user-defined collection in our lab that checks the last access for each database. It's not by specific login, just that anyone has accessed it.

This is the script, note the remarks at the top on server restart.

--Script to show when a database was last accessed
--If a server/service was rebooted/restarted, data will get cleared out

WITH results
     AS (SELECT   name,
                  last_access =
                     (SELECT X1 = MAX (LA.xx)
                        FROM (SELECT xx = MAX (last_user_seek)
                               WHERE MAX (last_user_seek) IS NOT NULL
                              UNION ALL
                              SELECT xx = MAX (last_user_scan)
                               WHERE MAX (last_user_scan) IS NOT NULL
                              UNION ALL
                              SELECT xx = MAX (last_user_lookup)
                               WHERE MAX (last_user_lookup) IS NOT NULL
                              UNION ALL
                              SELECT xx = MAX (last_user_update)
                               WHERE MAX (last_user_update) IS NOT NULL) LA)
             FROM    master.dbo.sysdatabases sd
                  LEFT OUTER JOIN
                     sys.dm_db_index_usage_stats s
                  ON sd.dbid = s.database_id
            WHERE     name NOT IN
                         ('master',
                          'model',
                          'msdb',
                          'tempdb',
                          'distribution',
                          'DBA',
                          'SSISDB',
                          'SSISConfigDB')
                  AND name NOT LIKE 'Report%Server%'
                  AND name NOT LIKE '%foglight%'
         GROUP BY sd.name)
SELECT name,
       CASE
          WHEN ISNULL (last_access, DATEADD (dd, -11, GETDATE ())) <
                  DATEADD (dd, -10, GETDATE ())
          THEN
             'No Database Access in last 10 days'
          ELSE
             CONVERT (VARCHAR, last_access, 121)
       END
          AccessDateDetails,
       DATEDIFF (dd, GETDATE (), last_access) AS days_since,
       last_access
  FROM results;

Children

No Data