Automate reports for when the SQL database was last used

  • Hi,

    I'm working on cleanup activity of deleting databases that aren't in use or not used for a while.

    I would like to do an automation to check this every once in 3 months and get the details on email or excel so I process further for cleanup. Have to validate on 100+ SQL servers.

    Can someone help me how this can be achieved.

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • First, I hope that you're taking a final full backup followed by a "Tail Log" backup to properly shut down a database prior to drop it.

    As for determining if the database has been used or not, that's actually more difficult that you might imagine.  All evidence of usage is wiped clean if the server is restarted OR the SQL Server Service is restarted for any reason (such as regularly scheduled Windows updates.

    With that being understood, we need to accumulate the data in a permanent table and create a bit of code to populate it with.  I'll leave the actual reporting up to you (you have to have some of the fun 😉 ).

    And, just to say it again, I'm am not responsible for anything having to do with this code or how someone uses it. PERIOD.  It does no deletes on its own and it's up to the user to determine if the data is appropriate for use and how to use it. PERIOD.

    Details are in the comments.

    /******************************************************************************
    Purpose:
    Collect the lastest usage dates for all databases except for master, model,
    msdb, and tempdb and saves the dated entry for each database in the related
    dbo.DbUsageHistory which, of course, must already exist.

    Note: This has not been tested on databases that use "In Memory" or any
    Columnstore tables and does not check for table-less proc usage.
    It samples sys.dm_db_index_usage_stats for index usage, which relates
    to the tables which relates to the databases.
    -------------------------------------------------------------------------------
    Dev Notes:
    1. This script may be turned into a stored procedure or, if the INSERT clause
    is removed, into an iTVF (inline Table Valued Function) or view.
    -------------------------------------------------------------------------------
    Usage:
    Note that the author of this code is NOT responsible for any problems or
    issues that occur by using this code in any way, shape, or fashion. PERIOD!

    1. Create the history table in your "DBA" database using the following code:

    CREATE TABLE dbo.DbUsageHistory
    (-- Column names with underscores are
    -- the same as from system tables.
    SampleDateTime datetime NOT NULL
    ,database_id smallint NOT NULL
    ,last_user_seek datetime NULL
    ,last_user_scan datetime NULL
    ,last_user_lookup datetime NULL
    ,last_user_update datetime NULL
    ,MaxSampleUsageDate datetime NULL --Max of the 4 dates above
    ,CONSTRAINT PK_DbUsageHistory
    PRIMARY KEY CLUSTERED (SampleDateTime,database_id)
    )
    ;

    2. Create a job to use the following code (or converted to stored proc, iTVF,
    or View) to sample the database usage dates on a regular basis.

    3. Create a report (code not inclued here) to get the maximum
    MaxSampleUsageDate date for each DB.
    Remember that this report is based on data that disappears (restarts) after
    every server reboot or restart of the SQL Server service. It is recommended
    that no action be taken base on the report until an adequate number of days
    have passed to see if rarely used databases are used at all.

    4. You may want to setup some form of "rolling delete" on the related history
    table or do the occasional "TRUNCATE" to "Start Over" while remembering to
    allow some good period of time (95 days or more to cover "once per quarter"
    usage, IMHO) to pass.
    -------------------------------------------------------------------------------
    Revision History:
    Rev 00 - 21 Sep 2024 - Jeff Moden
    - Initial creation (based on previous code for indexes) and unit test.
    - Ref:https://www.sqlservercentral.com/forums/topic/automate-reports-for-when-the-sql-database-was-last-used
    ******************************************************************************/ WITH cteUsed AS
    (--==== Get the max usage dates for all used databases.
    -- Will return ALL NULLs for any database that hasn't
    -- used as of the sample date after the last restart.
    SELECT SampleDateTime = GETDATE()
    ,db.database_id
    ,last_user_seek = MAX(usg.last_user_seek )
    ,last_user_scan = MAX(usg.last_user_scan )
    ,last_user_lookup = MAX(usg.last_user_lookup)
    ,last_user_update = MAX(usg.last_user_update)
    FROM sys.dm_db_index_usage_stats usg
    RIGHT JOIN sys.databases db
    ON usg.database_id = db.database_id
    WHERE db.database_id > 4 -- Exclude 4 main system databases
    GROUP BY db.database_id
    )
    INSERT INTO dbo.DbUsageHistory
    (
    SampleDateTime
    ,database_id
    ,last_user_seek
    ,last_user_scan
    ,last_user_lookup
    ,last_user_update
    ,MaxSampleUsageDate
    )
    SELECT -- This gets the max of the 4 dates and
    -- echoes the other dates for the INSERT
    usd.SampleDateTime
    ,usd.database_id
    ,usd.last_user_seek
    ,usd.last_user_scan
    ,usd.last_user_lookup
    ,usd.last_user_update
    ,mx.MaxSampleUsageDate
    FROM cteUsed usd
    CROSS APPLY (-- Finds the max of the 4 dates for each row.
    SELECT MAX(last.UsageDate)
    FROM (VALUES
    (last_user_seek)
    ,(last_user_scan)
    ,(last_user_lookup)
    ,(last_user_update)
    )last(UsageDate)
    )mx(MaxSampleUsageDate)
    ;

    --===== Just to see what we have
    SELECT * FROM dbo.DbUsageHistory
    ;

    --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)

Viewing 3 posts - 1 through 2 (of 2 total)

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