Tom V
August 4, 2016 10:56 am
What a coincidence, Aaron just created a series about this, you might link to it in the paragraph mentioning Kenneth and Remus.
Loading...
Reply
Kendra Little
August 4, 2016 12:15 pm
Oh, interesting! Do you have a link? We have a couple of Aarons in the community, and some of them even have a couple of blogs, so I’m guessing this managed to be in one I don’t have in my RSS reader somehow.
Loading...
Reply
Aaron Bertrand
August 4, 2016 6:27 pm
Well, this is awkward. 🙂
Loading...
Reply
Kendra Little
August 4, 2016 6:40 pm
Ooo, yay, thank you for the link! I looked for it on SQLBlog under your name and didn’t find it there. And then I was like, “maybe it was another Erin or Aaron?” My mental blog dictionary is well out of whack.
I just updated this post to link to the series. I get a variation of this question about this at least a few times a year, so having lots of resources for people is terrific.
I’ll also fix up my RSS feed!
Loading...
Reply
Russ Thomas
August 4, 2016 11:35 am
Good stuff, thanks Kendra!! Awhile back I wrote a post on an emergency step you can often take, by re-seeding the PK to the negative range of the int, essentially buying yourself another couple billion inserts. This only buys you time however, if you filled it once, you’ll do it again. I’ve gone back and updated the post with a link here for guidance on what you need to do with that time you bought and finally fix your underlying problem. https://sqljudo.wordpress.com/2014/09/16/help-pk-is-running-out-of-identity-values/
Loading...
Reply
Kendra Little
August 4, 2016 12:13 pm
Yep, that is an option– if you read the notes in this post or listen to the podcast, I spend a whole section explaining that exact option 😉
Loading...
Reply
Kyle
August 5, 2016 2:30 pm
Had recent really good success with this method:
Make couple of schemas, in the db with the big nasty table, “stage” and “old.” Have them both owned by dbo.
Make a blank copy of the table with its clustered index in the stage schema, don’t forget to change the column datatypes that are causing problems.
RDC into the server and fire up Data Tools. I’m assuming that you would be doing this from a high level/DBA role anyway, so you know how to behave in a terminal session on a prod server., 😉
Make a new project, create a data flow. In OLE DB Source, choose SQL Command, Build Query. Add the table and click (All Columns). Ok the dialog and click Build Query again, and then Ok. You now have all the columns from the table in the command text box with minimal typing. This gives better select performance than SELECT FROM TABLE or just choosing the table from the dropdown. I read it somewhere in a Microsoft SSIS page a while back but I don’t recall where. It does seem to help in any case.
Add a SQL Server Destination, connect the boxes and open the Destination to do the mappings. Ok your way out, save the project.
Run the project, you can even click inside the Data Flow to see the progress. If you haven’t messed with the MaxInsertCommitSize property, it will do all the commits at the end. You should try a few test runs to see what works best for your situation. In any case, even with full recovery, the log won’t get touched very much, but monitor to see how much growth you get.
When it’s done and stage.bigtable has been validated, move the production table to old.bigtable schema, then move stage.bigtable to dbo, (or whatever your prod schema is). The moves will be almost instant, and the table names and clustered index names won’t conflict.
Yes this requires a downtime, but it runs pretty fast depending on hardware.
I don’t think I left anything out, but it’s also Friday afternoon….
HTH
Loading...
Reply
manishkumar1980
September 20, 2016 9:46 pm
Hi Kendra,
Here you mentioned please…..
Multi-Terabyte example
I’ve worked with cases of Extra Extra Large databases where schema changes were staged outside of production using SAN snapshots and new databases.
If you’ve got an XXL change, SAN magic can be a big help.
Could you please elaborate this.Please it will be great.
Loading...
Reply
Kendra Little
September 21, 2016 9:41 am
Sure. This was a huge database (50TB+) which was being broken up into smaller (but still pretty huge) databases. A lot of the data was historical. Schema was being altered, partitioning was being introduced, lots of changes.
A SAN snapshot of the production data was presented to another server, so that data could be read from the snapshot and written /staged into new databases. It could also be used for testing. Periodically the production snapshot was refreshed to be able to update with new data, and eventually the staged databases were brought over and swapped into the production server during an outage.
There’s overhead reading from snapshots, of course, so care had to be taken– but this approach meant not having to use memory and CPU on the production server, not having to do locking in SQL Server when reading the production data, much greater ability to do testing, and a shorter outage when going live.
Loading...
Reply
Limiting Downtime for Schema Changes (Dear SQL DBA Episode 25) - by Kendra Little
December 15, 2016 8:23 am
[…] Note: I also talked about size of data operations in a previous Dear SQL DBA episode when I discussed another schema change: altering an INT column to a BIGINT. […]
Loading...
Reply
Finding All Gaps In An Identity Column | Shaun J Stuart
August 31, 2018 7:43 am
[…] Of course, the permament fix is to change the data type from integer to BIGINT, but, if your table is big enough to have this problem, that will also present its own set of challenges – this is a “size of data” operation, meaning SQL has to modify every single row in the table and the time it takes to do this is directly related to the size of the data in the table. The change will require the table to be locked, lots of transaction log will be generated, etc. See Kendra Little’s post about the issues you will face here. […]
Migrating comments from WordpressTom V August 4, 2016 10:56 am What a coincidence, Aaron just created a series about this, you might link to it in the paragraph mentioning Kenneth and Remus.
Loading... Reply Kendra Little August 4, 2016 12:15 pm Oh, interesting! Do you have a link? We have a couple of Aarons in the community, and some of them even have a couple of blogs, so I’m guessing this managed to be in one I don’t have in my RSS reader somehow.
Loading... Reply Aaron Bertrand August 4, 2016 6:27 pm Well, this is awkward. 🙂
http://sqlperformance.com/2016/08/sql-indexes/widening-identity-column-4
Loading... Reply Kendra Little August 4, 2016 6:40 pm Ooo, yay, thank you for the link! I looked for it on SQLBlog under your name and didn’t find it there. And then I was like, “maybe it was another Erin or Aaron?” My mental blog dictionary is well out of whack.
I just updated this post to link to the series. I get a variation of this question about this at least a few times a year, so having lots of resources for people is terrific.
I’ll also fix up my RSS feed!
Loading... Reply Russ Thomas August 4, 2016 11:35 am Good stuff, thanks Kendra!! Awhile back I wrote a post on an emergency step you can often take, by re-seeding the PK to the negative range of the int, essentially buying yourself another couple billion inserts. This only buys you time however, if you filled it once, you’ll do it again. I’ve gone back and updated the post with a link here for guidance on what you need to do with that time you bought and finally fix your underlying problem. https://sqljudo.wordpress.com/2014/09/16/help-pk-is-running-out-of-identity-values/
Loading... Reply Kendra Little August 4, 2016 12:13 pm Yep, that is an option– if you read the notes in this post or listen to the podcast, I spend a whole section explaining that exact option 😉
Loading... Reply Kyle August 5, 2016 2:30 pm Had recent really good success with this method:
Make couple of schemas, in the db with the big nasty table, “stage” and “old.” Have them both owned by dbo.
Make a blank copy of the table with its clustered index in the stage schema, don’t forget to change the column datatypes that are causing problems.
RDC into the server and fire up Data Tools. I’m assuming that you would be doing this from a high level/DBA role anyway, so you know how to behave in a terminal session on a prod server., 😉
Make a new project, create a data flow. In OLE DB Source, choose SQL Command, Build Query. Add the table and click (All Columns). Ok the dialog and click Build Query again, and then Ok. You now have all the columns from the table in the command text box with minimal typing. This gives better select performance than SELECT FROM TABLE or just choosing the table from the dropdown. I read it somewhere in a Microsoft SSIS page a while back but I don’t recall where. It does seem to help in any case.
Add a SQL Server Destination, connect the boxes and open the Destination to do the mappings. Ok your way out, save the project.
Run the project, you can even click inside the Data Flow to see the progress. If you haven’t messed with the MaxInsertCommitSize property, it will do all the commits at the end. You should try a few test runs to see what works best for your situation. In any case, even with full recovery, the log won’t get touched very much, but monitor to see how much growth you get.
When it’s done and stage.bigtable has been validated, move the production table to old.bigtable schema, then move stage.bigtable to dbo, (or whatever your prod schema is). The moves will be almost instant, and the table names and clustered index names won’t conflict.
Yes this requires a downtime, but it runs pretty fast depending on hardware.
I don’t think I left anything out, but it’s also Friday afternoon….
HTH
Loading...
Reply manishkumar1980 September 20, 2016 9:46 pm Hi Kendra,
Here you mentioned please…..
Multi-Terabyte example
I’ve worked with cases of Extra Extra Large databases where schema changes were staged outside of production using SAN snapshots and new databases.
If you’ve got an XXL change, SAN magic can be a big help.
Could you please elaborate this.Please it will be great.
Loading...
Reply Kendra Little September 21, 2016 9:41 am Sure. This was a huge database (50TB+) which was being broken up into smaller (but still pretty huge) databases. A lot of the data was historical. Schema was being altered, partitioning was being introduced, lots of changes.
A SAN snapshot of the production data was presented to another server, so that data could be read from the snapshot and written /staged into new databases. It could also be used for testing. Periodically the production snapshot was refreshed to be able to update with new data, and eventually the staged databases were brought over and swapped into the production server during an outage.
There’s overhead reading from snapshots, of course, so care had to be taken– but this approach meant not having to use memory and CPU on the production server, not having to do locking in SQL Server when reading the production data, much greater ability to do testing, and a shorter outage when going live.
Loading... Reply Limiting Downtime for Schema Changes (Dear SQL DBA Episode 25) - by Kendra Little December 15, 2016 8:23 am […] Note: I also talked about size of data operations in a previous Dear SQL DBA episode when I discussed another schema change: altering an INT column to a BIGINT. […]
Loading... Reply Finding All Gaps In An Identity Column | Shaun J Stuart August 31, 2018 7:43 am […] Of course, the permament fix is to change the data type from integer to BIGINT, but, if your table is big enough to have this problem, that will also present its own set of challenges – this is a “size of data” operation, meaning SQL has to modify every single row in the table and the time it takes to do this is directly related to the size of the data in the table. The change will require the table to be locked, lots of transaction log will be generated, etc. See Kendra Little’s post about the issues you will face here. […]