Solved

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

Posted on 2014-01-22
3
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 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 69

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime is disastrous for companies and can lead to major hits on a brand, reputation, an…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

730 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