I am currently migrating databases from SQL Server 2008 (SP4) to SQL Server 2017 (CU3). A significant change is that all filegroups have two data files after migration. To accomplish this task I restore a backup, add the new filegroup with two equally sized files as well as the same autogrowth settings and transfer the data by using the following syntax:
CREATE UNIQUE CLUSTERED INDEX <PK of the table> ..... WITH (DROP_EXISTING = ON ,...) ON <new Filegroup>
Unfortunately I also have to move some LOBs then things get a little more complicated:
- Add a partition scheme and partition function (basically with the same target)
- CREATE the clustered index (WITH DROP_EXISTING=ON) on the new partition scheme
- CREATE the clustered index (WITH DROP_EXISTING=ON) on the new filegroup
- DROP the partition scheme and function
This technique is described by Kimberly Tripp here and goes back to Brad Hoff.
Thank you for staying tuned and now to my question:
How much free space is needed in a filegroup by rebuilding an index like this?
I’ll give you an example:
- I have one LOB table which is 220GB in size (according to total pages in sys.allocation_units divided by 128 divided by 1024).
- Presized the two files in my new (empty) filegroup (to host just that table) by 220/2 = 110 GB each with filegrowth = 0.
- Tried to transfer the table using the technique described above but got error message
Could not allocate space for object ‘xyz’.’xyz_pk’ in database ‘abc’ because the ‘def’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
- Increased the filesize to 112 GB each…tried again and got the same error message
- Finally turned on autogrowth for the files and the process finished successfully
however each file then was 220 GB in size leaving 50% of free space within each file.
Up to now I do not know any other remedy as to perform
DBCC SHRINKFILE to get rid of the ridiculous high amount of free space. However that’s not something I am particulary proud about…it takes ages potentially leaves corruption etc.etc.
Can you help me understanding why SQL Server allocates so much free space while afterwards happily filling my two files proportionally?
I’ll try to prepare a demo afterwards…sorry but I am short on time now and perhaps some of you experts do already know the reason for this.
Thanks in advance for your help
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!