Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Unhappy Unanswered: need help to check when was database last accessed/updated

    I am trying to manage a sql server.

    In this sql server, I have about 200 databases.

    Is there any way/suggestions to check to see if some of the database has not been accessed/updated for the last month or longer?

    I am trying to develop a system to remove the database that's no longer needed.

    Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you talking about objects or data?

    Data...no way...unless you built it in with timestamps


    Objects...only if you drops and creates...

    I'm alway amazed at these questions........sounds like the OK Corral

    Corral?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You'd have to profiler the database you "think" aren't used for activity. Just eliminate them one-by-one until you get a list that hasn't been used for a month. In other words, query the table each day and add filters to limit the list so you're not having to continually profile databases you know are used. When you get down to the final list, start whacking them off.

    Oh, and BACKUP, BACKUP, BACKUP.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You serious?

    Would you want to profile 200 databases?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Nope. You know of another way Mr. Smartass? (grin)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Dec 2002
    Location
    Amsterdam
    Posts
    12

    Talking profiling? Nahhh

    eh.... take the databases whose transactions log don't grow offline? And sit near the telephone....

  7. #7
    Join Date
    Aug 2003
    Location
    California
    Posts
    18

    Talking My possible workaround.....

    I just thought about a workaround.

    What if I can grab the last login/access time for db user, and check if a user has not logged in for over a certain period of time, then I remove this user off the db.

    Loops through all the users and then do another loop, take the db that has no user id assigned offline and append the db name with the date that it was taken offline.

    Then do another schedule job to check the db name and see if any db has been taken offline for certain amount of time and decide whether to delete them or not.

    Either way, I need to be able to retrieve the last login/access time of a user id.

    I have checked the syslogins and sysxlogins table that it doesn't seem to contain this information.

    Does anyone know if mssql does provide such information?

  8. #8
    Join Date
    Oct 2003
    Posts
    84
    What about users that log in but don't do anything....You can keep your database "undeleted" just by logging in occasionnally without doing any actual work/accessing any data.

  9. #9
    Join Date
    Aug 2003
    Location
    California
    Posts
    18
    For those those logs in and not doing anything, that's okay, I can keep their.

    We are actually a membership-based company.

    So, once a member cancelled or does not renew the membership, we gotta find a way to clean up the server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •