Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?.

share|improve this question

migrated from stackoverflow.com Mar 29 '13 at 11:08

This question came from our site for professional and enthusiast programmers.

    
If we can create more data files for same profile means how we access those data via tsql queries. –  KESAVAN PURUSOTHAMAN Mar 29 '13 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 '13 at 7:05
    
can i keep same table in both the files? –  KESAVAN PURUSOTHAMAN Mar 29 '13 at 7:10
    
I'm unaware of this, the logical answer though is no, it'll significantly lower select performance. –  Vesper Mar 29 '13 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... –  Aaron Bertrand Mar 29 '13 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
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

share|improve this answer
    
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. –  KESAVAN PURUSOTHAMAN Mar 29 '13 at 11:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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