First get some information from your SQL Server and DB.
- Number of Cores of one CPU (Not HT). In my case 6
- Size of the existing DB. Here expected Size 70GB Data and 10 GB Log, Current Size 11GB
Current Situation
There is one file for Data and one file for Logs.
Create a Backup of your DB
Don’t work without backup.
Create new Files
The new Files have to be large enough to store the current data (11GB in this example). So divide the starting size by number of cores of one CPU to get a base size for one file (11GB /6 = close to 2GB) . Add Autogrowth size so it has some space. As this db will reach 70GB as final space within one year a growth of 1GB is OK per file. This makes an initial Size(MB) 3072MB . Use numbers of a multiple of 64 especially for the log file. Info: The files will grow independent according to the fill level.
Info: The file group will not change. This is possible with new databases.
Now start the creation of new file acordiung to your CPU.
Tip: Start creating a file within the SQL-GUI but don’t press OK. Script it to query and multiply the line to the number of files you need and edit the names.
USE [master]
GO
ALTER DATABASE [DWDB] ADD FILE ( NAME = N’MOM_DATA_1′, FILENAME = N’N:\SQL1\Data\DWDB_1.mdf’ , SIZE = 3145728KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [PRIMARY]
GO
…… and so on here 1 to 6
ALTER DATABASE [DWDB] ADD FILE ( NAME = N’MOM_DATA_6′, FILENAME = N’N:\SQL1\Data\DWDB_6.mdf’ ,
SIZE = 3145728KB , FILEGROWTH = 1048576KB ) TO FILEGROUP [PRIMARY]
GO
This will need some time as sql is allocating disk space for the new files. Finaly you get something like this.
Empty the original DB-File
Now we have to move all data to the new created files so only metadata remains. (Here for example DB DWDB and File MOM_DATA)
use DWDB
go
DBCC Shrinkfile (MOM_DATA, EMPTYFILE)
go
Msg 2555 ist OK, as metadata cant be removed.
Now the usage report should show a small base DB file and data within the others.
Resize the base DB File
Resize the master file containing Metadata as no more data should be added there. Limit to a few MB is OK
Shrink the resized file
Run the following SQL Query to shrink the file (DWDB ist the DBname, MOM_DATA is the base dab File)
USE [DWDB]
GO
DBCC SHRINKFILE (N’MOM_DATA’ , 0, TRUNCATEONLY)
GO
After a short time it should look like this.
Fix the log file
The log must be of an initial size and growing in 64 steps (1024 not 1000 as example) or it gets fragmented.
