Sql-server – Automatic database file creation based on database file size in SQL Server 2008 R2

First of all, in SQL Server, there is no automatically confiuration set up to automatically create or add new file to your existing file group when you reach the file size limitation. You have to do it on your own. But you can create script to do it automatically, even though it may not be exactly as you expected.

The logic to split data into different file by your own control is to create a new file group, add new file or add new file to existing file group. But, you have to create new table too.

For example, you have an existing table A. When you create it, you create it on FileGroup1, your FileGroup1 has two files, File1 and File2, you set the File1 and File2 to not autogrow, set them to fixed size 2GB. When you insert data into table A, you can NOT control insert data into which file, File1 or File2, which is controlled by SQL Engine, and because you set them to fixed size, so when both reaches to full, your insert query will throw exception. Certainly you can set them to auto growth. But this will not resolve your issue

What you can do is once table A is getting close to 4GB, create new table B first, after table B create successfully on new file group with new files, take a piece of idle time of table A, rename table A to table A archive and name table B as table A. In this way, the table change is transparent to your application layer.

How to know your table A file size is getting close to 4GB? You can use the following script to get current database file size information, including data file and log file. And you follow certain naming convention to create your FileGroup and Files, then you know what is your table's FileGroup and File name.

select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
from sysfiles

You can check your online table file size periodically, certainly set your file to autogrow, in case your periodically check and table switch fail, you will not lose data.
Once you decide it is time to create new table B, you first add new FileGroup and new file to database.

USE master
GO
ALTER DATABASE TEST
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = 'E:\MSSQL\DATA\TESTdat1.mdf',
    SIZE = 4000MB,
    MAXSIZE = 4000MB,
    FILEGROWTH = 500MB
)
TO FILEGROUP Test1FG1;
GO

After new FileGroup and new file created, you can create new table B

select * 
into table B
from table A
where 1<>1

Certainly, you need to add your indices and constraints. Better to save your table DDL somewhere and get it when you need to create new table.

Last step is to find a right time spot to do table switch. Make sure do it in one transcation.

EXEC sp_rename 'Table A', 'Table A archive'
Exec sp_rename 'Table B', 'Table A'

*The methodolgy above is just one approach, it is definitely not the best approach. *
Other approach:

1. Control the table and file split in your application layer, once you identify the data write to current table reaches some threshold, create new table, basing on timestamp or some naming convention your application knows.
   
   

2. In store procedure layer to control table and file split, normally your application will call store procedure to read and write data, you can use store procedure to create new table, and let the application read and write from new table.
   
   

3. There are more