user tables that are not longer used

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

user tables that are not longer used

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-04-20 : 12:28:57

Good morning

In a production system, there are hundred of user tables and the problem is that phisically there is no space left for filegroups that are running out of space.

so, im not a DBA but id like to know if theres a command (specially for sql server 2000) kind of system catalog that prints out a list of user tables that havent been used or accesed or affected some time ago for example 6 months ago, so i could drop those tables.

Thanks in advenced

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-20 : 12:38:06

Nope. Not easily done in SQL 2000. But if you suspect a table isn't used, rename it. Wait a week or a month. If no one squawks, drop it. But make sure you have backups.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 15:04:53

I think you've run server trace for some period and then find out unused objects in sql 2000 as you dont have any dmvs available in sql 2000

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alejo46
Posting Yak Master

157 Posts

Posted - 2012-04-20 : 17:21:00

if itsnt easy in sqlserver 2000 how would it be in 2008. that wuolbe possible and system voews and how ?

thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-20 : 18:15:36

In SQL 2008, I'll look at the cached execution plans, and SYS.DM_DB_INDEX_USAGE_STATS.

Note that this isn't foolproof. Nor is a trace, but it's a darn good start.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 12:29:15

quote:
Originally posted by alejo46

if itsnt easy in sqlserver 2000 how would it be in 2008. that wuolbe possible and system voews and how ?

thanks

for 2005/2008 you can use below

http://trycatchfinally.net/2010/01/finding-unused-tables-in-sql-server-2005-and-2008/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alejo46
Posting Yak Master

157 Posts

Posted - 2012-04-23 : 17:04:36

Thank you very much everyone of you for your help, it is really usfeul for me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 18:57:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources