December 4, 2017 at 1:37 am
Hi,
We are having a production database containing 4 datafiles of 50 GB each, One is in PRIMARY File group having extension .mdf and other three datafiles in "Datafiles" group with extension .ldf. Logically when Primary datafile .mdf is full it should write to reaming data files.
But when Primary datafile reached max limit of 50 GB we receive error "Could not allocate space for object in database because the PRIMARY Filegroup is full" . Can you please advise if there is any configuration changes to be done at database or file level or any setting to be done so that database will write in other datafiles concurrently and we will not received such error in future
Thanks & Regards,
Santosh
December 4, 2017 at 2:01 am
Increase the size of the PRIMARY filegroups files or set autogrowth to on.
Can you post the output of sp_helpfile when run inside the database context, or a screenshot of the file section of the database properties.
Your definition sounds like you have 1 data file with 3 log files, but need to ensure your configuration
December 4, 2017 at 2:12 am
anthony.green - Monday, December 4, 2017 2:01 AMIncrease the size of the PRIMARY filegroups files or set autogrowth to on.Can you post the output of
sp_helpfilewhen run inside the database context, or a screenshot of the file section of the database properties.Your definition sounds like you have 1 data file with 3 log files, but need to ensure your configuration
PFA screen shot , there are 4 datafiles ( 1 is in Primary Filegroup and 3 are in Datafile group) and 1 log file. Current we have fixed the issue by setting the max limit of Primary Group datafile to unlimited it was 50 GB when we had face the error. Please advise how set max limit to datafiles and data to be written to all datafiles concurrently to avoid the error mention above.
Thanks & Regards,
Santosh
December 4, 2017 at 2:24 am
santosh.mane - Monday, December 4, 2017 2:12 AManthony.green - Monday, December 4, 2017 2:01 AMIncrease the size of the PRIMARY filegroups files or set autogrowth to on.Can you post the output of
sp_helpfilewhen run inside the database context, or a screenshot of the file section of the database properties.Your definition sounds like you have 1 data file with 3 log files, but need to ensure your configuration
PFA screen shot , there are 4 datafiles ( 1 is in Primary Filegroup and 3 are in Datafile group) and 1 log file. Current we have fixed the issue by setting the max limit of Primary Group datafile to unlimited it was 50 GB when we had face the error. Please advise how set max limit to datafiles and data to be written to all datafiles concurrently to avoid the error mention above.
Thanks & Regards,
Santosh
OK, from that screenshot, if your getting the error as you don't have enough space on the D drive to grow the file by 1GB.
The PRIMARY filegroup is set to grow when it gets full by 1GB all the time, to a unlimited size, so you have no space on D to grow the file.
What is the output of xp_fixeddrives, if it is less than 1024 for the D drive you will need to go get more space from your storage team.
December 4, 2017 at 5:14 am
anthony.green - Monday, December 4, 2017 2:24 AMsantosh.mane - Monday, December 4, 2017 2:12 AManthony.green - Monday, December 4, 2017 2:01 AMIncrease the size of the PRIMARY filegroups files or set autogrowth to on.Can you post the output of
sp_helpfilewhen run inside the database context, or a screenshot of the file section of the database properties.Your definition sounds like you have 1 data file with 3 log files, but need to ensure your configuration
PFA screen shot , there are 4 datafiles ( 1 is in Primary Filegroup and 3 are in Datafile group) and 1 log file. Current we have fixed the issue by setting the max limit of Primary Group datafile to unlimited it was 50 GB when we had face the error. Please advise how set max limit to datafiles and data to be written to all datafiles concurrently to avoid the error mention above.
Thanks & Regards,
SantoshOK, from that screenshot, if your getting the error as you don't have enough space on the D drive to grow the file by 1GB.
The PRIMARY filegroup is set to grow when it gets full by 1GB all the time, to a unlimited size, so you have no space on D to grow the file.
What is the output of
xp_fixeddrives, if it is less than 1024 for the D drive you will need to go get more space from your storage team.
Hi,
Thanks for quick respont.
PFA requested information. There is not free disk space issue.
Let me explain the issue again, Issue is data is return in only PRIMARY datafile and not in Secondary datafile.
PRIMARY database was restricted with max size of 50 GB which got exceeded and we receive error, as an urgently temporary fix done by removing auto growth max limit of 50 to and set it to unlimited. The question is why the data was written to only PRIMARY Filegroup datafile and not to secondary filegroup. Please advise.
December 4, 2017 at 5:23 am
santosh.mane - Monday, December 4, 2017 5:14 AManthony.green - Monday, December 4, 2017 2:24 AMsantosh.mane - Monday, December 4, 2017 2:12 AManthony.green - Monday, December 4, 2017 2:01 AMIncrease the size of the PRIMARY filegroups files or set autogrowth to on.Can you post the output of
sp_helpfilewhen run inside the database context, or a screenshot of the file section of the database properties.Your definition sounds like you have 1 data file with 3 log files, but need to ensure your configuration
PFA screen shot , there are 4 datafiles ( 1 is in Primary Filegroup and 3 are in Datafile group) and 1 log file. Current we have fixed the issue by setting the max limit of Primary Group datafile to unlimited it was 50 GB when we had face the error. Please advise how set max limit to datafiles and data to be written to all datafiles concurrently to avoid the error mention above.
Thanks & Regards,
SantoshOK, from that screenshot, if your getting the error as you don't have enough space on the D drive to grow the file by 1GB.
The PRIMARY filegroup is set to grow when it gets full by 1GB all the time, to a unlimited size, so you have no space on D to grow the file.
What is the output of
xp_fixeddrives, if it is less than 1024 for the D drive you will need to go get more space from your storage team.Hi,
Thanks for quick respont.
PFA requested information. There is not free disk space issue.
Let me explain the issue again, Issue is data is return in only PRIMARY datafile and not in Secondary datafile.
PRIMARY database was restricted with max size of 50 GB which got exceeded and we receive error, as an urgently temporary fix done by removing auto growth max limit of 50 to and set it to unlimited. The question is why the data was written to only PRIMARY Filegroup datafile and not to secondary filegroup. Please advise.
The problem is then down to your design of the database. You have two filegroups, PRIMARY and Datafiles and the object you are inserting into lives in PRIMARY and not Datafiles. If all your objects must live on the Datafiles filegroup you will need to migrate the objects from PRIMARY to Datafiles.
The below script will show you what objects are on the PRIMARY filegroup
SELECT
o.[name] AS ObjectName,
o.[type] AS ObjectType,
s.[name] AS SchemaName,
f.[name] AS [Filename],
i.[name] AS PrimaryKey,
i.[index_id] AS IndexID
FROM
sys.indexes i
INNER JOIN
sys.filegroups f
ON
i.data_space_id = f.data_space_id
INNER JOIN
sys.all_objects o
ON
i.[object_id] = o.[object_id]
INNER JOIN
sys.schemas s
ON
s.[schema_id] = o.[schema_id]
order by
s.[name],
o.[name]
Data lives where you tell it to live, if the table is created on PRIMARY it will not do and create the object in Datafiles.
So if the PRIMARY filegroup is marked as default, every create table statement you do creates the table in PRIMARY not Datafiles unless you specify at create time you want it to live in Datafiles.
Your going to have to move the objects which shouldn't be on PRIMARY.
December 4, 2017 at 5:28 am
anthony.green - Monday, December 4, 2017 5:23 AMsantosh.mane - Monday, December 4, 2017 5:14 AManthony.green - Monday, December 4, 2017 2:24 AMsantosh.mane - Monday, December 4, 2017 2:12 AManthony.green - Monday, December 4, 2017 2:01 AMIncrease the size of the PRIMARY filegroups files or set autogrowth to on.Can you post the output of
sp_helpfilewhen run inside the database context, or a screenshot of the file section of the database properties.Your definition sounds like you have 1 data file with 3 log files, but need to ensure your configuration
PFA screen shot , there are 4 datafiles ( 1 is in Primary Filegroup and 3 are in Datafile group) and 1 log file. Current we have fixed the issue by setting the max limit of Primary Group datafile to unlimited it was 50 GB when we had face the error. Please advise how set max limit to datafiles and data to be written to all datafiles concurrently to avoid the error mention above.
Thanks & Regards,
SantoshOK, from that screenshot, if your getting the error as you don't have enough space on the D drive to grow the file by 1GB.
The PRIMARY filegroup is set to grow when it gets full by 1GB all the time, to a unlimited size, so you have no space on D to grow the file.
What is the output of
xp_fixeddrives, if it is less than 1024 for the D drive you will need to go get more space from your storage team.Hi,
Thanks for quick respont.
PFA requested information. There is not free disk space issue.
Let me explain the issue again, Issue is data is return in only PRIMARY datafile and not in Secondary datafile.
PRIMARY database was restricted with max size of 50 GB which got exceeded and we receive error, as an urgently temporary fix done by removing auto growth max limit of 50 to and set it to unlimited. The question is why the data was written to only PRIMARY Filegroup datafile and not to secondary filegroup. Please advise.The problem is then down to your design of the database. You have two filegroups, PRIMARY and Datafiles and the object you are inserting into lives in PRIMARY and not Datafiles. If all your objects must live on the Datafiles filegroup you will need to migrate the objects from PRIMARY to Datafiles.
The below script will show you what objects are on the PRIMARY filegroup
SELECT
o.[name] AS ObjectName,
o.[type] AS ObjectType,
s.[name] AS SchemaName,
f.[name] AS [Filename],
i.[name] AS PrimaryKey,
i.[index_id] AS IndexID
FROM
sys.indexes i
INNER JOIN
sys.filegroups f
ON
i.data_space_id = f.data_space_id
INNER JOIN
sys.all_objects o
ON
i.[object_id] = o.[object_id]
INNER JOIN
sys.schemas s
ON
s.[schema_id] = o.[schema_id]
order by
s.[name],
o.[name]Data lives where you tell it to live, if the table is created on PRIMARY it will not do and create the object in Datafiles.
So if the PRIMARY filegroup is marked as default, every create table statement you do creates the table in PRIMARY not Datafiles unless you specify at create time you want it to live in Datafiles.
Your going to have to move the objects which shouldn't be on PRIMARY.
Thanks for your instant reply and support, very much appreciated.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply