Solved

Microsoft SQL 2005 non used or static data tables - how do I find?

Posted on 2014-01-22
3
178 Views
Last Modified: 2014-01-27
Hi All

I am looking for a way to reduce the size of my SQL backups, I have a massive 400gb backup going on every night and I am sure most of that is static data.  My question is how do I monitor the tables to find out which ones are static or unchanged for a month or so.  If I can find which ones are static then I can concentrate the backups on the changed tables and even create ndfs to suit.

Are there any tools built in, or are there any free tools available to do this task?

Many thanks
0
Comment
Question by:Eric
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
Comment Utility
There is no easy way out for this

Option 1:

Best of the lot

Take a week old backup restore it as DB1
Take the current backup and restored it as DB2.

Now run the database compare between DB1 and DB2 http://dbcomparer.com/
All those tables where the data has changed are not static and the tables where the data has changed can be considered as static

Option 2:
if you are using enterprise edition then enable CDC on all of your SQL tables, and later check the results on the CDC (after a week ) and see which tables are being used and which are not.

Option 3: Highly not recomended in production env.
Create triggers on all tables and let them write to a audit log table and check that table later.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
I suggest looking into doing just differential backups 6/most days of the week, with a full backup only on the least-busy day(s).  Or some other full vs. differential schedule that matches your needs.
0
 

Author Closing Comment

by:Eric
Comment Utility
Many thanks for the suggestions, the file comparison option is the way I have gone - the dbcompare application was new to me, but has worked a treat.
0

Featured Post

Don't lose your head updating email signatures!

Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users do...so should you!

Join & Write a Comment

Introduction: Sometimes when I receive a call from my users to solve their problems it is very difficult for me to found their computer IP address. Even finding their computer Host to provide remote support can be a problem.  So I resorted to Goo…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now