January 12, 2012 at 10:17 am
Hi, we have a piece of work coming up to drop an image type column from a 7.5million row, 13GB table.
I don't see how a drop column can be batched up and I have a feeling this is going to hit the transaction log pretty hard space wise. The database is in full recovery mode (its also being mirrored). As it stands I have 20GB available for the transaction log.
Any input, experience, opinions gratefully received.
cheers
george
---------------------------------------------------------------------
January 12, 2012 at 10:18 am
for something similar, we've created the parallel table without the image column, migrated the data, then renamed the old vs new table, and after we were sure everything was beautiful, we dropped the original.
that avoids the whole schema lock stuff to just the time things are being renamed.
Lowell
January 12, 2012 at 10:44 am
thanks lowell, that could be batched up and would also have the advantage for us of defragmenting the table (no primary key, no clustered index). I could also add an identity column on the way to cluster on.
........unfortunately.....as I understand it this process will be driven from the application, turning off the option to store the images going forward and dropping those already collected, so the question still stands out of curiosity if nothing else - will this blow my transaction log.
the application will be down while this is done so not concerned about locking.
---------------------------------------------------------------------
January 12, 2012 at 10:50 am
george sibbald (1/12/2012)
thanks lowell, that could be batched up ....
For a 7.5 million rows I do not see the need of doing it in batches... you just have to insert 7.5 million rows which should be done fairly quckly.
george sibbald (1/12/2012)
so the question still stands out of curiosity if nothing else - will this blow my transaction log.
If your t-log has space for a 7.5 million rows insert query you should be fine.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 12, 2012 at 10:50 am
Lowell (1/12/2012)
for something similar, we've created the parallel table without the image column, migrated the data, then renamed the old vs new table, and after we were sure everything was beautiful, we dropped the original.that avoids the whole schema lock stuff to just the time things are being renamed.
This is exactly how I would do it.
Done from the application, well, it will blow your transaction log!
-- Gianluca Sartori
January 12, 2012 at 10:53 am
george sibbald (1/12/2012)
I don't see how a drop column can be batched
Quite true, but you could batch UPDATEs to set the column to NULL. Once every row is set to null, you could drop the column quite easily and fast.
-- Gianluca Sartori
January 12, 2012 at 11:02 am
If your t-log has space for a 7.5 million rows insert query you should be fine.
well yes, obviously, but how much space is that? Very much an 'it depends' question.
If we go that way I will test it.
---------------------------------------------------------------------
January 12, 2012 at 11:03 am
george sibbald (1/12/2012)
If your t-log has space for a 7.5 million rows insert query you should be fine.
well yes, obviously, but how much space is that? Very much an 'it depends' question.
If we go that way I will test it.
How much volume your database processes in a day?
How often you backup your t-logs?
How big are your t-log backups?
We do not know - you should know 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 12, 2012 at 11:04 am
Gianluca Sartori (1/12/2012)
Lowell (1/12/2012)
for something similar, we've created the parallel table without the image column, migrated the data, then renamed the old vs new table, and after we were sure everything was beautiful, we dropped the original.that avoids the whole schema lock stuff to just the time things are being renamed.
This is exactly how I would do it.
Done from the application, well, it will blow your transaction log!
thanks, wanted confirmation of my fears before allowing a drop to be run if thats the only option we get.
---------------------------------------------------------------------
January 12, 2012 at 11:10 am
george sibbald (1/12/2012)
Gianluca Sartori (1/12/2012)
Lowell (1/12/2012)
for something similar, we've created the parallel table without the image column, migrated the data, then renamed the old vs new table, and after we were sure everything was beautiful, we dropped the original.that avoids the whole schema lock stuff to just the time things are being renamed.
This is exactly how I would do it.
Done from the application, well, it will blow your transaction log!
thanks, wanted confirmation of my fears before allowing a drop to be run if thats the only option we get.
mmmhh... I thought the plan was to go for a reverse-drop and not for a drop of the column.
As already explained by other poster...
1- Wait until app is down and do-not-collect-images flag is up.
2- Be positive you have a good backup.
3- Create a new table that lacks the to-be-dropped column.
4- Load the new table from the old one - note that to-be-dropped column in not going to be moved.
5- Rename original table as *_old, new table to the expected name.
6- Build indexes, PKs, Triggers
7- Open the database for quality assurance.
8- Keep the old table for a week or two until it is clear nobody wants it back, get a signed approval to get rid of it, drop it.
9- Accept the gratitude of your boss and the user community - you are a hero now!
10- Wait for our consulting bill which is on the mail and send the check 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 12, 2012 at 11:13 am
PaulB-TheOneAndOnly (1/12/2012)
george sibbald (1/12/2012)
If your t-log has space for a 7.5 million rows insert query you should be fine.
well yes, obviously, but how much space is that? Very much an 'it depends' question.
If we go that way I will test it.
How much volume your database processes in a day?
How often you backup your t-logs?
How big are your t-log backups?
We do not know - you should know 🙂
we are getting a bit off the point. I have nothing to compare to a 'drop image column' which was the original question, and this is an OLTP database, not large batch processes, and if its a single transaction doesn't matter how often I backup the log or how big they normally are.
---------------------------------------------------------------------
January 12, 2012 at 11:19 am
george sibbald (1/12/2012)
PaulB-TheOneAndOnly (1/12/2012)
george sibbald (1/12/2012)
If your t-log has space for a 7.5 million rows insert query you should be fine.
well yes, obviously, but how much space is that? Very much an 'it depends' question.
If we go that way I will test it.
How much volume your database processes in a day?
How often you backup your t-logs?
How big are your t-log backups?
We do not know - you should know 🙂
we are getting a bit off the point. I have nothing to compare to a 'drop image column' which was the original question, and this is an OLTP database, not large batch processes, and if its a single transaction doesn't matter how often I backup the log or how big they normally are.
You are not dropping a column, you are going to insert 7.5 million rows which have to be much less than the 13 Gig of space you have in the current table that includes the images. No drop, Insert! Read again our postings. 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 12, 2012 at 11:23 am
Gianluca Sartori (1/12/2012)
george sibbald (1/12/2012)
I don't see how a drop column can be batchedQuite true, but you could batch UPDATEs to set the column to NULL. Once every row is set to null, you could drop the column quite easily and fast.
nice. I need to find out how the app does it (if anyone even knows) and prepare for the worst case scenario (which is 20Gb of log wont be enough) as it sounds like a straight drop column does produce a lot of log
---------------------------------------------------------------------
January 12, 2012 at 11:38 am
george sibbald (1/12/2012)
@Paul - please read the first 3 posts - this might HAVE to be a drop column and I need to be prepared for that.I appreciate the intention - but please I'm not a newbie.
No worries - I'm sorry for the inconvenience.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply