locked
Best option:Deleting old unused Databases from server / Auditing 'successful logins' by 'sql user' RRS feed

  • Question

  • Hello experts,

    I am working on a solution to detect DB's that have not been accessed for, say, 2months or more, and finally delete them and free up the space.

    Since I did not had any auditing enabled for 'successful logins', so I think i don't have any way to know which DB is not logged in by 'sql user' from long time. Is there any way? if so, please suggest?
     
    So, I am considering creating a AuditTable, that saves (i) LOGIN TIME (ii) DATABASE NAME to keep track of 'last login to db'

    I found some ways, that could be used:

    1. Enable 'Both successful and failed logins' option in 'Login Auditing'.
    2. Using 'LOGON TRIGGERS' .
    3. using sysprocesses.
    4. Sql Server 2008 Audit feature.

    In (1) error log size is an issue...need to increase its size also, and an overhead, as it stores multiple rows for even one 'successful login'.
    In (2) not sure but i think in logon triggers we can't access the DB as it if fired at connection to server, and not DB.
    In (3) schedule the job to read which DB is connected...how often it should be run. just an appropriation method....if job not schedued frequently.
    In (4), my db is 2005, last option, to upgrade it to2008 and use SQL 2008 AUDIT.

    Please suggest? 

     pardon me if this is not a relevant place to post my problem, no idea where this issue should actually come.....
    Monday, January 11, 2010 12:17 PM

Answers

  • It's not a complete waterproof option but check your index usage statistics.
    If you see no activity on your indexes (counters are reset after a restart of SQL Server) you can be pretty sure that nobody accessed the database.
    Of course, if you have indexes in your databases.

    Here is the script:
    SELECT
    last_user_seek = MAX(last_user_seek),
    last_user_scan = MAX(last_user_scan),
    last_user_lookup = MAX(last_user_lookup),
    last_user_update = MAX(last_user_update)
    FROM
    sys.dm_db_index_usage_stats
    WHERE
    [database_id] = DB_ID()
    • Proposed as answer by Kalman Toth Tuesday, January 12, 2010 10:35 PM
    • Marked as answer by Kalman Toth Saturday, January 16, 2010 6:51 AM
    Monday, January 11, 2010 2:28 PM