Unused Databases, Last accessed DB

  • Hi,

    How do i find the unused databases or last accessed datetime of databases. I want to find it and drop all the databases which are not accessed for past 3 months.

    Please help me.

  • I assume you are talking about a wild, non-secure development box.

    Shutdown the suspected databases, wait three month, if nobody comes crying foul take a full backup and drop it.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • 😎

    My Manager asked me to check and drop the databases which are not used for past 3 months.

    How do i do it?

  • Set up a profiler trace and filter it for the databases you need to drop and check whether you see any connections for these databases? Do this activity for good amount of time and then decide accordingly.

    MJ

  • What I do on servers that I suspect the databases/logins are not being used is setup a DBManagement database. That gets populated with connection information every min by SQL Agent jobs so I can later run queries against the tables in DBManagement to see what is in use and what is not.

    Not recommend on heavy used servers; as the job running every min would be too much performance impact; question is how often you think is enough?

    Thanks.

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • In sql 2005/2008 and the service us running for a long time

    you can run this script, and you can get an idia of wht is hapenning:

    --time sql service started

    select last_batch from sys.sysprocesses where spid=1

    ---

    SELECT db_name(d.database_id),MAX(last_user_SCAN),MAX(last_user_Seek),MAX(last_user_lookup),max(last_user_update)

    from sys.databases d

    left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id

    group by db_name(d.database_id)

  • A somewhat non-intrusive method is to set the databases to 'AUTO CLOSE'.

    Then all you have to do is scan the errorlog to see which ones are used !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • In SQL 2008 u could log/audit any statements run across the suspected DB's.

    Or just.

    You could use a trigger on the connection event.

    But the auto-close idea could be a Q and D fix.

    Carlton..

  • yehuda klein (3/31/2009)


    SELECT db_name(d.database_id),MAX(last_user_SCAN),MAX(last_user_Seek),MAX(last_user_lookup),max(last_user_update)

    from sys.databases d

    left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id

    group by db_name(d.database_id)

    Thanks for this script yehuda; it gives me what I need. 🙂

  • rudy komacsar - Doctor "X" (3/31/2009)


    A somewhat non-intrusive method is to set the databases to 'AUTO CLOSE'.

    Then all you have to do is scan the errorlog to see which ones are used !

    Best solution I've ever read on this forum.

    However it assumes you don't need the hd space right away. In that case backup and send to dvds or where you can for quick recovery then drop from server. Just make sure you tell your boss that recovery time will be in the hours range rather than seconds (or ms with Rudy's solution).

  • yehuda klein (3/31/2009)


    SELECT db_name(d.database_id),MAX(last_user_SCAN),MAX(last_user_Seek),MAX(last_user_lookup),max(last_user_update)

    from sys.databases d

    left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id

    group by db_name(d.database_id)

    NB. That DMV is flushed when the DB is started. Offline/online, detach/attach, close/open, restore, restart of SQL all will set the seeks and scans dates to null.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If the last_access column is null then no reads or writes have occurred:

    WITH cte AS (
    SELECT database_id, dt, op
    FROM sys.dm_db_index_usage_stats
    UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
    SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
    FROM sys.databases d
    LEFT JOIN cte c ON d.database_id=c.database_id
    CROSS JOIN sys.dm_os_sys_info i
    WHERE d.database_id>4
    GROUP BY d.name
    ORDER BY d.name;
  • francesco.mantovani wrote:

    If the last_access column is null then no reads or writes have occurred:

    WITH cte AS (
    SELECT database_id, dt, op
    FROM sys.dm_db_index_usage_stats
    UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
    SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
    FROM sys.databases d
    LEFT JOIN cte c ON d.database_id=c.database_id
    CROSS JOIN sys.dm_os_sys_info i
    WHERE d.database_id>4
    GROUP BY d.name
    ORDER BY d.name;

    Heh... especially in the first several milli-seconds or maybe even days after you've done a restart of the SQL Server Service or the box it's on.  For that matter, you could have important databases that are used only once per month or even less.

    I strongly recommend that if you use this method to determine if a database is still used or not that 1) you make damned sure that you have a viable, quickly restore-able backup that you've tested for restore-ability and 2) that your resume is up to date and 3) that you stand a chance of actually getting a job after you use this method to drop supposedly unused databases. 😉

    Also... while I always appreciate someone adding to a more than a decade old thread with additional information, you might not be aware of the fact that this thread was last active more than a decade ago prior to your post on it.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 13 posts - 1 through 13 (of 13 total)

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