April 4, 2018 at 2:47 pm
I have a question, I hope someone here can answer. I have searched multiple web sites, and can't get a definitive answer.
if I execute a DBCC Shrinkfile EMPTYFILE, and stop emptying the file, will this allow additional data to be re-input, into that file?
Is there a 'flag' that says this file is being emptied, is it reset if I cancel the DBCC, and is there anyway to confirm it?
I know you can cancel a DBCC Shrinkfile, without an issue, and I know that the pages are logged and moved in short transactions. I also know that is true for the Emptyfile option. I'm just not 100% sure about what happens if you kill the process, or cancel the query. I am pretty sure it doesn't put anything back in the file, but I want to continue using it.
Thanks,
Leonard
April 5, 2018 at 3:15 am
Leonard Rutkowski - Wednesday, April 4, 2018 2:47 PMI have a question, I hope someone here can answer. I have searched multiple web sites, and can't get a definitive answer.
if I execute a DBCC Shrinkfile EMPTYFILE, and stop emptying the file, will this allow additional data to be re-input, into that file?
Is there a 'flag' that says this file is being emptied, is it reset if I cancel the DBCC, and is there anyway to confirm it?
I know you can cancel a DBCC Shrinkfile, without an issue, and I know that the pages are logged and moved in short transactions. I also know that is true for the Emptyfile option. I'm just not 100% sure about what happens if you kill the process, or cancel the query. I am pretty sure it doesn't put anything back in the file, but I want to continue using it.Thanks,
Leonard
Quick question, what is the goal of this exercise?
😎
Further, can you share some more information, such as the db's file configuration, file groups, are you working with the master data file or secondary files in the same file group etc?
April 5, 2018 at 6:13 am
I have multiple file groups, primary, data, index, and lob. I am trying to balance the data across all of the individual files. I am not doing the primary just yet. there are only 2 files on that file group anyway. For example, I have 7 files in the index file group. Each of these have disparate sizes, from 30GB to over 500GB. I am trying to 'balance' this out, so they are all of the same size. Once that is done, I am going to turn on trace flag 1117. Rebuilding the index is one way of spreading the data, and I am working on that, but that requires quiet time, due to potential blocking, etc. So, my thought was, can I start an DBCC SHRINKFILE EMPTYFILE, and move some of the data. I would cancel it after a certain period, then shrink the file. I have to do this in stages, due to space constraints on each of the drives. But, if it is going to mark the file as unusable, and not allow it to be used for new data, then that approach won't work. So, I just need to know what the impact is. I believe it would be ok, but wanted to be 100%.
Once all that is done, I plan to do the index rebuilds.
Thanks,
Leonard
April 5, 2018 at 8:15 am
Leonard Rutkowski - Thursday, April 5, 2018 6:13 AMI have multiple file groups, primary, data, index, and lob. I am trying to balance the data across all of the individual files. I am not doing the primary just yet. there are only 2 files on that file group anyway. For example, I have 7 files in the index file group. Each of these have disparate sizes, from 30GB to over 500GB. I am trying to 'balance' this out, so they are all of the same size. Once that is done, I am going to turn on trace flag 1117. Rebuilding the index is one way of spreading the data, and I am working on that, but that requires quiet time, due to potential blocking, etc. So, my thought was, can I start an DBCC SHRINKFILE EMPTYFILE, and move some of the data. I would cancel it after a certain period, then shrink the file. I have to do this in stages, due to space constraints on each of the drives. But, if it is going to mark the file as unusable, and not allow it to be used for new data, then that approach won't work. So, I just need to know what the impact is. I believe it would be ok, but wanted to be 100%.
Once all that is done, I plan to do the index rebuilds.
Thanks,
Leonard
Before you start using EMPTYFILE you would need to have all files in the file group sized the same - with autogrowth set to grow each file the same and at the same time. If you don't have that - then the proportional algorithm used to move the data is going to move more of the data to the largest file, then the next largest, etc...
If you don't have the space available to grow the smaller files to the expected end size - because the larger files have to be shrunk first then you wouldn't be able to run the empty file successfully either.
If you have the space available - then resize the smaller files to match the largest file then rebuild the indexes. If you can leave it like that - then you will just have a lot of space available for future growth. If your system will never grow to use that additional space, then you have the option to remove one (or more) of the files using empty file to recover the space on disk.
Finally - if you can rebuild the indexes online and sort in tempdb you can reduce the impact of the index rebuilds to avoid blocking issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 5, 2018 at 8:59 am
I have sized most of the others, to the anticipated size. The largest one, that I am trying to reduce, has no free space, and if I use the emptyfile, the algorithm doesn't come into play for that file anyway. Your right, I don't have the space, but that is why I was going to cancel the emptyfile, dbcc after a certain point. it wouldn't empty the file completely, just down to the size I wanted. That was what the original question was about. What happens if I cancel the DBCC? There is no rollback, so that's fine, some of the data would have been moved, and if the Emptyfile is cancelled, then no harm. if the Emptyfile stays set, and no additional data can be added to the file, then that is not what I want. This would have a minimal impact, as opposed to a bigger impact when I do the index rebuild.
The algorithm would come into play during the index rebuild, which is exactly what I want. Once everything is the same size, then the data will be spread evenly across the files. I believe the algorithm is based on percentage of free space, and not necessarily just on size. So a 500GB file that is full, would get none, while a 150GB file that only has 50GB, would get data. There are probably other factors in play as well.
Leonard
April 12, 2018 at 1:01 pm
From my reading and testing, once you run dbcc shrinkfile(file, emptyfile), the file is marked as not ready for more data. If you cancel this, I'm not sure if the EMPTYFILE will remain in effect, you'd have to try this on a test system. On small systems, once I run this, I can't add data to the empty file any longer.
If you are trying to balance out the data, what I'd really do is just resize the files. Ultimately, you shouldn't be concerned about the data being written to a file, but rather a filegroup. The algorithm seems to work on free space, not file size, as you've noted. Over time things will even out.
If this is really a concern, I'd build a new filegroup, size things appropriately, and then migrate all your objects to that filegroup. Then you can shrink or drop the existing filegroup.
April 12, 2018 at 1:19 pm
OK, another test with larger files and this seems to work. If I have files that are set with a certain amount of data, with sizes like this:
Segment Name AllocatedMB UsedMB AvailableMB PercentUsed
EmptyFileTest 56.00 20.69 35.31 36.95
SecondFile 56.00 24.56 31.44 43.86
ThirdFile 56.00 47.75 8.25 85.27
I can then run thisDBCC SHRINKFILE(EmptyFileTest, EMPTYFILE)
and cancel it. I then see this:
Segment Name AllocatedMB UsedMB AvailableMB PercentUsed
EmptyFileTest 56.00 20.69 35.31 36.95
SecondFile 56.00 24.56 31.44 43.86
ThirdFile 56.00 47.75 8.25 85.27
I add data, and see this:
Segment Name AllocatedMB UsedMB AvailableMB PercentUsed
EmptyFileTest 60.00 56.00 4.00 93.33
SecondFile 60.00 59.50 0.50 99.17
ThirdFile 56.00 56.00 0.00 100.00
It appears I can still add data to the big file, in proportion.
April 13, 2018 at 1:48 pm
Thanks Steve. That was my next step. I was going to do some testing, to see what happens, but you beat me to it. I just thought if someone already knew the answer, then I wouldn't have to actually do the testing. Anyway, thanks for that. It looks like this may be a moot point, as we are going to upgrade the server, and when we do, we will get more space. I still plan on balancing out the files in the file groups.
Leonard
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply