Douglas Rosa
Hi I would like to go back to the traditional design of one mdf file and one ldf file, to be able to migrate my enterprise edition to a standard edition of SQL.I had around 28 files created in the database and I started to empty each file until I ended up with exactly 8, one file per filegroup in my database
When I want to empty the last file in each group it gives me this error
There is insufficient space in the filegroup to complete the emptyfile operation.
I can no longer delete it because it gives me an error telling that the file has data.
Como dijo John, no hay ninguna limitación en la edición Standard que impida que tengas varios archivos por base de datos. Inclusive en la edición Express es posible hacerlo.
El problema es que estás utilizando DBCC SHRINKFILE with EMPTYFILE para vaciar los archivos pero lo que tienes que hacer es mover los objetos (tablas e índices) al filegroup PRIMARY como indica el artículo que incluí antes.
Por cierto, el diseño de un solo archivo de datos no es "tradicional", sólo es el default. Lo común es que haya varios archivos de datos para facilitar la administración y, bajo ciertas condiciones, optimizar el rendimiento.
As John said, there's no limitation in the Standard edition that prevents you from having multiple files per database. Even in the Express edition is possible.
The problem is that you're using DBCC SHRINKFILE with EMPTYFILE to empty the files but what you need to do is to move the objects (tables and indexes) to the PRIMARY filegroup as stated in the article I included earlier.
By the way, the design of a single data file is not "traditional", it's just the default. The common practice is to have multiple data files to ease management and, under certain circumstances, improve performance.