January 22, 2018 at 10:59 am
Bobby Russell - Monday, January 22, 2018 10:16 AMItem 4 was my choice, which IS correct whether you think so or not, and the best way to perform this operation. Why would you consider this incorrect? It is the most correct of your options available.
If you can tolerate the extreme amount of data movement, you still end up with all the problems that DBCC SHRINKFILE imposes on a database... massive fragmentation on many indexes caused by the "index page inversion" (for lack of a better term) the DBCC SHRINKFILE causes. If you have a small database, that may not cause you an issue but it'll be a huge problem for larger databases because, in order to get performance back, you'll need to rebuild many indexes and some of them could be huge. That'll cause wanton growth of the MDF/NDF file and cause your log file to explode in size. Even the article that was previously cited said that one of the final steps will be to do index maintenance on the file you just moved.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2018 at 11:11 am
Jeff Moden - Monday, January 22, 2018 10:59 AMBobby Russell - Monday, January 22, 2018 10:16 AMItem 4 was my choice, which IS correct whether you think so or not, and the best way to perform this operation. Why would you consider this incorrect? It is the most correct of your options available.If you can tolerate the extreme amount of data movement, you still end up with all the problems that DBCC SHRINKFILE imposes on a database... massive fragmentation on many indexes caused by the "index page inversion" (for lack of a better term) the DBCC SHRINKFILE causes. If you have a small database, that may not cause you an issue but it'll be a huge problem for larger databases because, in order to get performance back, you'll need to rebuild many indexes and some of them could be huge. That'll cause wanton growth of the MDF/NDF file and cause your log file to explode in size. Even the article that was previously cited said that one of the final steps will be to do index maintenance on the file you just moved.
OK, my data stays online, my goal is achieved AND with an online index rebuild we're golden. Again, not wrong....
January 22, 2018 at 2:40 pm
You're answering the question according to your requirements, not the question's. The question asked about renaming the file, not adding a new one.
January 22, 2018 at 6:10 pm
Bobby Russell - Monday, January 22, 2018 11:11 AMJeff Moden - Monday, January 22, 2018 10:59 AMBobby Russell - Monday, January 22, 2018 10:16 AMItem 4 was my choice, which IS correct whether you think so or not, and the best way to perform this operation. Why would you consider this incorrect? It is the most correct of your options available.If you can tolerate the extreme amount of data movement, you still end up with all the problems that DBCC SHRINKFILE imposes on a database... massive fragmentation on many indexes caused by the "index page inversion" (for lack of a better term) the DBCC SHRINKFILE causes. If you have a small database, that may not cause you an issue but it'll be a huge problem for larger databases because, in order to get performance back, you'll need to rebuild many indexes and some of them could be huge. That'll cause wanton growth of the MDF/NDF file and cause your log file to explode in size. Even the article that was previously cited said that one of the final steps will be to do index maintenance on the file you just moved.
OK, my data stays online, my goal is achieved AND with an online index rebuild we're golden. Again, not wrong....
And your logfile explodes in growth as do your backups and you end up with unintended free space in the file larger than the largest index that you rebuild. If you have small tables and indexes, that may not be a problem but for me, that would be a quarter Terabyte problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2018 at 10:16 pm
Jeff Moden - Monday, January 22, 2018 6:10 PMBobby Russell - Monday, January 22, 2018 11:11 AMJeff Moden - Monday, January 22, 2018 10:59 AMBobby Russell - Monday, January 22, 2018 10:16 AMItem 4 was my choice, which IS correct whether you think so or not, and the best way to perform this operation. Why would you consider this incorrect? It is the most correct of your options available.If you can tolerate the extreme amount of data movement, you still end up with all the problems that DBCC SHRINKFILE imposes on a database... massive fragmentation on many indexes caused by the "index page inversion" (for lack of a better term) the DBCC SHRINKFILE causes. If you have a small database, that may not cause you an issue but it'll be a huge problem for larger databases because, in order to get performance back, you'll need to rebuild many indexes and some of them could be huge. That'll cause wanton growth of the MDF/NDF file and cause your log file to explode in size. Even the article that was previously cited said that one of the final steps will be to do index maintenance on the file you just moved.
OK, my data stays online, my goal is achieved AND with an online index rebuild we're golden. Again, not wrong....
And your logfile explodes in growth as do your backups and you end up with unintended free space in the file larger than the largest index that you rebuild. If you have small tables and indexes, that may not be a problem but for me, that would be a quarter Terabyte problem.
i concur.
nice question, tanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
January 23, 2018 at 12:28 am
ildjarn.is.dead - Monday, January 22, 2018 6:48 AMJeff Moden - Monday, January 22, 2018 5:44 AMildjarn.is.dead - Monday, January 22, 2018 3:47 AMThe question isn't "What is the easiest way" but "what should I do". And adding an extra file and migrating the data works.Try your solution with a 2TB database file. 😉
Does that make the answer less correct? Again: Nowhere in the question is 'easiest' mentioned. Answer 4 is a valid answer, although most of the times not the smartest. But answer 3 (the 'correct' answer) is definitely incomplete (you need to offline the database before renaming the datafile), so it's actually less correct than answer #4.
yes. If some one accessing the database, how will you rename?
Manik
You cannot get to the top by sitting on your bottom.
January 23, 2018 at 1:31 am
sqldoubleg - Monday, January 22, 2018 8:50 AMChristoph Muthmann - Monday, January 22, 2018 2:49 AMSetting the database offline as the first step and bringing it back online at the end would be a good idea!Although it is true that you need to set the database offline at certain point to remove the lock SQL Server holds on the database files, the steps to minimise downtime are
- ALTER DATABASE MODIFY FILE to change the file name
- SET DATABASE OFFLINE --> this is actually missing in the "correct answer"
- Change the actual file --> only possible if the db is offline
- SET DATABASE ONLINEObviously you can set offline first, but you are adding down time, it possibly is negligible for a single file, specially if you have everything scripted out, but I'd say its better practice have the database offline as less as possible.
Cheers.
Yes that could be. But I'm submitting the ALTER DATABASE and SET DATABASE ONLINE from the same session in SSMS after renaming the physical file and there is no time gap.
Have a nice day,Christoph
January 23, 2018 at 6:38 am
manik_anu - Tuesday, January 23, 2018 12:28 AMildjarn.is.dead - Monday, January 22, 2018 6:48 AMJeff Moden - Monday, January 22, 2018 5:44 AMildjarn.is.dead - Monday, January 22, 2018 3:47 AMThe question isn't "What is the easiest way" but "what should I do". And adding an extra file and migrating the data works.Try your solution with a 2TB database file. 😉
Does that make the answer less correct? Again: Nowhere in the question is 'easiest' mentioned. Answer 4 is a valid answer, although most of the times not the smartest. But answer 3 (the 'correct' answer) is definitely incomplete (you need to offline the database before renaming the datafile), so it's actually less correct than answer #4.
yes. If some one accessing the database, how will you rename?
Using answer #4 uses DBCC SHRINKFILE with the EMPTYFILE option, which will move the data from the old file to any existing (new file empty , in this case) files in the same file group.. It doesn't require a "rename" of the current file because the contents of the current file will be transferred to the new, correctly named file and then the old, incorrectly named file can be dropped from the system. It's all done without taking the database offline and that is the single advantage that someone may want to consider.
What's to consider? DBCC SHRINK FILE is fairly aggressive and if the database has any heft to it, you can have a long period of relative slowness. After that, many of the indexes will have been logically "inverted" causing massive logical fragmentation and I've seen it where a query that normally takes 500ms suddenly doesn't complete even after 2 hours. That means that you have to rebuild or reorganize the indexes that have been affected in such a manner. If you have a relatively small database, that won't take much time but, unless a special trick is done to prevent it, rebuilding the largest index affected will cause about 120% of the size of the index to become possibly-unwanted free space in the mdf or ldf file and the log file could grow substantially if you are in the full recovery model and have to keep it that way. If you "only" reorganize (which is always done in an online fashion and is always fully logged regardless of recovery model), your log file could still grow a lot depending on the size of the largest index. During that whole time, the machine is quite busy and could cause a long period of slowness that could be quite bothersome to the end users.
To the point of many of the others on this thread, answer #4 IS an answer that will work according to what was asked and could work fairly well especially if you can't afford even sub-second periods of down time on a database provided that you understand that the indexes will need index maintenance and all the caveats that go with that. It's certainly isn't the easiest but it does work and, in certain circumstances, may actually be the best option provided that you do understand all of the ramifications that will affect performance during the evolution whether you have the Standard or Enterprise edition and know how to avoid some of the problems the method will cause (like blowing out the MDF, NDF, and/or LDF files).
The answer being touted as the "correct" answer is nasty fast and absolutely the easiest to do. The problem with it is that it DOES require some downtime for the database even though it's likely to be sub-second. On a really busy system, this will require the use of the SINGLE USER mode with a ROLLBACK immediate to "safely" force everyone and everything out of the database so that it can be taken offline. I say "safely" in quotes because there won't be any corruption of the database BUT any transactions in process will be "safely" rolled back., which could annoy the hell out of a couple thousand users on a busy system.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2018 at 6:45 am
@steve-2 Jones,
Whether or not I agree with the use of answer 4 and whether or not it has a shedload of caveats associated with it and whether or not it's the easiest answer, answer 4 DOES actually get the job done and, in certain circumstances, may actually be the best answer. At the very least, it does solve the problem asked and the word "easiest" was neither stated nor implied in the question nor was it stated that you had to select the "best" answer, which would still be a point of contention depending on other factors.
The great part of all of this is that the question inspired some good conversation but, the fact remains, answer 4 does solve the problem as stated in question 4. Please consider including it as a correct answer even if it isn't the "easiest" answer.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2018 at 10:02 am
I had to guess the answer but I learned a lot from this discussion. Thanks to all, especially to Steve and Jeff.
January 24, 2018 at 10:29 am
If you don't read anything into the question, at least to me, answer 3 is the correct answer. Of course, not all QofT work this way (some do require making some kind of assumption(s)).
January 25, 2018 at 8:52 pm
I'm going to disagree on #4. That might be preferable for some of you, but that's not the question or the point. It doesn't rename the file, it replaces the file with a new file.
There are plenty of systems that don't have hundreds or thousands of users and can tolerate downtime. There are also plenty that can't. The choice here isn't to pick the one that fits your system, but one that answers the question.
January 25, 2018 at 9:52 pm
Steve Jones - SSC Editor - Thursday, January 25, 2018 8:52 PMI'm going to disagree on #4. That might be preferable for some of you, but that's not the question or the point. It doesn't rename the file, it replaces the file with a new file.There are plenty of systems that don't have hundreds or thousands of users and can tolerate downtime. There are also plenty that can't. The choice here isn't to pick the one that fits your system, but one that answers the question.
I'll agree to disagree. The whole purpose of such a drill is to end up with a renamed file and, while you're semantically correct in saying that method 4 doesn't actually rename a file, the same result is achieved and may provide an even better answer depending on whether or not you can allow down time.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2018 at 1:15 pm
Steve Jones - SSC Editor - Thursday, January 25, 2018 8:52 PMI'm going to disagree on #4. That might be preferable for some of you, but that's not the question or the point. It doesn't rename the file, it replaces the file with a new file.There are plenty of systems that don't have hundreds or thousands of users and can tolerate downtime. There are also plenty that can't. The choice here isn't to pick the one that fits your system, but one that answers the question.
Looking at answer 3 a person who is attempting this for the first time may think it can be done as an online operation and will attempt it without calling a downtime and later realize Sql Server will not allow to rename the OS filename without taking DB offline or Sql Server down.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply