Find Unused or Backup Tables

Question

Find Unused or Backup Tables

nirali_shastri89

SSC Enthusiast

Points: 182
More actions
March 18, 2024 at 12:00 am

#4383098

Comments posted to this topic are about the item Find Unused or Backup Tables

Viewing 7 posts - 1 through 6 (of 6 total)

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

DaveRogers Newbie Points: 5 More actions · Answer 1

'%_Old%' won't find what you intend it to find. The underscore means "any character". The literal value needs to be '%[_]Old%'.

Russel Loski SSCertifiable Points: 7058 More actions · Answer 2

One hiccup I've encountered using dm_db_index_usage_stats happens when I recently checked the table by doing a select. Suddenly I had a user_scan not related to production usage.

I try to remember to run this query before exploring the table.

A second approach was suggested in another blog (I have lost the reference). The writer defined a process whereby a job runs periodically, adding a record to a log table if there had been activity since the last time the dm_db_index_usage_stats query had been run.

Russel Loski, MCSE Business Intelligence, Data Platform

harriga.rabie-1008938 Old Hand Points: 319 More actions · Answer 3

hi All,

When maintenace tables is running it will update stats in these DMV, that is not a production usage , we can't eliminate it , so we can't declare the table is unsed.

for me we need to configure a audit per table ( lock shared for example , extend events ,..)

Meera SSCommitted Points: 1957 More actions · Answer 4

What exactly the scan means for the field "user_scans" being with the table "sys.dm_db_index_usage_stats" ?

Like, there are tables in our DB which aren't used for sure as the module is shutdown 4 years back but the DB holds all the tables related to that module. So, was trying to list out the untouched tables against the DB prior to upgrading our SQL. But found that user_scans for those untouched tables are getting updated on certain occasions. But unable to trace back the reasons.

So need some more info on what scenarios does these user_scans gets updated?

- Thank you!

harriga.rabie-1008938 Old Hand Points: 319 More actions · Answer 5

Hi All ,

fitst you need to disable all maintenance plan , perhaps your rebound or organise process all table without considering fragmentation .

also tha sal server audit can help you to identify the process that uses your tables

Jeff Moden SSC Guru Points: 1004053 More actions · Answer 6

harriga.rabie-1008938 wrote:

Hi All ,
fitst you need to disable all maintenance plan , perhaps your rebound or organise process all table without considering fragmentation .
also tha sal server audit can help you to identify the process that uses your tables

Heh... as a bit of a sidebar, if you're using the supposed "Best Practice" 5/30 method for Index Maintenance, you should just turn your index maintenance off because doing it wrong is a whole lot worse than doing no index maintenance at all. Even the guy that came up with those numbers came out with a disclaimer years ago... if nothing else, read the very last sentence of this article.

https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

I'll also tell you that REORGANIZE is mostly poison. It doesn't work the way most people envisioning it works and it actually sets up many different types of indexes for perpetual fragmentation.

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