?
Solved

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

Posted on 2014-01-22
3
Medium Priority
?
225 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 2000 total points
ID: 39800062
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 70

Expert Comment

by:Scott Pletcher
ID: 39800454
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
ID: 39811444
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

568 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