February 21, 2010 at 10:04 pm
Comments posted to this topic are about the item Cleaning Up Garbage from Data
February 22, 2010 at 2:15 am
Cleaning Up Garbage from Data is my first article to SQLServerCenter. Waiting for valuable comment.
Regards,
Shubha
February 22, 2010 at 3:42 am
Simple and nice to understand.
February 22, 2010 at 4:02 am
February 22, 2010 at 6:19 am
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.
February 22, 2010 at 6:29 am
February 22, 2010 at 8:30 am
Not, what I was expecting
February 22, 2010 at 9:44 am
February 22, 2010 at 9:44 am
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
February 22, 2010 at 10:00 am
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
February 22, 2010 at 11:56 am
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
February 22, 2010 at 10:18 pm
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
February 22, 2010 at 11:35 pm
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
February 23, 2010 at 12:04 am
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
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
February 23, 2010 at 12:22 am
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply