table and "Last Accessed" | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

table and "Last Accessed"

Anyone know of a way to find out when a specific table was last accessed….? -David Roesch
San Diego, Ca
You would need to add auditing with triggers or similair. There is no built-in function in SQL Server to find out when a table was last accessed. /Argyle
And also can take help of third party tool ENTEGRA from LUMIGENT which has useful representation of audit reports on the database. Check http://www.lumigent.com. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hi David, might be an overkill to what you want, but SQL Server comes along with a C2 auditing feature.
I’ve never done this myself, but you can read about it in BOL. At least, this could save you some money. HTH
Cheers,
Frank
One disadvantage of using SQL Server 2000’s C2 auditing functionality is its all-or-nothing approach. You can’t instruct SQL Server which event categories to audit, and you can’t instruct SQL Server to record access to particular objects (e.g., tables)—as you can in Win2K. If SQL Server can’t write to a log file—for example, if the disk contains no more free space—it will halt all execution. SQL Server won’t restart until it can resume logging. And the problem with C2 auditing is you need to set AUDIT LEVEL on SQL server to ALL which will write to the log and cumbersome to monitor the log. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hi satya,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />One disadvantage of using SQL Server 2000’s C2 auditing functionality is its all-or-nothing approach. You can’t instruct SQL Server which event categories to audit, and you can’t instruct SQL Server to record access to particular objects (e.g., tables)—as you can in Win2K.<br /><br />If SQL Server can’t write to a log file—for example, if the disk contains no more free space—it will halt all execution. SQL Server won’t restart until it can resume logging. <br /><br />And the problem with C2 auditing is you need to set AUDIT LEVEL on SQL server to ALL which will write to the log and cumbersome to monitor the log.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br />well, yes that’s the downside, but the functionality to do so is there.<br />That’s what I meant. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Cheers,<br />Frank
I agree, but by any chance its not recorded/maintained on SQL about last accessed information until you deploy such tools. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Maybe David can tell us, what he wants to achieve, so there might be another solution or workaround? Just wondering, because some time ago I asked somewhere else a similar question with the intention behind to control network admins’ activity. It turned out that I ‘simply’ had to remove BUILTIN/Administrators to get rid of this problem. Cheers,
Frank
Pardon me for my ignorance but will this auditing monitor table access as well? If it is a matter of deletion / updation / insert in the query, the same can be accomplished using triggers. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Wow, looks like I opened pandoras box.. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> <br /><br />I started at a new company, and they are experiencing some growing pains. There are some tables that I found after graphing the them that are not used. And even more from what I was told by someone here. But he doesnt know about all of them. <br /><br />So if there is an easy way, or process that wont drag the server down that could let us know what tables are "dead" we could clean up the model a lot..<br /><br />-David<br /><br />-David Roesch<br />San Diego, Ca
Hi David,
quote:
So if there is an easy way, or process that wont drag the server down that could let us know what tables are "dead" we could clean up the model a lot..
aah, things becoming clearer now. What about starting to check whether they are referenced by any other object?
I think, there is a sproc for this, but i don’t know its name right now. Cheers,
Frank
If you have any performance issues try to fine tune them, if the database growth is concerned then take help of tools or triggers whatever referred above and audit the events. If any, come again to this forum to resolve. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>