Solved

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

Posted on 2014-01-22
3
185 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article was originally published on Monitis Blog, you can check it here . 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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

729 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