Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Unanswered: how to tell if accessed

    I just got assigned to be our companies sql server dba, there are about 80 databases on various servers. I am suspecting many are not even used. Is there a way for me to tell the last time they were accessed and by whom? And, more importantly, the last time any changes have been made in the database? Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Let me guess. The old DBA quit suddenly without leaving any documentation?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    5
    Actually, there has never really been a sql server dba here. I'm new, and like all the other dbas,we are life long oracle dbas. But, someone has to get these under some control. Until now, all microsoft has been left in the hands of the developers to admin. Fox guarding the hen-house so to speak. Nobody wanted to touch it until I came along. (oh, and the dozens of developers involved have come and gone over the years, not much help)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Inheriting 80 databases on multiple servers in lord knows what state of maintenance, you folks might consider hiring a SQL Server DBA...
    Having gone from SQL Server to Oracle I can tell you that there will be some learning curve, and what you are describing sounds like a full-time position.

    As far as telling whether a database is being used, that is difficult after the fact. You can set up tracing or auditing on it to track future access.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's a lot of licensing..

    Do you have an inventory of all the boxes?

    I would start there. Are they physically located near you? Or is it remote?

    AND I would change the sa password and see what comes out of the wood work.

    AND I would revoke any sa level priviliges.

    Do you have a traditional server landscape?

    Development, Production, Contingency?
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What's that awful grinding sound I hear? A room full of developers gnashing their teeth?

    Heh heh heh...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'd check when they were last backed up, then build a custom trace to track logins and db's that they access. Very often DB info is not available in the trace if you use one of the predefined templates, so carefully select the events to ensure that db info is captured.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Nov 2004
    Posts
    5
    Thanks for all the info. Everything is fine, backups have been taken care of and are scheduled now, and licensing was already addressed. SA passwords were changed and I'm working on the multiple file shares where data files reside with full access for Everyone who maps to it. Internal database privileges are next.

    I've tried setting up a trace, but can only figure out how to track the login which, as stated, does not really show which database they are connecting to. These are all version 2000(8.0) and if anyone can help me with an event that will show me what database is being connected to, I'd appreciate it.

Posting Permissions

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