July 18, 2010 at 12:46 pm
Is there a way i can add secondary data file to an ONLINE database and move some of the table to the secondary file without taking the database offline.
July 18, 2010 at 2:31 pm
After you create the file, rebuild the clustered index for the table. If you want the entire table to be moved to the new file, you would need to create that new file in a new filegroup. Then you move the clustered index to the new filegroup.
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
July 18, 2010 at 3:38 pm
does it mean all of these steps can be done without taking the database offline, could you please point me to any scripts which does this.
July 18, 2010 at 4:42 pm
I would not recommend doing this during normal production hours. Depending on the size of the table and the usage - you could cause some serious slowdowns in the applications or user access.
The script to run will depend on your intentions for the new file. Will this be a new file group or in an existing one?
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
July 18, 2010 at 6:18 pm
It's worth pointing out that unless you have Enterprise Edition, and can rebuild your indexes online, then you will have a period of downtime when each clustered index is unavailable as it is built. Otherwise, Jason's advice is sound - make sure you do this in the off hours, and I'd also recommend paying careful attention to the size of the transaction log as you rebuild.
July 18, 2010 at 6:40 pm
I second Jim's note. While each table moves, it will be locked by the server and unavailable unless you can rebuild online.
The nice thing is you can script this and run tables during less busy times. It doesn't have to be all at once.
July 20, 2010 at 4:27 am
my table does not have CI but it has NON-CI, so as to move the table I am creating a clustered index on my table and i will delete later but it is taking too long ti createa Clustered index, what happens if i stop it ?
- does the table roll back to its original drive?
- Am i going to lose the table by any chance?
- How can i move fwd at this point ?
July 20, 2010 at 4:57 am
When you create an index ONLINE, you are creating a brand new structure, and the old structure is not affected until the new one is ready. Therefore, if you were to cancel the index build, it should rollback almost immediately - all new pages allocated will be released, much like a truncation. You will be left with the original heap.
Remember also, that any non-clustered indexes you have will need to be rebuilt at the same time as you're creating the clustered index. If this takes too long, you might consider disabling any non-critical indexes prior to adding the clustered index, and rebuilding the non-clustered indexes later.
Edit: I just tested the index creation on a heap, and cancelling it. When you create an index ONLINE, you can roll back easily - after 8 seconds of building, the rollback took another 2 seconds. When creating the index OFFLINE, rollback after 8 seconds took another 8 seconds.
July 20, 2010 at 8:42 am
Another thing to take note of - when you create the CI and then drop it you must rebuild any other indexes on your table after the drop. It is best to leave the CI in place.
Paul Randal explains this whole process in his myth a day series.
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
July 20, 2010 at 1:21 pm
actualy i have a question about partitions, if have database with parition for every month, say if i load data for july 2010 in dev environment,can i just backup this file from the database and restore on to prodcution database?
Also I will be defning all partitions files in before in both production and dev.
July 20, 2010 at 1:23 pm
That's a good question. I think the partition would have to already exist in the production Db to do the restore, and I'm not sure how timing/integrity fits in here.
You certainly could easily make an SSIS package to move a partition of data.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply