Detecting Tables with no recent activity

Is there an efficent way to determine which tables in a SQL Server database
have had no activity reccently. This includes inserts, updates, deleted
and/or selects.
Are there any tools or techniques to find these tables?
All help appreciated!
Tim O wrote:
> Is there an efficent way to determine which tables in a SQL Server
> database have had no activity reccently. This includes inserts,
> updates, deleted and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
Not inherently, as far as I know. The system tables do not track changes
to underlying objects. You can easily implement some very simple
triggers on the tables you are trying to track using something like the
following. This implementation tracks changes to a table at most once
each day. Using the Ignore_dup_key option on the clustered index tells
SQL Server to ignore duplicates, preventing you from having to manually
check the table each time. You could, of course, improve on this concept
and add a user column and add the suser_sname() value to it and the
index.
CREATE TABLE [testing] (
[col1] [int] NULL)
go
create table TableAccess(TblNm nvarchar(128) NOT NULL, AccessDate
char(8) NOT NULL)
go
create unique clustered index TableAccess_idx on TableAccess (TblNm,
AccessDate) with ignore_dup_key
go
create trigger testing_change on testing
for insert, update, delete
as
Begin
insert into TableAccess (TblNm, AccessDate) Values ('testing',
CONVERT(char(8), getdate(), 112))
End
go
insert into testing values (5)
insert into testing values (6)
insert into testing values (7)
David Gugick
Imceda Software
www.imceda.com
|||"Tim O" wrote:

> Is there an efficent way to determine which tables in a SQL Server database
> have had no activity reccently. This includes inserts, updates, deleted
> and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
The use of TRIGGERS will not detect SELECTS that are executed on a TABLE as
far as I know!
I need an EFFICINIENT WAY to detect table that are neither read for written
to.
I am also concerned with the overheads that may be placed on the system.
There are a large number of tables and a high degree of daily activity!
Cheers Tim O
|||Tim O wrote:
> The use of TRIGGERS will not detect SELECTS that are executed on a
> TABLE as far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
> written to.
> I am also concerned with the overheads that may be placed on the
> system. There are a large number of tables and a high degree of daily
> activity!
> Cheers Tim O
If you are using stored procedures, you can log access from there, even
on SELECTs. The overhead is minimal on insert activity. You can place
the table on a secondary filegroup or in another database, if necessary.
David Gugick
Imceda Software
www.imceda.com
|||Tim,
There is a column in sysindexes for the object that keeps a count of
modifications to the table. The column is rowmodctr. SQL uses this to keep
track of when to run Update Stats when "Auto Update Stats" is on. If after
running update stats for that table, the value rises above 0, then there are
modifications to it. For more info please see:
http://support.microsoft.com/default...en-us%3B195565
As far as Selects, you can run profiler (trace) to see if it is called using
the Filter "TextData". You can also see if it is embedded in any stored
procs, triggers via sp_depends <table_name>. This won't indicate any
references from outside the current DB, tho.
Hope some of this helps.
Steve
"Tim O" <TimO@.discussions.microsoft.com> wrote in message
news:31F8C451-62F7-480F-9362-736E07698FFF@.microsoft.com...[vbcol=seagreen]
>
> "Tim O" wrote:
database
> The use of TRIGGERS will not detect SELECTS that are executed on a TABLE
as
> far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
written
> to.
> I am also concerned with the overheads that may be placed on the system.
> There are a large number of tables and a high degree of daily activity!
> Cheers Tim O

Design Question

I am creating a table and would like to know the most efficent way to do
this.
One of the colums will be text datatype and can have several thousand
characters in it (less than10k). The 'ntext' datatype in SQL and hold over
a million characters so that isn't a problem.
The question is: Is it more efficent to have the data directly in the
database or should I write it to a file and store a filename?
I would prefer storing the data directly in the table but if this is a
terribly bad idea I want to know. The problem I have with the file aspect
is maintenace of all the files and over years of use there could be quite a
few files generated.

Thank you in advance,
EricEric, in my opinion the choice of keeping the data in the database and
keeping only a reference to external data really depends on the use of
the data.

Will the files in question be updated after initial creation?
How often?
Are the updates complete file replacement or changes to data within the
file?
What are the chances multiple uses will attempt to update a file at the
same time?
How many different applications need access to the data?

The more changes, changes to only peices of the files, the more times
the data is accessed, and the more concurrent update activity the more
I would favor storing the data in the database.
IMHO -- Mark D Powell --|||Thank you for your response. Answers to your questions.

> Will the files in question be updated after initial creation?
Yes
> How often?
A small subset would rarely be updated.
> Are the updates complete file replacement or changes to data within the
> file?
The updates would be edits only.
> What are the chances multiple uses will attempt to update a file at the
> same time?
Infrequent.
> How many different applications need access to the data?
1

I favor the database method too, despite the fact that the updates would not
be a frequent.
Partly for security, easy to back up the database compared to many files.
My concern was eventual database size, but there must be many large database
that store records at least as large as I mentioned.

Thanks again,
Eric|||Beringer (borden_eric@.invalid.com) writes:
> I am creating a table and would like to know the most efficent way to do
> this.
> One of the colums will be text datatype and can have several thousand
> characters in it (less than10k). The 'ntext' datatype in SQL and hold
> over a million characters so that isn't a problem.
> The question is: Is it more efficent to have the data directly in the
> database or should I write it to a file and store a filename?
> I would prefer storing the data directly in the table but if this is a
> terribly bad idea I want to know. The problem I have with the file
> aspect is maintenace of all the files and over years of use there could
> be quite a few files generated.

There are people who quite strongly recommend storing only the the filename
in the database, and the text as separate files.

I am not one of those. The point with storing on file is mainly simplicity
in programming. The text datatype is messy to work with, not the least in
ADO.

On the other hand, you perfectly right that backing one database is
easier than backing up one database + lots of files. Add to that you
cannot get transactional consistency with a file system, and that a
file may disappear without the knowledge of SQL Server.

Thus, in my opinion, storing only the filename is a low-budget solution
when your requirements are lax. Storing the data in the database may
require more development, and, yes, it can also have some negative
effect on performance. But it is a more robust solution.

And in your case, since your texts are no more than 10000 chars, it's
not really an issue. Go with ntext.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL server is capable of handling that sort of data in a column.. So I
would suggest holding it in the database rather than putting them in a
filesystem. What will happen if you need to migrate things to a
different server.. or one of the path to that folder changes...? Plus
if you need to remove the data, you need to delete the file plus the
path/file name reference in the database.. Things can go wrong if you
dont stay consistant with all the updates... Plus, if more than one
person need to update one record in a particular time(dont ask me how
often thats gonna happen... but as an architect you need count that
into account when making a decision) that will complicate things...!
Plus its easy to do searches of a text in sql than thru windows
search....