Finding Out Latest Used Objects in a Database

Question

Finding Out Latest Used Objects in a Database

Ryan Christopher Surrao-235526

Grasshopper

Points: 13
More actions
June 3, 2005 at 7:28 am

#86474

Hey There
I have a scenario where in i need to find the latest used objects in a database... Any ideas on how to approach it???
Ciao
Ryan
Kindest Regards,
Ryan Christopher Surrao
MCSE (NT), CCNA, MCDBA, MCAD.NET, MSCD.NET

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You'll pretty have to fire up the profiler (saving the trace in a table). Then query that table as needed to find the last used object.

Ryan Christopher Surrao-235526 Grasshopper Points: 13 More actions · Answer 2

Hey Remi

Thanks for the input but thats something that i want to avoid... Running the profiler on a 24/7 environment is not something i wanna do

Thanks anyway

Ryan

Kindest Regards,

Ryan Christopher Surrao

MCSE (NT), CCNA, MCDBA, MCAD.NET, MSCD.NET

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I agree, but here are the other options :

Create an audit table.

Create a stored procs that can enter the needed information to log.

Then in EVERY stored proc, call that audit proc.

The problem with that solution is that it doesn't audit selects (unless made in proc), it doesn't audit views, nor direct access to the tables (god forbid) and it doesn't audit functions.

The other possibility would be for you to purchase a log explorer than can read the transaction log. But that again doesn't log the select statements.

Is this something you want to run permanently or just a temp measure to catch something / someone?

Ryan Christopher Surrao-235526 Grasshopper Points: 13 More actions · Answer 4

Hey Remi

U r right about the catching someone or something part

But the thing is the other person knows a little bit about sql and works on it too...

I need something at the server level which i know they cant tamper with...

Thanks A Lot

Kindest Regards,

Ryan Christopher Surrao

MCSE (NT), CCNA, MCDBA, MCAD.NET, MSCD.NET

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I assume here that this is something important. I would then suggest that you run the trace, maybe to a file so that he can't access it (save it on your personal station). Then you can transfer that trace to a table for "research".

If you are trying to catch someone, you could put a filter on the login. That way it would at least reduce the write count to the disk and reduce the impact on the server too.

For the trace this is your call... Is the catch more important that the absolute performance of the server?? Is the server not able to handle the trace for a few hours/days??

Just another hint.. is there anything in the windows log or sql logs that can help you catch him (like login failure on the sa account??). Are you also auditing the login failure/success on the server??