Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. In this database data are logging in every seconds. Here i wants to maintain
the data file size as 4GB. If the size excited the limit then i wants to create one new data file under the same profile. Is there any possible options available in SQL Server 2008 R2?.

  • If we can create more data files for same profile means how we access those data via tsql queries.
    Mar 29, 2013 at 7:00
  • Nothing should change for the database query-wise, you're querying the database as a whole, and it's SQL server's job to select files. Also I'm afraid youwon't be able to auto-create additional files with database settings, but rather create a number of files, spread tables across them and watch their sizes.
    – Vesper
    Mar 29, 2013 at 7:05
  • can i keep same table in both the files?
    Mar 29, 2013 at 7:10
  • I'm unaware of this, the logical answer though is no, it'll significantly lower select performance.
    – Vesper
    Mar 29, 2013 at 7:17
  • 4
    It's 2013. Why are you using FAT32? Is your operating system also x86? Please step out of the dark ages... Mar 29, 2013 at 13:19

1 Answer 1


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
ALTER DATABASE AdventureWorks2012 
    NAME = test1dat3,
    FILENAME = 'E:\MSSQL\DATA\TESTdat1.mdf',
    SIZE = 4000MB,
    MAXSIZE = 4000MB,

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

  • Explain me more things on store procedure approach. I like to remember you that i can't able to store the data file more than 4GB. That's my problem i need to make the database into more than one data file only when the data file size reach the 4GB limit. Mar 29, 2013 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.