the use history from table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » the use history from table

the use history from table

Rate Topic

Display Mode

Topic Options

Author

Message

fernandoibarra23

Posted Tuesday, November 10, 2009 12:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 01, 2010 7:28 AM
Points: 3, Visits: 7

Hello, I need to know the use history from tables.
Help me, please
Thanks a lot

Post #816731

Jack Corbett

Posted Tuesday, November 10, 2009 1:31 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Can you provide some more details? Do you mean how to use history tables? Do you mean how to find a history of who accessed a table and what they did?

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #816791

fernandoibarra23

Posted Wednesday, November 11, 2009 4:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 01, 2010 7:28 AM
Points: 3, Visits: 7

Hi Jack, thanks for your help.
To be more specific, I need to delete unused tables from my Data Base.

Post #817073

Jack Corbett

Posted Wednesday, November 11, 2009 5:07 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

There really is not a way to tell which tables have been used. You could set up a server-side trace, but how long would be long enough to run it to know if a table is used or not. Perhaps a table is used only once or twice a year? Unless you have major space issue and can't get more disk space I'd just leave it alone.

Post #817081

Joie Andrew

Posted Wednesday, November 11, 2009 2:39 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

If the tables on your database do not have a timestamp column that you can evaluate you have a few options. The first is what was already posted - run a trace against the database and find out what tables are being used. The downside is that this can take quite a while, and you still cannot be sure there are tables that are not modified yet still used ocassionally. Another option is to either create a change column on each table or a change table for the database and have triggers run to populate it with information on what table was used. Again the same problem is present though. I would say the only way to find out for sure what tables are not in use is to get with the users who use the program and the engineers who designed the database to determine what tables, if any, are no longer needed.

Is the purpose of deleting tables just to clcear up space? Because if that is the case, there are better options available, such as partitioning tables, archiving data, and/or deleting only records that are no longer needed.

Joie Andrew
"Since 1982"

Post #817475

fernandoibarra23

Posted Thursday, November 12, 2009 4:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 01, 2010 7:28 AM
Points: 3, Visits: 7

Hi,
I need to know statistics about the uso from my tables and my store procedura (specialy the stores procedures) becouse I want to update all my SP through a script.
The old (and unused) SP have same not exists column and this may be will fail my script.
Then, if the tables or SP are not used I can make decision.
I don´t need to clear up space, my goal es administration better my Data Base.
Thanks a lot.

Post #817720

Joie Andrew

Posted Thursday, November 12, 2009 2:37 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

Others can correct my thinking if I am wrong, but you may need to get with the engineers/developers who designed and built the system to best determine which stored procedures and/or tables that can be gotten rid of, if any. I would imagine they would know how to determine which ones are in use and which ones can be deleted. It may not be as easy as just seeing which ones have been used last depending on how the database is used.

Joie Andrew
"Since 1982"

Post #818135

« Prev Topic | Next Topic »

Permissions