Post reply

when a databases was last used

  • May 19, 2007 at 2:52 pm

    #61303

    I have a question on how programatically to determine when the database was last used/accessed. I have an old test server and there are huge amount of databases no longer in use that I would like to clean up.

    Thanks a lot.

    mj

  • May 19, 2007 at 4:18 pm

    #707661

    There is no way you can tell this unless you constantly running profiler/trace...

     

    MohammedU
    Microsoft SQL Server MVP

  • May 19, 2007 at 5:42 pm

    #707665

    Thanks a lot, but I cannot run the Profiler 24/7.

    What about for some work around?

    This is what I currently do for Oracle - I have an after logon trigger that checkes and inserts/updates a record per each login for the users that is not in my list. This way I could pull a report to show me who was loggin and when. For DB2 (Unix only) I check the time stamp on the log file and again, I get what I need.

    Can I find such a work aroung for SQL server?

    Are the loggins logged somewhere in the data dictionary?

    What about on the OS level?

    Thanks again, mj

  • May 20, 2007 at 3:18 pm

    #707702

    of course you can. create lite-weight sql side trace...it will have very minimal impact on your system

  • May 21, 2007 at 4:42 am

    #707767

    Hi, I have the same issue here to a degree. I review the dates the db's were created and use my knowledge of on going projects to try and determine if a db is in use. I send out a monthly email asking for feedback etc and the response is usually poor and from the same people. So.... if I don't think a db is been actively used, I back it up and detach it and if someone shouts I re-attach. After another period I delete the detached db's. So far no one has broke me chops over this approach. Other than that as perviously stated, a lite-weight profiler. Rgds Derek

  • May 21, 2007 at 7:22 am

    #707803

    Well there is a way of course to figure it all out!  Detaach all the databases and then reattach when someone screams. 

     

    Although a server side trace is most likely a more acceptable solution.... There is very little overhead on a server side trace, and on a test/dev server it would not concern me at all to have one running all the time to figure this out.

    I had one running in production that logged all the stored procedures being used for 6 weeks with zero noticeable performance issues.  Use a script to start it so you don't have to have the client running at all times, as I have seen it cause problems.

  • May 21, 2007 at 7:44 am

    #707814

    You can set them to auto-close if you think they aren't being used and then if they get accessed, the log will reflect it opening. Don't do this for systems that you are sure are working.

  • May 21, 2007 at 7:53 am

    #707821

    Actually Steve that is a very good idea for a test environment as you can take a performance hit during the opening the database etc. Thanks Derek

  • May 21, 2007 at 11:03 pm

    #707959

    You can you same DDL Triggers in SQL server 2005 as you were using in Oracle

  • May 22, 2007 at 9:40 am

    #708114

    Thanks a lot for the ideas, guys.

    Any help on what exactly I need to select in the light trace for log on?

    Thanks again, mj

  • May 23, 2007 at 4:28 am

    #708274

    Choose an event to use this code. For example, Form_load or SaveButton_click. It will create a file in the specified place to tell you when the program was opened or closed. (Make the necessary changes to have the required results.

    Dim timedate As Date = Now

    My.Computer.FileSystem.WriteAllText("C:\Log.txt", vbCrLf & " The program was opened at :  " & timedate, True)

     

Viewing 11 posts - 1 through 10 (of 10 total)

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