March 23, 2016 at 6:19 am
I have 3TB database and MDF is 2TB, how to create secondary file?
March 23, 2016 at 6:34 am
PJ_SQL (3/23/2016)
I have 3TB database and MDF is 2TB, how to create secondary file?
You can use ALTER DATABASE to add a file, the example below adds a file (64Mb) "MyNewDataFile" to the database TEEST
😎
USE [master]
GO
ALTER DATABASE [TEEST] ADD FILE
( NAME = N'MyNewDataFile'
, FILENAME = N'E:\SQLDATA\MyNewDataFile.ndf'
, SIZE = 65536KB
, FILEGROWTH = 65536KB
) TO FILEGROUP [FILE_GROUP_NAME]
GO
March 23, 2016 at 6:41 am
Thanks but how would it spread data from the 1st file which is 2 TB to second file created?
March 23, 2016 at 6:49 am
PJ_SQL (3/23/2016)
Thanks but how would it spread data from the 1st file which is 2 TB to second file created?
1. Create a new file group
2. Add a file to the new file group
3. Move tables into the new file group by rebuilding first the clustered indices and then the nonclustered indices specifying the new file group.
4. If a table is Heap (no clustered index) then add a clustered index to it specifying the new filegroup, nonclustered indices on a Heap table must also be rebuilt.
😎
March 23, 2016 at 8:51 am
PJ_SQL (3/23/2016)
Thanks but how would it spread data from the 1st file which is 2 TB to second file created?
When multiple files are in the same filegroup, SQL Server will automatically spread new allocations (favoring the file with the highest amount of free space).
When the files are in different filegroups, you are in control because you determine on which filegroup a table or index should be created.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply