|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Consolidate datafiles to one drive
Hello,
My database has 10 datafiles spread across 10 drives. We designed it a year back anticipating heavy traffic but it didn't turn out that way. Now, I need to give all those extra SAN space back and so need to put 9 datafiles to a single drive. All these 9 files are 1GB each with free space around 1-4%. What is the best way to accomplish this in minimal downtime? TIA, AbbA It depends on how you define "downtime".
The operation can be very quick by detaching the database, copy the files to desired destination and then attach the database (specifying the new location for each file of course). Database is totally offline during operation but operation is quick. And you still have 10 files, but all are on same (desired) drive now. Another option is to expand the file you want to keep and shrink each other file using the EMPTYFILE option. This will "push the data over to the other files. For each file, you after the shrink then delete the file (using ALTER DATABASE, of course). BE sure to set appropriate max size for each file as you do this. You don't want when you do the first file to have SQL Server push pages to files that will later be shrinked in turn. This process will be considerably slower than the detach process, but data isn't totally offline during your work. This also require a bit more knowledge of SQL Server, so you do the right step in the right way and in the right order. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Abba" <SQL_H***@aaa.com> wrote in message news:ObcjCTFVJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > My database has 10 datafiles spread across 10 drives. We designed it > a year back anticipating heavy traffic but it didn't turn out that > way. Now, I need to give all those extra SAN space back and so need > to put 9 datafiles to a single drive. > > All these 9 files are 1GB each with free space around 1-4%. > > What is the best way to accomplish this in minimal downtime? > > TIA, > AbbA > Thanks Tibor.
I would be taking the second approach as I do not want the 9 files and instead would like to have only 1 that contains the data from the 9. I need a clarification: >> BE sure to set appropriate max size for each file as you do this. What do you mean by this?Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:eVuVwxFVJHA.4900@TK2MSFTNGP05.phx.gbl... > It depends on how you define "downtime". > > The operation can be very quick by detaching the database, copy the files > to desired destination and then attach the database (specifying the new > location for each file of course). Database is totally offline during > operation but operation is quick. And you still have 10 files, but all are > on same (desired) drive now. > > Another option is to expand the file you want to keep and shrink each > other file using the EMPTYFILE option. This will "push the data over to > the other files. For each file, you after the shrink then delete the file > (using ALTER DATABASE, of course). BE sure to set appropriate max size for > each file as you do this. You don't want when you do the first file to > have SQL Server push pages to files that will later be shrinked in turn. > This process will be considerably slower than the detach process, but data > isn't totally offline during your work. This also require a bit more > knowledge of SQL Server, so you do the right step in the right way and in > the right order. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Abba" <SQL_H***@aaa.com> wrote in message > news:ObcjCTFVJHA.5032@TK2MSFTNGP05.phx.gbl... >> Hello, >> >> My database has 10 datafiles spread across 10 drives. We designed it a >> year back anticipating heavy traffic but it didn't turn out that way. >> Now, I need to give all those extra SAN space back and so need to put 9 >> datafiles to a single drive. >> >> All these 9 files are 1GB each with free space around 1-4%. >> >> What is the best way to accomplish this in minimal downtime? >> >> TIA, >> AbbA >> > >>> BE sure to set appropriate max size for each file as you do this. I'm sorry to say this, but the fact that you ask indicates me that you > What do you mean by this? might not be ready to undertake that second approach. As I said, you need to do the right things, in the right way and in the right order. If you are confident with this than the second alternative can be the best choice - if not a consultant or the first option can be better. Anyhow, you can specify a max file size for a database file. This is what I'm referring to. This is done using the ALTER DATABASE command (syntax details in Books Online). -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Abba" <SQL_H***@aaa.com> wrote in message news:OxvNcKGVJHA.5424@TK2MSFTNGP04.phx.gbl... > Thanks Tibor. > > I would be taking the second approach as I do not want the 9 files > and instead would like to have only 1 that contains the data from > the 9. I need a clarification: > >>> BE sure to set appropriate max size for each file as you do this. > What do you mean by this? > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> > wrote in message news:eVuVwxFVJHA.4900@TK2MSFTNGP05.phx.gbl... >> It depends on how you define "downtime". >> >> The operation can be very quick by detaching the database, copy the >> files to desired destination and then attach the database >> (specifying the new location for each file of course). Database is >> totally offline during operation but operation is quick. And you >> still have 10 files, but all are on same (desired) drive now. >> >> Another option is to expand the file you want to keep and shrink >> each other file using the EMPTYFILE option. This will "push the >> data over to the other files. For each file, you after the shrink >> then delete the file (using ALTER DATABASE, of course). BE sure to >> set appropriate max size for each file as you do this. You don't >> want when you do the first file to have SQL Server push pages to >> files that will later be shrinked in turn. This process will be >> considerably slower than the detach process, but data isn't totally >> offline during your work. This also require a bit more knowledge of >> SQL Server, so you do the right step in the right way and in the >> right order. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Abba" <SQL_H***@aaa.com> wrote in message >> news:ObcjCTFVJHA.5032@TK2MSFTNGP05.phx.gbl... >>> Hello, >>> >>> My database has 10 datafiles spread across 10 drives. We designed >>> it a year back anticipating heavy traffic but it didn't turn out >>> that way. Now, I need to give all those extra SAN space back and >>> so need to put 9 datafiles to a single drive. >>> >>> All these 9 files are 1GB each with free space around 1-4%. >>> >>> What is the best way to accomplish this in minimal downtime? >>> >>> TIA, >>> AbbA >>> >> > > To add a bit to Tibor's explanation here. You have 10 data files, and SQL
Server does what is called proportional-fill by adding data to the files round robin, but also weighting the allocations towards files that are larger and with more free space. When you do a SHRINKFILE with EMPTYFILE, you have to configure the remaining files appropriately so that you don't empty the 10th file and spread the data evenly across the remaining 9. Then empty the 9th file and spread the data evenly across the remaining 8. If you do this, you are moving data into file 9 to move it back out, and in the process you will incur filegrowth on files you plan to empty anyway. You have to know what you are doing, and you have to think this out. If you grow the Primary or #1 file to be larger than necessary to hold all of the data the proportional-fill will favor that file, but it will still allocate data and grow the other files as a part of the round robin effect. When you set the Max file size, you prevent it from growing, and when it is full it is removed from the round robin effect and proportional-fill. Again, you have to know what you are doing, and plan this out. Test this on a copy of the database somewhere else if you plan to do this yourself and it is your first time, and monitor your files along the way to ensure that you aren't double/tripple/quadruple ( I can't go any higher than that) moving the data around the files. Show quoteHide quote "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in message news:OGW29PGVJHA.1200@TK2MSFTNGP02.phx.gbl... >>>> BE sure to set appropriate max size for each file as you do this. >> What do you mean by this? > > I'm sorry to say this, but the fact that you ask indicates me that you > might not be ready to undertake that second approach. As I said, you need > to do the right things, in the right way and in the right order. If you > are confident with this than the second alternative can be the best > choice - if not a consultant or the first option can be better. Anyhow, > you can specify a max file size for a database file. This is what I'm > referring to. This is done using the ALTER DATABASE command (syntax > details in Books Online). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Abba" <SQL_H***@aaa.com> wrote in message > news:OxvNcKGVJHA.5424@TK2MSFTNGP04.phx.gbl... >> Thanks Tibor. >> >> I would be taking the second approach as I do not want the 9 files and >> instead would like to have only 1 that contains the data from the 9. I >> need a clarification: >> >>>> BE sure to set appropriate max size for each file as you do this. >> What do you mean by this? >> >> >> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >> in message news:eVuVwxFVJHA.4900@TK2MSFTNGP05.phx.gbl... >>> It depends on how you define "downtime". >>> >>> The operation can be very quick by detaching the database, copy the >>> files to desired destination and then attach the database (specifying >>> the new location for each file of course). Database is totally offline >>> during operation but operation is quick. And you still have 10 files, >>> but all are on same (desired) drive now. >>> >>> Another option is to expand the file you want to keep and shrink each >>> other file using the EMPTYFILE option. This will "push the data over to >>> the other files. For each file, you after the shrink then delete the >>> file (using ALTER DATABASE, of course). BE sure to set appropriate max >>> size for each file as you do this. You don't want when you do the first >>> file to have SQL Server push pages to files that will later be shrinked >>> in turn. This process will be considerably slower than the detach >>> process, but data isn't totally offline during your work. This also >>> require a bit more knowledge of SQL Server, so you do the right step in >>> the right way and in the right order. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> "Abba" <SQL_H***@aaa.com> wrote in message >>> news:ObcjCTFVJHA.5032@TK2MSFTNGP05.phx.gbl... >>>> Hello, >>>> >>>> My database has 10 datafiles spread across 10 drives. We designed it a >>>> year back anticipating heavy traffic but it didn't turn out that way. >>>> Now, I need to give all those extra SAN space back and so need to put 9 >>>> datafiles to a single drive. >>>> >>>> All these 9 files are 1GB each with free space around 1-4%. >>>> >>>> What is the best way to accomplish this in minimal downtime? >>>> >>>> TIA, >>>> AbbA >>>> >>> >> >> > Got it. Thanks Jonathan.
Show quoteHide quote "Jonathan Kehayias" <jmkehayias.nospam@nospam.google.com> wrote in message news:OE945rOVJHA.4456@TK2MSFTNGP06.phx.gbl... > To add a bit to Tibor's explanation here. You have 10 data files, and SQL > Server does what is called proportional-fill by adding data to the files > round robin, but also weighting the allocations towards files that are > larger and with more free space. When you do a SHRINKFILE with EMPTYFILE, > you have to configure the remaining files appropriately so that you don't > empty the 10th file and spread the data evenly across the remaining 9. > Then empty the 9th file and spread the data evenly across the remaining 8. > If you do this, you are moving data into file 9 to move it back out, and > in the process you will incur filegrowth on files you plan to empty > anyway. You have to know what you are doing, and you have to think this > out. > > If you grow the Primary or #1 file to be larger than necessary to hold all > of the data the proportional-fill will favor that file, but it will still > allocate data and grow the other files as a part of the round robin > effect. When you set the Max file size, you prevent it from growing, and > when it is full it is removed from the round robin effect and > proportional-fill. Again, you have to know what you are doing, and plan > this out. Test this on a copy of the database somewhere else if you plan > to do this yourself and it is your first time, and monitor your files > along the way to ensure that you aren't double/tripple/quadruple ( I can't > go any higher than that) moving the data around the files. > > > -- > Jonathan Kehayias > SQL Server MVP > http://jmkehayias.blogspot.com > http://www.sqlclr.net > > > "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote > in message news:OGW29PGVJHA.1200@TK2MSFTNGP02.phx.gbl... >>>>> BE sure to set appropriate max size for each file as you do this. >>> What do you mean by this? >> >> I'm sorry to say this, but the fact that you ask indicates me that you >> might not be ready to undertake that second approach. As I said, you need >> to do the right things, in the right way and in the right order. If you >> are confident with this than the second alternative can be the best >> choice - if not a consultant or the first option can be better. Anyhow, >> you can specify a max file size for a database file. This is what I'm >> referring to. This is done using the ALTER DATABASE command (syntax >> details in Books Online). >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> "Abba" <SQL_H***@aaa.com> wrote in message >> news:OxvNcKGVJHA.5424@TK2MSFTNGP04.phx.gbl... >>> Thanks Tibor. >>> >>> I would be taking the second approach as I do not want the 9 files and >>> instead would like to have only 1 that contains the data from the 9. I >>> need a clarification: >>> >>>>> BE sure to set appropriate max size for each file as you do this. >>> What do you mean by this? >>> >>> >>> "Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote >>> in message news:eVuVwxFVJHA.4900@TK2MSFTNGP05.phx.gbl... >>>> It depends on how you define "downtime". >>>> >>>> The operation can be very quick by detaching the database, copy the >>>> files to desired destination and then attach the database (specifying >>>> the new location for each file of course). Database is totally offline >>>> during operation but operation is quick. And you still have 10 files, >>>> but all are on same (desired) drive now. >>>> >>>> Another option is to expand the file you want to keep and shrink each >>>> other file using the EMPTYFILE option. This will "push the data over to >>>> the other files. For each file, you after the shrink then delete the >>>> file (using ALTER DATABASE, of course). BE sure to set appropriate max >>>> size for each file as you do this. You don't want when you do the first >>>> file to have SQL Server push pages to files that will later be shrinked >>>> in turn. This process will be considerably slower than the detach >>>> process, but data isn't totally offline during your work. This also >>>> require a bit more knowledge of SQL Server, so you do the right step in >>>> the right way and in the right order. >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://sqlblog.com/blogs/tibor_karaszi >>>> >>>> >>>> "Abba" <SQL_H***@aaa.com> wrote in message >>>> news:ObcjCTFVJHA.5032@TK2MSFTNGP05.phx.gbl... >>>>> Hello, >>>>> >>>>> My database has 10 datafiles spread across 10 drives. We designed it a >>>>> year back anticipating heavy traffic but it didn't turn out that way. >>>>> Now, I need to give all those extra SAN space back and so need to put >>>>> 9 datafiles to a single drive. >>>>> >>>>> All these 9 files are 1GB each with free space around 1-4%. >>>>> >>>>> What is the best way to accomplish this in minimal downtime? >>>>> >>>>> TIA, >>>>> AbbA >>>>> >>>> >>> >>> >> > >  
Other interesting topics
Re-attaching database
Migrating SQL 2000 to SQL 2005, any risk I change db collation too? Alter Index On Database Rebuild (SQL Server 2005) Adding memory has degraded performance SQL GROUP BY CLAUSE SQL 2k5-32bit >SQL 2k5-64bit SQL Server 2005 Cannot refer column Schema Across Database Taking database offline taking long time Bug or Bad Practice on my part? Re: Odd Identity Behavior in Enterprise Manager |
|||||||||||||||||||||||
