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

Question

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

rak_gokul

Mr or Mrs. 500

Points: 592
More actions
June 14, 2008 at 6:42 pm

#70322

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

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

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

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]

rak_gokul Mr or Mrs. 500 Points: 592 More actions · Answer 3

thanks

but how do i find what database they are accessing

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

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)

michaela SSCertifiable Points: 5649 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

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)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

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

rak_gokul Mr or Mrs. 500 Points: 592 More actions · Answer 8

Thanks I think this will help me

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

thanks a lot

Gokul Krishna

Emilio.Mendoza Mr or Mrs. 500 Points: 576 More actions · Answer 9

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 10

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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 11

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

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 12

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

cloud-754230 SSC Veteran Points: 219 More actions · Answer 13

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

Emilio.Mendoza Mr or Mrs. 500 Points: 576 More actions · Answer 14

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