December 4, 2013 at 9:31 am
Hi all
I have been tasked with a requirement to drop around 500,000 tables from a data warehouse. The application was supposed to have a front end tool to allow users to drop these but it either doesn't work (or they never did it correctly) hence the sudden need for this.
When I tried running a simple script wrapped in a txn (about 100,000 tables) with DROP TABLE <tablename> on each line it ran for 6 hours and got nowhere!
Checked RAM and CPU usage at the time, looked OK. No locks or blocking but I did notice lots of PAGEIOLATCH_EX which suggests the I/O was struggling.
Moving on I need a way to maybe do them in smaller chunks via a SQL Agent Job run on a schedule and the table names taken from either a file (or a table in a db).
Toyed with the idea of a READ_ONLY cursor but not all the tables of a similar naming convention require to be dropped (these are created by the app and have fairly similar long names) and I know cursors are not the best method to use ideally.
So if anyone has done similar or has some suggestions I'm all ears! Will be tested on a non-PROD Server first with a subset of the tables imported into an empty database so happy to tweak/experiment with the code and number of table drops per run.
The PROD Server is 2008 R2 with 64GB RAM (56GB for SQL) and these tables take up around .5TB or more (DW db is 1.5TB). No PK/FK relationships or Replication.
Thanks
December 4, 2013 at 11:36 am
500,00 tables? Wow :crazy:
Why wrap the script in a transaction? Does it have to be an all or nothing type thing? Also, there is no way to grammatically return the list of tables to be deleted?
When I have log tables that I need to delete large amounts of records I use a table-based approach where I list the table name and the number rows to delete at a time. Then I have a stored procedure that looks in the table to see which table is next and how many rows to delete. This keeps the transaction short.
Also what is your recovery model for your database? Can you set it to simple during this activity? If not, then you may want to decrease the amount of time between tlog backups. Good luck!
December 4, 2013 at 12:20 pm
^^^
Thanks for the quick response.
The first run at it was wrapped in a txn just as a precaution in case of need to rollback but it never came to that as it didn't work 🙁
I'm inclined along your lines, a new table containing all the names of the tables to be dropped then a a sproc that loops through x thousand at a time (can't loop through sysobjects as the names are not grammatically different enough between those that need to be dropped and those to retain).
Would you mind sharing the code for the sproc you use? Probably could be adapted to drop tables rather than delete rows from tables.
DB is in Fully Logged Mode, the drive for TLog has oodles of free space and Auto Grow is enabled, loath to switch modes unless I have to.
Any other input or suggestions most welcome!
Thanks
December 4, 2013 at 1:07 pm
When you test this procedure please note the size of your transaction log and then pre-grow the tlog in production. You don't want to have your tlog auto-grow in production since it can't benefit from instant file initialization and will slow down the process along with other transactions. Also, if you rely on auto-grow you may start having VLF issues too.
My code isn't very special it simply retrieves the name of the next table and executes dynamic sql to perform the delete. You could do the same with your drop statement
December 5, 2013 at 2:42 am
^^^
Oh yeah, had already provisioned growing the TLOG to a reasonable size before running it in PROD, don't want the thing slowed to a crawl for slow file growth increments.
Thanks again.
January 1, 2014 at 2:16 pm
I realize this is an older post that might have already been accomplished but I wanted to note (in case someone else reads this) that only the page deallocations are logged for either DROP or TRUNCATE. You just aren't going to have to pregrow the log file that much, if at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply