Cleaning Up Garbage from Data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Shubhajyoti Ghosh » Cleaning Up Garbage from Data

18 posts, Page 1 of 2

1 2 »»

Cleaning Up Garbage from Data

Rate Topic

Display Mode

Topic Options

Author

Message

Shubhajyoti Ghosh

Posted Sunday, February 21, 2010 10:04 PM

Grasshopper

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

Shubhajyoti Ghosh

Posted Monday, February 22, 2010 2:15 AM

Grasshopper

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

Satnam Singh

Posted Monday, February 22, 2010 3:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:14 AM
Points: 291, Visits: 1,066

Simple and nice to understand.

Post #870198

Shubhajyoti Ghosh

Posted Monday, February 22, 2010 4:02 AM

Grasshopper

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

sjkln

Posted Monday, February 22, 2010 6:19 AM

Forum 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

Shubhajyoti Ghosh

Posted Monday, February 22, 2010 6:29 AM

Grasshopper

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

78.gkumar

Posted Monday, February 22, 2010 8:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 21, 2012 8:27 AM
Points: 3, Visits: 10

Not, what I was expecting

Post #870393

Shubhajyoti Ghosh

Posted Monday, February 22, 2010 9:44 AM

Grasshopper

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

pbarbin

Posted Monday, February 22, 2010 9:44 AM

Grasshopper

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

Shubhajyoti Ghosh

Posted Monday, February 22, 2010 10:00 AM

Grasshopper

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. Smile

thanks for your contribution.

Regards,

Shubha
www.freegamingnow.com

Post #870526

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions