Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cleaning Up Garbage from Data Expand / Collapse
Author
Message
Posted Sunday, February 21, 2010 10:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 11:34 AM
Points: 23, Visits: 53
Comments posted to this topic are about the item Cleaning Up Garbage from Data
Post #870051
Posted Monday, February 22, 2010 2:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 11:34 AM
Points: 23, Visits: 53
Cleaning Up Garbage from Data is my first article to SQLServerCenter. Waiting for valuable comment.


Regards,

Shubha
www.freegamingnow.com
Post #870156
Posted Monday, February 22, 2010 3:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 6, 2014 1:47 AM
Points: 292, Visits: 1,084
Simple and nice to understand.

Post #870198
Posted Monday, February 22, 2010 4:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 11:34 AM
Points: 23, Visits: 53
Thanks singhsatnam84

Regards,

Shubha
www.freegamingnow.com
Post #870207
Posted Monday, February 22, 2010 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 16, 2012 10:04 AM
Points: 3, Visits: 17
This looks very useful. Thanks for posting.

One enhancement would be to check for the table names that were entered for exclusion to make sure that they exist before running the delete/truncate. Otherwise, someone may end up deleting contents from a table that they wanted to keep just because they misspelled the table name.
Post #870282
Posted Monday, February 22, 2010 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 11:34 AM
Points: 23, Visits: 53
Thanks sjkln you are right. This is a very common scenario.

Regards,

Shubha
www.freegamingnow.com
Post #870289
Posted Monday, February 22, 2010 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 7:58 AM
Points: 3, Visits: 11
Not, what I was expecting
Post #870393
Posted Monday, February 22, 2010 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 11:34 AM
Points: 23, Visits: 53
What you expecting ?


Regards,

Shubha
www.freegamingnow.com
Post #870507
Posted Monday, February 22, 2010 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 13, 2012 7:23 AM
Points: 22, Visits: 349

If there are many foreign key relationships and the tables end up being deleted, beware. Deleting is a logged operation and your log file will go through the roof not to mention this will be a very lengthy operation. I'd suggest if there are only four tables, that you consider copying them from the populated database to any empty database. We keep a schema only copy of our production database for a similar purpose. We take limited records from almost every table and repopulate an empty db so that we're left with a much smaller version of our production database. As an example, one of our dbs is 400Gb. After our process, the database is only 15-20Gbs. This process takes only a few hours. Deleting the data would take MUCH longer and result in a very large log file.

Alternately, consider scripting all foreign keys, dropping all foreign keys, truncate tables not needed, and then reapply foreign keys.

Paul
Post #870508
Posted Monday, February 22, 2010 10:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 27, 2011 11:34 AM
Points: 23, Visits: 53
Paul thanks for your reply. no of table limited to 4 is just a example. it may be more than that. U r right that this is a lengthy operation. How you solve ur problem that depends on lots factor. May be don't have a right to create different DB. to copy those table into new one. It's depend on Situation, company policy.

As your solution is well excepted. Its a nice one. I am just sharing my solution.

thanks for your contribution.

Regards,

Shubha
www.freegamingnow.com
Post #870526
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse