last time table is accessed

Question

last time table is accessed

HanShi

SSC-Dedicated

Points: 33507
More actions
September 18, 2008 at 7:23 am

#71983

Hello,
I'm given the task to administer an existing (SQL-2000) database. Users are accessing this DB through an application and I don't have the code of this application. When looking at the tables in the database I suspect some of them are not used anymore (table names like "TestDate" or "tmpState").
I know I can set up a trace and log all statements that use a specified table name. If a statement is logged, the table is used. But this trace has to run for a year just to be sure the table isn't used only once a year.
Is there another (faster) way to know what was the last time the table is used (either read or write)?
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

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

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

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 1

I am actually faced witht the same task. Although I have not developed anything yet, one approach I am thinking about is setting up triggers and then writing to a log file (or table) whenever a DML command is executed on the table.

I also would like to hear of alternate methods others may have.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

I also thought of triggers. The downside of triggers is that they don't act on SELECTs. So if a table is only accessed by SELECT statements (for reference or archive tables), nothing will be logged. 🙁

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

bkDBA Ten Centuries Points: 1320 More actions · Answer 3

Unless there are issues, such as with diskspace or customizations needed, I'd leave the tables alone. There are far more important things to concern about 🙂

But if you really need to know, I would install a SQL Server 2008 180-day trial somewhere, import your database and use the new change-capture features of SQL Server 2008.

_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees

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

Ummm... that'll find the last date a table was SELECTED from?

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

bkDBA Ten Centuries Points: 1320 More actions · Answer 5

Jeff Moden (9/18/2008)
Ummm... that'll find the last date a table was SELECTED from?

Yes, provided the new 2008 database audit events are set up:

http://blogs.msdn.com/euanga/archive/2008/05/28/sql-2008-how-do-i-tell-if-a-specific-table-is-still-being-used-in-an-app.aspx

2008 BOL says: "SQL Server audits consist of zero or more audit action items. These audit action items can be either a group of actions, such as Server Audit Change, or individual actions such as SELECT operations on a table."

This will not retroactively tell you who accessed what, but it might be faster and/or easier than parsing through a trace log.

_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Hanshi and Cy

A technique I have used in SQL 2000 to determine which tables are used or not used is contained in Forum posting

http://www.sqlservercentral.com/Forums/Topic562824-145-1.aspx#bm564071

I will be fair and honest, if implimented as explained in the posting it will record all T-SQL events as it is invoked from each T-SQL statement. Each SQL select statement will have to be modified to include a call to the SQL statement given in the above posting. For deletes/inserts/updates you can invoke the procedure from within a trigger, to some extent lightening your work load. Depending upon the number of select statements it can be a lot of work, probably more than most would like to undertake. If the accessing application uses dynamic SQL it will not track those activities exept by using a trigger. But it is an idea, which in turn might prompt something you can do in your individual cases.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 7

I'd rename the table. That will tell you if it's still being used.

Quick.

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 8

bkDBA (9/18/2008)
Jeff Moden (9/18/2008)
Ummm... that'll find the last date a table was SELECTED from?
Yes, provided the new 2008 database audit events are set up:
http://blogs.msdn.com/euanga/archive/2008/05/28/sql-2008-how-do-i-tell-if-a-specific-table-is-still-being-used-in-an-app.aspx
2008 BOL says: "SQL Server audits consist of zero or more audit action items. These audit action items can be either a group of actions, such as Server Audit Change, or individual actions such as SELECT operations on a table."
This will not retroactively tell you who accessed what, but it might be faster and/or easier than parsing through a trace log.

Thanks. Good info. Have you actually tried it? Not trying to be a smart guy about this... I've heard that the auditing features of 2k8 are world class and would really like to know. 🙂

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

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 9

Steve Jones - Editor (9/18/2008)
I'd rename the table. That will tell you if it's still being used.
Quick.

Heh... that's my favorite method. But, I believe the OP might have a problem with renaming ALL the tables in a database to see which ones returned complaints. Basically, it would break the entire database and all associated apps.

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 10

Steve Jones - Editor (9/18/2008)
I'd rename the table. That will tell you if it's still being used.
Quick.

If you suggest this, I know another approach. Rename all tables and change it back to the original name only when business is complaining:cool:. This will keep your phone ringing for the next few days/months though...:P And you won't be friends with any business collegues anymore.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33507 More actions · Answer 11

I will start with a combination of two approaches. First I will run a trace for a week or two that captures all statements with the tablename(s). If no statements are captured I will continue with the renaming suggestion. I will make a task in my agenda to delete the table next year (if no one complained).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

cy-dba SSCarpal Tunnel Points: 4179 More actions · Answer 12

bitbucket (9/18/2008)
Hanshi and Cy
A technique I have used in SQL 2000 to determine which tables are used or not used is contained in Forum posting
http://www.sqlservercentral.com/Forums/Topic562824-145-1.aspx#bm564071

Thanks for the lead...I'll check it out.

bkDBA Ten Centuries Points: 1320 More actions · Answer 13

Thanks. Good info. Have you actually tried it? Not trying to be a smart guy about this... I've heard that the auditing features of 2k8 are world class and would really like to know. 🙂

No, I've not tried these tools in production yet. I'm researching and getting ready to try them out in a development environment. I've been so impressed with SS2K8 that I'm considering skipping SS2K5...(we're a bit behind here and still in 2000).

_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees

Steve Jones - SSC Editor SSC Guru Points: 736362 More actions · Answer 14

I'd definitely look at SS2K8. There are bugs, but overall it's a more mature, stable version of SS2K5.