How to find last accessed Date of a database ??? on sql server 2000

  • need to delete Databases that are no more used by any one

    because we are planning to migrate to 2005 so we want to make sure to migrate those that are used

    so what is the way to find out the last accessed date of the database

    please help me

    i need to do this as soon as possible

    Thanks in advance

    Gokul Krishna

  • Set up a script or proc that reads sysprocesses every 30 to 60 seconds to check for the presence of "Active" spids. For that matter, you could run sp_who2 or syslocks and compare the output to 60 seconds ago.

    --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.


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

  • Or just disable them and see who complains.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • thanks

    but how do i find what database they are accessing

    WOW i can not disable and check that is not feasible for me

  • Ummm.... SysProcesses has a DBID column...

    --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.


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

  • You would have to monitor your dbs access for a certain period; there is not a straight way to "select last transaction date" from a db.

    What I would do: create some temporary tables into a DBAdmin db and save here results for:

    Declare @DbName sysname

    select @DbName ='your_db' --create cursor for as many dbs you have

    select sp.loginame, sp.login_time, sp.last_batch, sp.hostname

    from master..sysprocesses sp, master..sysdatabases sd

    where sp.dbid = sd.dbid and sd.name = @DbName order by sp.last_batch desc

    Hope this helps

  • michaela (6/15/2008)


    You would have to monitor your dbs access for a certain period; there is not a straight way to "select last transaction date" from a db.

    What I would do: create some temporary tables into a DBAdmin db and save here results for:

    Declare @DbName sysname

    select @DbName ='your_db' --create cursor for as many dbs you have

    select sp.loginame, sp.login_time, sp.last_batch, sp.hostname

    from master..sysprocesses sp, master..sysdatabases sd

    where sp.dbid = sd.dbid and sd.name = @DbName order by sp.last_batch desc

    Hope this helps

    No... no cursor required... SysProcesses monitors all DB's. No need to step through DB's.

    --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.


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

  • Short of using a procedure or TSQL to query sysprocesses the easiest and safest thing if to just ALTER the database OFFLINE. It is relatively harmless unless soimeone is actually using the database and can be undone in seconds.

    A word of note about using sysprocesses ... it is a good method but not infallible since views may encompass tables/views from other databases than that shown in sysprocesses.

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

  • Thanks I think this will help me

    i will try this and get back 2marrow with questions if any

    thanks a lot

    Gokul Krishna

  • Well for me I think this could be done in a very simple way, why don't you check at OS level when was the last time de MDF file was modified and then you will have the last date when somebody accesed it???

    just a Though

  • Emilio.Mendoza (7/8/2008)


    Well for me I think this could be done in a very simple way, why don't you check at OS level when was the last time de MDF file was modified and then you will have the last date when somebody accesed it???

    just a Though

    Sorry, but READS don't affect the OS date and any JOBS would give false info.

    And I don't believe that writes change the OS file date unless the DB file's size is increased.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Hello,

    I have created two select statements listing accessed databases and non-accessed databases according to the processes running on the SQL Server for SQL2005 or SQL2008

    Here is the two select statements

    with cte

    as

    (

    select

    DB_NAME(dbid) dbname,

    rn = ROW_NUMBER() OVER (

    PARTITION BY DB_NAME(dbid) ORDER BY last_batch DESC

    )

    from master..sysprocesses

    )

    select *

    from cte

    where rn = 1

    order by dbname

    select

    d.name

    from sys.databases d

    left join master..sysprocesses p

    on p.dbid = d.database_id

    where p.dbid is null

    order by d.name

    Eralper

    T-SQL Development

  • There is one sitiation that this SQL will miss. Executing SP in database A to access database B via 3 part naming - I know because we have lots of applications that do that !

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

  • Sounds like an ideal solution would be to use the SQLProfiler, filtering on the Databases you require.

    Try and limit what you select. Then you can see who access the db - you could even check the prepare sql for the databases to see what queries are being done - select or update

  • Thanks for the reply, is there a script to obtain this information in SQL 2000?

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

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