Cleaning Up Garbage from Data

  • Comments posted to this topic are about the item Cleaning Up Garbage from Data

  • Cleaning Up Garbage from Data is my first article to SQLServerCenter. Waiting for valuable comment.

    Regards,

    Shubha

    www.freegamingnow.com

  • Simple and nice to understand.

  • Thanks singhsatnam84

    Regards,

    Shubha

    www.freegamingnow.com

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

  • Thanks sjkln you are right. This is a very common scenario.

    Regards,

    Shubha

    www.freegamingnow.com

  • Not, what I was expecting

  • What you expecting ?

    Regards,

    Shubha

    www.freegamingnow.com

  • 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

  • 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

  • Thanks for the article. I tend to use the method similar to what Paul described.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi all,

    Good day.

    Shubhajyoti, you need to improve your SQL skills, not waste time to create a sp to delete tables.

    if you want to create some tmp tables, you can name them like a#t_customer, a#t_sales...

    then when you need to delete them, they sit at the top of table list. then just delete them.

    You marked them with special name at first place.

    What you are doing is not a good habit.

    at DBA's view, this is not even acceptable.

    have fun.

    Ben

  • Hi Ben,

    Your recommendation is appreciated, But not always need to delete Temp table explicitly. If someone working as DBA, then he or she have oder option to handle DB as per his / her expertise. But in generally

    developer those are not typically working as DBA if they need to solve this kind of problem. they can go through this type of approach may be according to experts this are not an optimize solution. As I already mentioned Time, Situation, proper analysis point of view is play as a major factor to solve a particular problem. But in our world may be don't have enough time analysis of our problem lots of other factor also.

    So my approach is just a blinking of light to overcome the dark situation. Rather than search out for a halogen. 🙂 😉

    Regards,

    shubha

    www.freegamingnow.com

  • Shubhajyoti Ghosh (2/22/2010)


    Hi Ben,

    Your recommendation is appreciated, But not always need to delete Temp table explicitly. If someone working as DBA, then he or she have oder option to handle DB as per his / her expertise. But in generally

    developer those are not typically working as DBA if they need to solve this kind of problem. they can go through this type of approach may be according to experts this are not an optimize solution. As I already mentioned Time, Situation, proper analysis point of view is play as a major factor to solve a particular problem. But in our world may be don't have enough time analysis of our problem lots of other factor also.

    So my approach is just a blinking of light to overcome the dark situation. Rather than search out for a halogen. 🙂 😉

    Regards,

    shubha

    www.freegamingnow.com

    Hi shubha,

    Good day.

    Yeah, time is everything. There are more inside a blinking, not only light.

    :-):-):-)

    Our world only exists inside a blinking, Outside this blinking only our illusions.

    it likes film, 24 pics per second. but only pics, not movements.

    they are tables, but if you define them as Garbage, you can delete them in whatever name.

    Yes, you are right, sometime we need to delete some tables.

    the differences are how many clicks: 1,10 or 500...

    :-D:-D:-D

    have fun

    Ben

  • Nice counter attack :-D:-D:-D

    Regards,

    Shubha

    www.freegamingnow.com

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply