Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-01-22
3
Medium Priority
?
207 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

564 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