April 18, 2012 at 3:05 am
OK, another question.......
Have a database 200GB which has 55,000 user tables created as part of an application search/filter facility. These should have been cleared regularly, but unfortunately have not and now I need to take action. They appear to contribute up to half of the data space. The tables range from 0 rows to over 1 million.
The vendor had pointed me towards a SP which deletes from a particular table. This table has a trigger which then removes the offending user table with a 'DROP TABLE...' command.
If I was to run the SP and it went ahead and ran against all 55,000 would:
a) the system fall over?
b) tempdb fill up?
c) run for hours?....
Or is 'drop table' similar to say a truncate in the way that it can fairly quickly get rid of data without logging etc, so the system will be fairly untroubled?
I can't test this as only the production environment has the problem :crying:
thanks in advance!
April 19, 2012 at 5:48 am
as a followup question since there have been no replies :angry:
which of these two scenarios will be quickest:
1) run stored procedure which will select a bunch of rows to be deleted from a table, this will then cause a trigger to subsequently delete a bunch of corresponding tables (each row in the delete table has an ID, which is also the name of a user table which will be dropped).
2) disable the delete trigger. run a script (similar syntax to the procedure above) to delete rows from the table. run script to delete the user tables. re-enable the trigger
in the first scenario do the row+table delete/drops happen one at a time until all are complete? or do all rows get deleted, then all tables? (EDIT: think i have answered my own question since the trigger seems to utilise a cursor)
so i suppose my question is: is the trigger/cursor method significantly slower than using two seperate statements?
due to the fact i need to do this for so many tables and can't seem to estimate how long it will take to drop them all, I may need to break this down into chunks (would rather do it as one item though)
April 19, 2012 at 9:01 am
The trigger is a delete trigger? And it deletes the table? Not really understanding what you are trying to do here.
If you want to drop 55,000 tables, and some of them have millions of rows, it will monumentally faster to drop the table instead of deleting the data. Why would want to first delete the data and then drop the table? If the tables are no longer needed then drop them. Dropping tables will be pretty fast.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 9:38 am
hi sean,
sorry - its difficult to describe.
the vendor told me of a SP which should be running (but isn't) and it does two things:
1) delete from table_A where ID = 12345
2) drop table table_12345
I can either run the SP and let it do the work, or run the parts seperately which will allow me to have more control in terms of amont done
if i just run the SP it has no parameters and so it is going to be finding 55,000 rows to delete, and the delete trigger on that table is going to be removing 55,000 user tables. the 'delete from' is going to be fairly quick but I am struggling to grasp the impact of the 'drop table'. especially when the trigger is dropping them one table at a time (thats probably all DROP TABLE can do anyway :unsure:)
is a table drop instantaneous? i suspect it isn't since its essentially a logged operation
April 19, 2012 at 9:49 am
Yes explaining it make me shudder. It sounds like there is a table for each row in some other table? Sounds pretty scary.
Drop table is "minimally logged". That means it logs the page deletes which is a zillion times faster than logging the data. I would not say it is instantaneous but it is pretty fast.
I can't being to offer a recommendation without a mountain more information but it sounds like you have a pretty solid understanding of the system.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 10:29 am
use sp_msforeachtable, which loops through each table in the database and deletes data based on the condition. Also change the database to simple recovery mode before running the script.
April 19, 2012 at 10:38 am
Well you could do that I guess...but then you have done nothing but generate a cursor to loop through 55,000+ tables. :w00t:
And why change to simple recovery? That would defeat the whole purpose.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 11:32 am
As Deleting rows from tables in database would be logged onto transactional log if it's in full recovery mode, i've specified to change the recovery mode to simple. Once the data is deleted we could revert it back to full recovery mode.
April 19, 2012 at 11:38 am
If the point is to delete all the data why bother with changing recovery models at all, just truncate. Then is it minimally logged and just as fast as dropping the table. Of course dropping the table is what the OP wants to do so deleting the data is irrelevant.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 19, 2012 at 12:04 pm
oops! misunderstood the question, my bad. If deleting all the data from table and deleting the table is what required then truncate is the best option.
April 20, 2012 at 12:32 pm
rarara (4/19/2012)
1) delete from table_A where ID = 123452) drop table table_12345
Hopefully it would either have those two steps wrapped in a transaction or drop the table first, then delete the "tracking" record from the database. Without a transaction, if you deleted 12345, but the drop table failed, how would you know that table_12345 is a candidate for deletion?
April 20, 2012 at 12:41 pm
To me it seems like the easiest implementation of this would be to have a delete trigger on the "List" table. Then it can simply delete the "child" table. Then of course I would never design such an ugly thing (not being critical of the OP since this is a vendor create mess).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2012 at 8:04 am
ACinKC (4/20/2012)
rarara (4/19/2012)
1) delete from table_A where ID = 123452) drop table table_12345
Hopefully it would either have those two steps wrapped in a transaction or drop the table first, then delete the "tracking" record from the database. Without a transaction, if you deleted 12345, but the drop table failed, how would you know that table_12345 is a candidate for deletion?
isn't that how triggers work - they are automatically part of a transaction? so if my delete fires a trigger which then attempts to 'drop table', if this doesn't complete both the table drop and the deletion are rolled back? what about in my case where there are multiple rows being deleted, and the trigger then uses a cursor to the run several 'drop table' commands - will everything roll back if any problem occurs?
this is quite confusing!
April 23, 2012 at 8:07 am
rarara (4/23/2012)
ACinKC (4/20/2012)
rarara (4/19/2012)
1) delete from table_A where ID = 123452) drop table table_12345
Hopefully it would either have those two steps wrapped in a transaction or drop the table first, then delete the "tracking" record from the database. Without a transaction, if you deleted 12345, but the drop table failed, how would you know that table_12345 is a candidate for deletion?
isn't that how triggers work - they are automatically part of a transaction? so if my delete fires a trigger which then attempts to 'drop table', if this doesn't complete both the table drop and the deletion are rolled back? what about in my case where there are multiple rows being deleted, and the trigger then uses a cursor to the run several 'drop table' commands - will everything roll back if any problem occurs?
this is quite confusing!
Yes there is an implicit transaction happening during the delete (which includes the trigger). So yes if something goes wrong during the delete and ensuing cursor in the trigger (JUST SHOOT ME THIS IS SUCH A BAD IDEA!!!!) then the whole thing will be rolled back.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 10:33 am
rarara (4/19/2012)
hi sean,sorry - its difficult to describe.
the vendor told me of a SP which should be running (but isn't) and it does two things:
1) delete from table_A where ID = 12345
2) drop table table_12345
I can either run the SP and let it do the work, or run the parts seperately which will allow me to have more control in terms of amont done
if i just run the SP it has no parameters and so it is going to be finding 55,000 rows to delete, and the delete trigger on that table is going to be removing 55,000 user tables. the 'delete from' is going to be fairly quick but I am struggling to grasp the impact of the 'drop table'. especially when the trigger is dropping them one table at a time (thats probably all DROP TABLE can do anyway :unsure:)
is a table drop instantaneous? i suspect it isn't since its essentially a logged operation
Once you have escaped from this nightmare scenario, my long term advice is that you drop the database and drop vendor.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply