Could not allocate space for object 'xxx' in database 'abc' becaus

Error 1105 Could not allocate space for object 'xxx' in database 'abc'
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?
have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:

> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
|||Hi Paulo,
FILEGROWTH=0%. I don't want it to autogrow but in current config there is
still plenty of space for growth.
Rgds.
"Paulo Ferreira" wrote:
[vbcol=seagreen]
> have you checked if there is any limit to the auto-growth of the data files
> configured?
>
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "sysbox27" wrote:
|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||and how many filegroups do you have?
all tables are on the primary filegroup?
what is the sql server version? do you have the last sp applied?
what is the size of the database?
what are the limitations and space allocated on the data and log files?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
[vbcol=seagreen]
> Hi Paulo,
> FILEGROWTH=0%. I don't want it to autogrow but in current config there is
> still plenty of space for growth.
> Rgds.
>
> "Paulo Ferreira" wrote:

Could not allocate space for object 'xxx' in database 'abc' becaus

Error 1105 Could not allocate space for object 'xxx' in database 'abc'
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>|||Hi Paulo,
FILEGROWTH=0%. I don't want it to autogrow but in current config there is
still plenty of space for growth.
Rgds.
"Paulo Ferreira" wrote:
> have you checked if there is any limit to the auto-growth of the data files
> configured?
>
> --
> Paulo Ferreira
> http://www.info2k.pt
> SQL Server DBA Experts
>
>
> "sysbox27" wrote:
> > Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> > because the 'PRIMARY' filegroup is full..
> >
> > Hello,
> > I hope somebody can help me with this issue:
> > The message above appears on a db that has separately located data and log
> > files. the host server itself has ample disk space,
> > but the db is not set to autogrow but instead has
> > been sized appropriately and about half of the data is still free (several
> > hundred mb). the db options are all set appropriately.
> > I have dumped the tran log and rebooted server.
> >
> > I perform a select * into x from y (where y has 1 row of data) and I get the
> > above error, yet if I create table x and then insert
> > 20 rows of data I don't get an error.
> >
> > I am missing something obvious?
> >|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||and how many filegroups do you have?
all tables are on the primary filegroup?
what is the sql server version? do you have the last sp applied?
what is the size of the database?
what are the limitations and space allocated on the data and log files?
--
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:
> Hi Paulo,
> FILEGROWTH=0%. I don't want it to autogrow but in current config there is
> still plenty of space for growth.
> Rgds.
>
> "Paulo Ferreira" wrote:
> > have you checked if there is any limit to the auto-growth of the data files
> > configured?
> >
> >
> > --
> > Paulo Ferreira
> >
> > http://www.info2k.pt
> > SQL Server DBA Experts
> >
> >
> >
> >
> > "sysbox27" wrote:
> >
> > > Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> > > because the 'PRIMARY' filegroup is full..
> > >
> > > Hello,
> > > I hope somebody can help me with this issue:
> > > The message above appears on a db that has separately located data and log
> > > files. the host server itself has ample disk space,
> > > but the db is not set to autogrow but instead has
> > > been sized appropriately and about half of the data is still free (several
> > > hundred mb). the db options are all set appropriately.
> > > I have dumped the tran log and rebooted server.
> > >
> > > I perform a select * into x from y (where y has 1 row of data) and I get the
> > > above error, yet if I create table x and then insert
> > > 20 rows of data I don't get an error.
> > >
> > > I am missing something obvious?
> > >

Could not allocate space for object 'xxx' in database 'abc' becaus

Error 1105 Could not allocate space for object 'xxx' in database 'abc'
because the 'PRIMARY' filegroup is full..
Hello,
I hope somebody can help me with this issue:
The message above appears on a db that has separately located data and log
files. the host server itself has ample disk space,
but the db is not set to autogrow but instead has
been sized appropriately and about half of the data is still free (several
hundred mb). the db options are all set appropriately.
I have dumped the tran log and rebooted server.
I perform a select * into x from y (where y has 1 row of data) and I get the
above error, yet if I create table x and then insert
20 rows of data I don't get an error.
I am missing something obvious?have you checked if there is any limit to the auto-growth of the data files
configured?
Paulo Ferreira
http://www.info2k.pt
SQL Server DBA Experts
"sysbox27" wrote:

> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get t
he
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>|||"sysbox27" <sysbox27@.discussions.microsoft.com> wrote in message
news:A51F188A-BE9E-44F2-B8D9-AFC6F3105187@.microsoft.com...
> Error 1105 Could not allocate space for object 'xxx' in database 'abc'
> because the 'PRIMARY' filegroup is full..
> Hello,
> I hope somebody can help me with this issue:
> The message above appears on a db that has separately located data and log
> files. the host server itself has ample disk space,
> but the db is not set to autogrow but instead has
> been sized appropriately and about half of the data is still free (several
> hundred mb). the db options are all set appropriately.
> I have dumped the tran log and rebooted server.
> I perform a select * into x from y (where y has 1 row of data) and I get
> the
> above error, yet if I create table x and then insert
> 20 rows of data I don't get an error.
> I am missing something obvious?
>
DBCC updatestats
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Could not allocate space for object 'MSrepl_commands' in database

We have transaction replication set up in SQL 2K environment . It's simple
SQL 2k publisher to SQL 2K subscriber (both Enterprise). It works fine but
some times(once a week or so) we get error message "Could not allocate space
for object 'MSrepl_commands' in database 'zzzz_distribution' " and log reader
agent gets stopped. I don't see any issues with space and both data and log
files are set for auto grow. When I go back and see DB size is very minimal.
Can you throw some ideas.
Thanks,
Prabhu
This looks like a problem with the distribution database being unable to
grow, can you confirm that the distribution database has space to grow, and
the growth increment is not a percent but a fixed amount like 100 Mgs.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:7BD73EC1-0AF2-494B-AD3C-6AE6F3D2D605@.microsoft.com...
> We have transaction replication set up in SQL 2K environment . It's simple
> SQL 2k publisher to SQL 2K subscriber (both Enterprise). It works fine but
> some times(once a week or so) we get error message "Could not allocate
> space
> for object 'MSrepl_commands' in database 'zzzz_distribution' " and log
> reader
> agent gets stopped. I don't see any issues with space and both data and
> log
> files are set for auto grow. When I go back and see DB size is very
> minimal.
> Can you throw some ideas.
> Thanks,
> Prabhu

'Could not allocate space for object 'dbo.SORT temporary run storage'

Hello all,
I’m trying to transfer/transform some data from one table to another, both on the same database. The source table holds 92M records.
To do so I'm using the next statement:
INSERT INTO [dbo].[Messages1]
([Time] ,[ID] ,[ResponseTo] ,[MessageMD5] ,[source] ,[dest]
,[arln_err] ,[ErrorDescription] ,[ErrorNumber] ,[ErrorSource]
,[ErrorType] ,[HttpCall] ,[HttpStatus] ,[QuoteAmount]
,[ReservationPickupLocation] ,[RatePickupLocationDescription] ,[RateReqCarType] ,[RateReqPickupLocation]
,[RejectMessage] ,[ReservationAmount] ,[ReservationCarType] ,[ReservationCarTypeDescription]
,[RatePickupLocation] ,[resp1] ,[ResultNum] ,[strRejectMessage]
,[strResultNum])
SELECT [Time] ,[ID] ,[ResponseTo] ,[MessageMD5] ,[source] ,[dest]
,[EE01] ,[EE02] ,[EE03] ,[EE04]
,[EE05] ,[EE06] ,[EE07] ,cast([EE08] as float)
,[EE09] ,[EE10] ,[EE11] ,[EE12]
,[EE13] ,cast ([EE14] as float) ,[EE15] ,[EE16]
,[EE17] ,[EE18] ,[EE19] ,[EE20]
,[EE21]
FROM [dbo].[Messages]
And I’m getting next exception:
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 185394470715392' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I’m having enough disk space, and the PRIMARY and log files are set to auto grow. I also try to add aditionary log files – with no success.
How should I perform this task? How do I stop the database logging for this action or even for the whole database?
Regards
Ronen S.

i will be both tempdb and your data/log file of the user database is growing, and then you run out of disk somewhere. Try rerunnig and monitoring the disks the db files reside on.

Another thing you can do is break up the insert into smaller-sized transactions, i.e. insert ranges, maybe one or two million at a time. That will be more performant and should be faster.

|||

Hello Greg,

It looks to me the problem is while creating temporary table space for the primary key sort.

The statement fail after running for 4 minutes, so the disk space is not running out (I have 52G of free disk). anyhow - how can I monitor table/log size?

About your second suggestion how can I take a million records in a time? is there a something like Oracle psaudo column ROWNUM?

Thanks in advance.

Ronen S.

|||

The error messages talked about tempdb space usage. It indicated that the tempdb data and log file run out of space.

If you are sure that you have enough space for tempdb data and log file, then the problem probably will be that you do not have autogrow or your tempdb files reached to its size limit.

Thanks

Stephen

|||

I would BCP your data out to a file and then BCP the data back in to a table with no indexes.

Then create you indexes after.

Could not allocate space for object ... in database ... because the ... filegroup is full.

Hello,
I keep getting the following error from time to time:
"Could not allocate space for object ... in database ... because the
... filegroup is full."
The file group currently has a size of 2338 MB.
It is set to automatically grow by steps of 200 MB, I tried a grow by
10 percent but had the same problem.
My application is using ADO and ODBC.
My application succeeds when I relaunch it at a later time.
The version of the engine is SQL Server Entreprise Edition 8.00.760
(SP3).
The version of the ODBC Driver is 03.81.9041.
By the way I see in the trace of the SQL Server that it was doing a
backup of the transaction log just before the failure.
As a matter of fact I created a Database Management Plan that is making
backups of the transaction log periodically outside the period when my
application is scheduled and for some resaon my application was running
longer than expected.
Might this be the reason ?
Thanks in advance,
Eric.
Eric
Do you have enough free space on the disk?
"Eric Paesmans" <Eric.Paesmans@.missioncriticalit.com> wrote in message
news:1164627437.187453.49910@.45g2000cws.googlegrou ps.com...
> Hello,
> I keep getting the following error from time to time:
> "Could not allocate space for object ... in database ... because the
> ... filegroup is full."
> The file group currently has a size of 2338 MB.
> It is set to automatically grow by steps of 200 MB, I tried a grow by
> 10 percent but had the same problem.
> My application is using ADO and ODBC.
> My application succeeds when I relaunch it at a later time.
> The version of the engine is SQL Server Entreprise Edition 8.00.760
> (SP3).
> The version of the ODBC Driver is 03.81.9041.
> By the way I see in the trace of the SQL Server that it was doing a
> backup of the transaction log just before the failure.
> As a matter of fact I created a Database Management Plan that is making
> backups of the transaction log periodically outside the period when my
> application is scheduled and for some resaon my application was running
> longer than expected.
> Might this be the reason ?
> Thanks in advance,
> Eric.
>

Could not allocate space for object ... in database ... because the ... filegroup is full.

Hello,
I keep getting the following error from time to time:
"Could not allocate space for object ... in database ... because the
... filegroup is full."
The file group currently has a size of 2338 MB.
It is set to automatically grow by steps of 200 MB, I tried a grow by
10 percent but had the same problem.
My application is using ADO and ODBC.
My application succeeds when I relaunch it at a later time.
The version of the engine is SQL Server Entreprise Edition 8.00.760
(SP3).
The version of the ODBC Driver is 03.81.9041.
By the way I see in the trace of the SQL Server that it was doing a
backup of the transaction log just before the failure.
As a matter of fact I created a Database Management Plan that is making
backups of the transaction log periodically outside the period when my
application is scheduled and for some resaon my application was running
longer than expected.
Might this be the reason ?
Thanks in advance,
Eric.Eric
Do you have enough free space on the disk?
"Eric Paesmans" <Eric.Paesmans@.missioncriticalit.com> wrote in message
news:1164627437.187453.49910@.45g2000cws.googlegroups.com...
> Hello,
> I keep getting the following error from time to time:
> "Could not allocate space for object ... in database ... because the
> ... filegroup is full."
> The file group currently has a size of 2338 MB.
> It is set to automatically grow by steps of 200 MB, I tried a grow by
> 10 percent but had the same problem.
> My application is using ADO and ODBC.
> My application succeeds when I relaunch it at a later time.
> The version of the engine is SQL Server Entreprise Edition 8.00.760
> (SP3).
> The version of the ODBC Driver is 03.81.9041.
> By the way I see in the trace of the SQL Server that it was doing a
> backup of the transaction log just before the failure.
> As a matter of fact I created a Database Management Plan that is making
> backups of the transaction log periodically outside the period when my
> application is scheduled and for some resaon my application was running
> longer than expected.
> Might this be the reason ?
> Thanks in advance,
> Eric.
>|||Yes,
I got more than 12 GB on the disk where my DB files are located, almost
2 GB on my system disk and a bit more than 1 GB on the disk where the
software is installed.
Concerning the interference with the backup of the transaction log I
mentioned, I checked and the backup happened only on the production
server and not on the acceptance server while my application failed on
both servers.
It might have happened that the network was disturbed at that time.
Might this have an impact ?
Thanks in advance,
Eric.
> Eric
> Do you have enough free space on the disk?
> "Eric Paesmans" <Eric.Paesmans@.missioncriticalit.com> wrote in message
> news:1164627437.187453.49910@.45g2000cws.googlegroups.com...
>> Hello,
>> I keep getting the following error from time to time:
>> "Could not allocate space for object ... in database ... because the
>> ... filegroup is full."
>> The file group currently has a size of 2338 MB.
>> It is set to automatically grow by steps of 200 MB, I tried a grow by
>> 10 percent but had the same problem.
>> My application is using ADO and ODBC.
>> My application succeeds when I relaunch it at a later time.
>> The version of the engine is SQL Server Entreprise Edition 8.00.760
>> (SP3).
>> The version of the ODBC Driver is 03.81.9041.
>> By the way I see in the trace of the SQL Server that it was doing a
>> backup of the transaction log just before the failure.
>> As a matter of fact I created a Database Management Plan that is making
>> backups of the transaction log periodically outside the period when my
>> application is scheduled and for some resaon my application was running
>> longer than expected.
>> Might this be the reason ?
>> Thanks in advance,
>> Eric.
>|||One more thought.
Every evening I am shrinking the database (DBCC SHRINKDATABASE ...).
Might there be a side effect ?
Thanks in advance,
Eric.
> Yes,
> I got more than 12 GB on the disk where my DB files are located, almost
> 2 GB on my system disk and a bit more than 1 GB on the disk where the
> software is installed.
> Concerning the interference with the backup of the transaction log I
> mentioned, I checked and the backup happened only on the production
> server and not on the acceptance server while my application failed on
> both servers.
> It might have happened that the network was disturbed at that time.
> Might this have an impact ?
> Thanks in advance,
> Eric.
>> Eric
>> Do you have enough free space on the disk?
>> "Eric Paesmans" <Eric.Paesmans@.missioncriticalit.com> wrote in message
>> news:1164627437.187453.49910@.45g2000cws.googlegroups.com...
>> Hello,
>> I keep getting the following error from time to time:
>> "Could not allocate space for object ... in database ... because the
>> ... filegroup is full."
>> The file group currently has a size of 2338 MB.
>> It is set to automatically grow by steps of 200 MB, I tried a grow by
>> 10 percent but had the same problem.
>> My application is using ADO and ODBC.
>> My application succeeds when I relaunch it at a later time.
>> The version of the engine is SQL Server Entreprise Edition 8.00.760
>> (SP3).
>> The version of the ODBC Driver is 03.81.9041.
>> By the way I see in the trace of the SQL Server that it was doing a
>> backup of the transaction log just before the failure.
>> As a matter of fact I created a Database Management Plan that is making
>> backups of the transaction log periodically outside the period when my
>> application is scheduled and for some resaon my application was running
>> longer than expected.
>> Might this be the reason ?
>> Thanks in advance,
>> Eric.
>>|||> Every evening I am shrinking the database (DBCC SHRINKDATABASE ...).
> Might there be a side effect ?
Yes, you don't want to shrink regularly. There are plenty of reasons for this, see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Sometime autogrow doesn't seem to work, because the allocation of new storage isn't "fast enough".
Short story is that autogrow should be seen as something you have so you don't get paged in the
middle of the night. For normal operations, make sure that the databases has some air, and monitor
and pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric Paesmans" <Eric.Paesmans@.missioncriticalit.com> wrote in message
news:456c2a5b$0$1284$6c56d894@.reader0.news.be.easynet.net...
> One more thought.
> Every evening I am shrinking the database (DBCC SHRINKDATABASE ...).
> Might there be a side effect ?
> Thanks in advance,
> Eric.
>> Yes,
>> I got more than 12 GB on the disk where my DB files are located, almost 2 GB on my system disk
>> and a bit more than 1 GB on the disk where the software is installed.
>> Concerning the interference with the backup of the transaction log I mentioned, I checked and the
>> backup happened only on the production server and not on the acceptance server while my
>> application failed on both servers.
>> It might have happened that the network was disturbed at that time.
>> Might this have an impact ?
>> Thanks in advance,
>> Eric.
>> Eric
>> Do you have enough free space on the disk?
>> "Eric Paesmans" <Eric.Paesmans@.missioncriticalit.com> wrote in message
>> news:1164627437.187453.49910@.45g2000cws.googlegroups.com...
>> Hello,
>> I keep getting the following error from time to time:
>> "Could not allocate space for object ... in database ... because the
>> ... filegroup is full."
>> The file group currently has a size of 2338 MB.
>> It is set to automatically grow by steps of 200 MB, I tried a grow by
>> 10 percent but had the same problem.
>> My application is using ADO and ODBC.
>> My application succeeds when I relaunch it at a later time.
>> The version of the engine is SQL Server Entreprise Edition 8.00.760
>> (SP3).
>> The version of the ODBC Driver is 03.81.9041.
>> By the way I see in the trace of the SQL Server that it was doing a
>> backup of the transaction log just before the failure.
>> As a matter of fact I created a Database Management Plan that is making
>> backups of the transaction log periodically outside the period when my
>> application is scheduled and for some resaon my application was running
>> longer than expected.
>> Might this be the reason ?
>> Thanks in advance,
>> Eric.
>>

Could not allocate space for object .. PRIMARY filegroup is full

I am adding a lot of data to my "SQL Server database". Suddenly I get th following error:

SYMBOLGEN: Macro variable TBL resolves to SERVICE_DATA
SYMBOLGEN: Macro variable TBL resolves to SERVICE_DATA
MPRINT(MAIN): proc append base=ODS.AAK_SERVICE_DATA data=TMP.SERVICE_DATA;
NOTE: Appending TMP.SERVICE_DATA to ODS.AAK_SERVICE_DATA.
WARNING: Variable LOST_DAYS_CURR_ENLIST has different lengths on BASE and DATA files (BASE 8 DATA 4).
WARNING: Variable MI_LOST_DAYS_CURR_ENLIST has different lengths on BASE and DATA files (BASE 8 DATA 4).
NOTE: There were 287898 observations read from the data set TMP.SERVICE_DATA.
NOTE: 287897 observations added.
NOTE: The data set ODS.AAK_SERVICE_DATA has . observations and 19 variables.
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object
'AAK_SERVICE_DATA' in database 'AAK_ODS' because the 'PRIMARY' filegroup is full.
NOTE: Statements not processed because of errors noted above.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE APPEND used:
real time 1:10.04
cpu time 22.86 seconds

Note I have set the automatic file growth to 10% on the SQL Server side.

Any solutions out there?Is this disk full?

Note - you should always set a max size to stop uncontrolled growth.

Could not allocate space for object

There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !
Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
noticed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !
|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:

> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>
|||If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...[vbcol=seagreen]
> How can I pre-allocate storage. There is space on disk. I tried to create a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:

> If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>
|||I see. Can you post the TSQL command you tried and the exact error message returned?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...[vbcol=seagreen]
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:

> I see. Can you post the TSQL command you tried and the exact error message returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
>

Could not allocate space for object

There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (
as you have just
noticed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying t
o
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Li
ne
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:

> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow
(as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>|||If you want to do in EM, right-click database, properties, data files, mark
the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER
DATABASE... MODIFY FILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...[vbcol=seagreen]
> How can I pre-allocate storage. There is space on disk. I tried to create
a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
>|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:

> If you want to do in EM, right-click database, properties, data files, mar
k the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTE
R DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>|||I see. Can you post the TSQL command you tried and the exact error message r
eturned?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...[vbcol=seagreen]
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
>|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:

> I see. Can you post the TSQL command you tried and the exact error message
returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
>

Could not allocate space for object

There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
1" shows up.
I tried to create a new filegroup but the error message is the same. There
is disk space. Could someone help me ? Thanks !Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
noticed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> 1" shows up.
> I tried to create a new filegroup but the error message is the same. There
> is disk space. Could someone help me ? Thanks !|||How can I pre-allocate storage. There is space on disk. I tried to create a
new data with 1Mb and no success.
"Tibor Karaszi" wrote:
> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> noticed).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> > 1" shows up.
> > I tried to create a new filegroup but the error message is the same. There
> > is disk space. Could someone help me ? Thanks !
>|||If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> How can I pre-allocate storage. There is space on disk. I tried to create a
> new data with 1Mb and no success.
> "Tibor Karaszi" wrote:
>> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
>> noticed).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
>> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
>> > 1" shows up.
>> > I tried to create a new filegroup but the error message is the same. There
>> > is disk space. Could someone help me ? Thanks !
>>|||ok, but I don't want increase the size I want add a new data file. The
problem is add a new data file or filegroup. I tried do it on EM and TSQL,
but no success.
"Tibor Karaszi" wrote:
> If you want to do in EM, right-click database, properties, data files, mark the mdf file and enter a
> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY FILE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> > How can I pre-allocate storage. There is space on disk. I tried to create a
> > new data with 1Mb and no success.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> >> noticed).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> >> > 1" shows up.
> >> > I tried to create a new filegroup but the error message is the same. There
> >> > is disk space. Could someone help me ? Thanks !
> >>
> >>
>|||I see. Can you post the TSQL command you tried and the exact error message returned?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
> ok, but I don't want increase the size I want add a new data file. The
> problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> but no success.
> "Tibor Karaszi" wrote:
>> If you want to do in EM, right-click database, properties, data files, mark the mdf file and
>> enter a
>> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY
>> FILE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
>> > How can I pre-allocate storage. There is space on disk. I tried to create a
>> > new data with 1Mb and no success.
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
>> >> noticed).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
>> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
>> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
>> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
>> >> > 1" shows up.
>> >> > I tried to create a new filegroup but the error message is the same. There
>> >> > is disk space. Could someone help me ? Thanks !
>> >>
>> >>
>>|||Hi !
the command:
ALTER DATABASE NetSys
ADD FILEGROUP SECOND
GO
ALTER DATABASE NetSys
ADD FILE
( NAME = NetSys10,
FILENAME = 'e:\Data\NETSYS10.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
( NAME = NetSys08,
FILENAME = 'e:\Data\dbNETSYS08.NDF',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP SECOND
I tried today again and the command was successfull. thanks for you help.
"Tibor Karaszi" wrote:
> I see. Can you post the TSQL command you tried and the exact error message returned?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> news:C02C5DFA-32EA-4418-ACD1-361B7BDAF9B2@.microsoft.com...
> > ok, but I don't want increase the size I want add a new data file. The
> > problem is add a new data file or filegroup. I tried do it on EM and TSQL,
> > but no success.
> >
> > "Tibor Karaszi" wrote:
> >
> >> If you want to do in EM, right-click database, properties, data files, mark the mdf file and
> >> enter a
> >> bigger size for the file. If you want to use TSQL directly, check out ALTER DATABASE... MODIFY
> >> FILE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> news:A840434E-5867-4718-A89A-042427BB34F6@.microsoft.com...
> >> > How can I pre-allocate storage. There is space on disk. I tried to create a
> >> > new data with 1Mb and no success.
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Pre-allocate storage. Expand the size of the file. Don't rely on auto-grow (as you have just
> >> >> noticed).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Shima" <Shima@.discussions.microsoft.com> wrote in message
> >> >> news:D7E3B5B7-4EEF-4926-B101-6C40E0EDF713@.microsoft.com...
> >> >> > There is a Primary Filegroup with 5 datafiles and 2 logfiles. I'm trying to
> >> >> > add more datafiles, but the error "Server: Msg 1105, Level 17, State 2, Line
> >> >> > 1" shows up.
> >> >> > I tried to create a new filegroup but the error message is the same. There
> >> >> > is disk space. Could someone help me ? Thanks !
> >> >>
> >> >>
> >>
> >>
>

Could not allocate space for .. error

I am getting a "Could not allocate space for object 'temp_trc' in database
'Test' because the 'PRIMARY' filegroup is full"

The database test has unrestricted growth (All the defaults). It resides on
drive c which has 4Gigs free. I added new data and log files on drive d
which is about 30G free. I know that my insert doesn't take even 1G disk
space.

Why is the database complaining about a full filegroup when I just expanded
it?

J.John Dalberg (john_dd@.hotmail.com) writes:
> I am getting a "Could not allocate space for object 'temp_trc' in database
> 'Test' because the 'PRIMARY' filegroup is full"
> The database test has unrestricted growth (All the defaults). It resides
> on drive c which has 4Gigs free. I added new data and log files on drive
> d which is about 30G free. I know that my insert doesn't take even 1G
> disk space.

How big is the database now, and how much is set to grow at a time? The
default is 10%, so if the database is 43 GB, it cannot expand on C.

> Why is the database complaining about a full filegroup when I just
> expanded it?

What size did you make the data file on D? It may be that you need to
turn off auto-grow on the first file, so that SQL Server does not try
to expand that file first.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Could not allocate space because the 'DEFAULT' filegroup is full.

We are getting the error below when trying to attach a MS SQL 2005 Express
Database with a size of around 2GB.
The database had some issues before we detached the database and now we can
not attach.
There are a number of other databases in the primary filegroup but they are
all very small i.e. no more than 100Mb total in size.
Is this a configuration issue of some sort or a database corruption issue.
==================
TITLE: Microsoft SQL Server Management Studio Express
Attach database failed for Server 'ONLINE-INT'.
(Microsoft.SqlServer.Express.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
Could not allocate space for object 'events' in database 'port' because the
'DEFAULT' filegroup is full.
Error while undoing logged operation in database 'port'. Error at log record
ID (11807:19741:34).
Could not open new database 'port'. CREATE DATABASE is aborted.
1 transactions rolled forward in database 'port' (10). (Microsoft SQL
Server, Error: 1105)
PSernz
Hi
Do you have free space on the filesystem ?
"PSernz" <PSernz@.discussions.microsoft.com> wrote in message
news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
> We are getting the error below when trying to attach a MS SQL 2005 Express
> Database with a size of around 2GB.
> The database had some issues before we detached the database and now we
> can
> not attach.
> There are a number of other databases in the primary filegroup but they
> are
> all very small i.e. no more than 100Mb total in size.
> Is this a configuration issue of some sort or a database corruption issue.
>
> ==================
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Attach database failed for Server 'ONLINE-INT'.
> (Microsoft.SqlServer.Express.Smo)
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> Could not allocate space for object 'events' in database 'port' because
> the
> 'DEFAULT' filegroup is full.
> Error while undoing logged operation in database 'port'. Error at log
> record
> ID (11807:19741:34).
> Could not open new database 'port'. CREATE DATABASE is aborted.
> 1 transactions rolled forward in database 'port' (10). (Microsoft SQL
> Server, Error: 1105)
>
>
> PSernz
|||Yes I have 5Gb free on the disk that contains all Primary files.
PSernz
"Uri Dimant" wrote:

> Hi
> Do you have free space on the filesystem ?
>
>
> "PSernz" <PSernz@.discussions.microsoft.com> wrote in message
> news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
>
>
|||Hi
Do you have valid BACKUP of the database? Have you tried RESTORE?
"PSernz" <PSernz@.discussions.microsoft.com> wrote in message
news:0F6C0960-F410-46D5-87AE-8890D3163D55@.microsoft.com...[vbcol=seagreen]
> Yes I have 5Gb free on the disk that contains all Primary files.
>
> --
> PSernz
>
> "Uri Dimant" wrote:
|||Unfortunately not and hence we are hopeful that this is a config issue and
not a data integrity issue.
PSernz
"Uri Dimant" wrote:

> Hi
> Do you have valid BACKUP of the database? Have you tried RESTORE?
>
>
> "PSernz" <PSernz@.discussions.microsoft.com> wrote in message
> news:0F6C0960-F410-46D5-87AE-8890D3163D55@.microsoft.com...
>
>

Could not allocate space because the 'DEFAULT' filegroup is full.

We are getting the error below when trying to attach a MS SQL 2005 Express
Database with a size of around 2GB.
The database had some issues before we detached the database and now we can
not attach.
There are a number of other databases in the primary filegroup but they are
all very small i.e. no more than 100Mb total in size.
Is this a configuration issue of some sort or a database corruption issue.
==================
TITLE: Microsoft SQL Server Management Studio Express
--
Attach database failed for Server 'ONLINE-INT'.
(Microsoft.SqlServer.Express.Smo)
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
--
Could not allocate space for object 'events' in database 'port' because the
'DEFAULT' filegroup is full.
Error while undoing logged operation in database 'port'. Error at log record
ID (11807:19741:34).
Could not open new database 'port'. CREATE DATABASE is aborted.
1 transactions rolled forward in database 'port' (10). (Microsoft SQL
Server, Error: 1105)
PSernzHi
Do you have free space on the filesystem ?
"PSernz" <PSernz@.discussions.microsoft.com> wrote in message
news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
> We are getting the error below when trying to attach a MS SQL 2005 Express
> Database with a size of around 2GB.
> The database had some issues before we detached the database and now we
> can
> not attach.
> There are a number of other databases in the primary filegroup but they
> are
> all very small i.e. no more than 100Mb total in size.
> Is this a configuration issue of some sort or a database corruption issue.
>
> ==================> TITLE: Microsoft SQL Server Management Studio Express
> --
> Attach database failed for Server 'ONLINE-INT'.
> (Microsoft.SqlServer.Express.Smo)
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> Could not allocate space for object 'events' in database 'port' because
> the
> 'DEFAULT' filegroup is full.
> Error while undoing logged operation in database 'port'. Error at log
> record
> ID (11807:19741:34).
> Could not open new database 'port'. CREATE DATABASE is aborted.
> 1 transactions rolled forward in database 'port' (10). (Microsoft SQL
> Server, Error: 1105)
>
>
> PSernz|||Yes I have 5Gb free on the disk that contains all Primary files.
PSernz
"Uri Dimant" wrote:
> Hi
> Do you have free space on the filesystem ?
>
>
> "PSernz" <PSernz@.discussions.microsoft.com> wrote in message
> news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
> > We are getting the error below when trying to attach a MS SQL 2005 Express
> > Database with a size of around 2GB.
> >
> > The database had some issues before we detached the database and now we
> > can
> > not attach.
> >
> > There are a number of other databases in the primary filegroup but they
> > are
> > all very small i.e. no more than 100Mb total in size.
> >
> > Is this a configuration issue of some sort or a database corruption issue.
> >
> >
> > ==================> >
> > TITLE: Microsoft SQL Server Management Studio Express
> > --
> >
> > Attach database failed for Server 'ONLINE-INT'.
> > (Microsoft.SqlServer.Express.Smo)
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> >
> > (Microsoft.SqlServer.Express.ConnectionInfo)
> >
> > --
> >
> > Could not allocate space for object 'events' in database 'port' because
> > the
> > 'DEFAULT' filegroup is full.
> > Error while undoing logged operation in database 'port'. Error at log
> > record
> > ID (11807:19741:34).
> > Could not open new database 'port'. CREATE DATABASE is aborted.
> > 1 transactions rolled forward in database 'port' (10). (Microsoft SQL
> > Server, Error: 1105)
> >
> >
> >
> >
> > PSernz
>
>|||Hi
Do you have valid BACKUP of the database? Have you tried RESTORE?
"PSernz" <PSernz@.discussions.microsoft.com> wrote in message
news:0F6C0960-F410-46D5-87AE-8890D3163D55@.microsoft.com...
> Yes I have 5Gb free on the disk that contains all Primary files.
>
> --
> PSernz
>
> "Uri Dimant" wrote:
>> Hi
>> Do you have free space on the filesystem ?
>>
>>
>> "PSernz" <PSernz@.discussions.microsoft.com> wrote in message
>> news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
>> > We are getting the error below when trying to attach a MS SQL 2005
>> > Express
>> > Database with a size of around 2GB.
>> >
>> > The database had some issues before we detached the database and now we
>> > can
>> > not attach.
>> >
>> > There are a number of other databases in the primary filegroup but they
>> > are
>> > all very small i.e. no more than 100Mb total in size.
>> >
>> > Is this a configuration issue of some sort or a database corruption
>> > issue.
>> >
>> >
>> > ==================>> >
>> > TITLE: Microsoft SQL Server Management Studio Express
>> > --
>> >
>> > Attach database failed for Server 'ONLINE-INT'.
>> > (Microsoft.SqlServer.Express.Smo)
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > An exception occurred while executing a Transact-SQL statement or
>> > batch.
>> >
>> > (Microsoft.SqlServer.Express.ConnectionInfo)
>> >
>> > --
>> >
>> > Could not allocate space for object 'events' in database 'port' because
>> > the
>> > 'DEFAULT' filegroup is full.
>> > Error while undoing logged operation in database 'port'. Error at log
>> > record
>> > ID (11807:19741:34).
>> > Could not open new database 'port'. CREATE DATABASE is aborted.
>> > 1 transactions rolled forward in database 'port' (10). (Microsoft SQL
>> > Server, Error: 1105)
>> >
>> >
>> >
>> >
>> > PSernz
>>|||Unfortunately not and hence we are hopeful that this is a config issue and
not a data integrity issue.
--
PSernz
"Uri Dimant" wrote:
> Hi
> Do you have valid BACKUP of the database? Have you tried RESTORE?
>
>
> "PSernz" <PSernz@.discussions.microsoft.com> wrote in message
> news:0F6C0960-F410-46D5-87AE-8890D3163D55@.microsoft.com...
> > Yes I have 5Gb free on the disk that contains all Primary files.
> >
> >
> > --
> > PSernz
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> Do you have free space on the filesystem ?
> >>
> >>
> >>
> >>
> >> "PSernz" <PSernz@.discussions.microsoft.com> wrote in message
> >> news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
> >> > We are getting the error below when trying to attach a MS SQL 2005
> >> > Express
> >> > Database with a size of around 2GB.
> >> >
> >> > The database had some issues before we detached the database and now we
> >> > can
> >> > not attach.
> >> >
> >> > There are a number of other databases in the primary filegroup but they
> >> > are
> >> > all very small i.e. no more than 100Mb total in size.
> >> >
> >> > Is this a configuration issue of some sort or a database corruption
> >> > issue.
> >> >
> >> >
> >> > ==================> >> >
> >> > TITLE: Microsoft SQL Server Management Studio Express
> >> > --
> >> >
> >> > Attach database failed for Server 'ONLINE-INT'.
> >> > (Microsoft.SqlServer.Express.Smo)
> >> >
> >> > --
> >> > ADDITIONAL INFORMATION:
> >> >
> >> > An exception occurred while executing a Transact-SQL statement or
> >> > batch.
> >> >
> >> > (Microsoft.SqlServer.Express.ConnectionInfo)
> >> >
> >> > --
> >> >
> >> > Could not allocate space for object 'events' in database 'port' because
> >> > the
> >> > 'DEFAULT' filegroup is full.
> >> > Error while undoing logged operation in database 'port'. Error at log
> >> > record
> >> > ID (11807:19741:34).
> >> > Could not open new database 'port'. CREATE DATABASE is aborted.
> >> > 1 transactions rolled forward in database 'port' (10). (Microsoft SQL
> >> > Server, Error: 1105)
> >> >
> >> >
> >> >
> >> >
> >> > PSernz
> >>
> >>
> >>
>
>

Could not allocate space because the 'DEFAULT' filegroup is full.

We are getting the error below when trying to attach a MS SQL 2005 Express
Database with a size of around 2GB.
The database had some issues before we detached the database and now we can
not attach.
There are a number of other databases in the primary filegroup but they are
all very small i.e. no more than 100Mb total in size.
Is this a configuration issue of some sort or a database corruption issue.
==================
TITLE: Microsoft SQL Server Management Studio Express
--
Attach database failed for Server 'ONLINE-INT'.
(Microsoft.SqlServer.Express.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
Could not allocate space for object 'events' in database 'port' because the
'DEFAULT' filegroup is full.
Error while undoing logged operation in database 'port'. Error at log record
ID (11807:19741:34).
Could not open new database 'port'. CREATE DATABASE is aborted.
1 transactions rolled forward in database 'port' (10). (Microsoft SQL
Server, Error: 1105)
PSernzHi
Do you have free space on the filesystem ?
"PSernz" <PSernz@.discussions.microsoft.com> wrote in message
news:E2EAFD34-01F0-4887-A7D0-134D73EEC501@.microsoft.com...
> We are getting the error below when trying to attach a MS SQL 2005 Express
> Database with a size of around 2GB.
> The database had some issues before we detached the database and now we
> can
> not attach.
> There are a number of other databases in the primary filegroup but they
> are
> all very small i.e. no more than 100Mb total in size.
> Is this a configuration issue of some sort or a database corruption issue.
>
> ==================
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Attach database failed for Server 'ONLINE-INT'.
> (Microsoft.SqlServer.Express.Smo)
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> Could not allocate space for object 'events' in database 'port' because
> the
> 'DEFAULT' filegroup is full.
> Error while undoing logged operation in database 'port'. Error at log
> record
> ID (11807:19741:34).
> Could not open new database 'port'. CREATE DATABASE is aborted.
> 1 transactions rolled forward in database 'port' (10). (Microsoft SQL
> Server, Error: 1105)
>
>
> PSernz

Could not allocate new page for database TEMPDB?

"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

I get this error when running a query on another database. But why?

Both data and transaction files on TEMPDB are set to "automatically grow file" and "unrestricted file growth" and there is 70GB of free space on the disk drive. Shouldn't the files just grow? Why would this happen?because temporary table store in tempdb|||because temporary table store in tempdb

Thanks for the reply. I don't understand what you are saying but it sounds like you know what the issue is. Could you please elaborate a little bit?|||You say ：I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?|||You say ：I get this error when running a query
you are running a query , can you show me the query ?
do you create temporary table in your query?

No, I'm not explicitly doing anything with TEMPDB.

My query is very straight forward:

SELECT TOP 100 SampleDescriptor, COUNT(*) RepCount FROM DataSamples GROUP BY SampleDescriptor ORDER BY RepCount DESC

On my dev system, with 30 test records, this works fine.

On the production server with 188 million records (yes, a LOT of records!), this fails with the TEMPDB error that I mentioned.

I'm trying a simple: SELECT COUNT(*) FROM DataSamples now. It's been running for over 30 minutes and still going. I wonder if it will hit a similar error...|||When you get the error, have you checked the size of tempdb (both data and log) and free space of the hard drive?

I assume that tempdb is located in the hard drive with 70GB free space.|||Yes, of course. The size of both tempdb data and transaction files are 2MB and they are located on the drive with 68GB free. All drives on that system have plenty of space (although for tempdb, D: should be the only one that matters):

C: has 25 GB free
D: has 68 GB free
E: has 124 GB free|||What do sp_spaceused and sp_helpdb return?

BTW, which version and service pack of SQL Server?

Could not allocate new page for database 'TEMPDB'.

I recently had a routine using a table variable involved in a fairly
complex query using a number of fairly large tables which ended with
the following error.
Server: Msg 1101, Level 17, State 10, Line 40
Could not allocate new page for database 'TEMPDB'. There are no more
pages
available in filegroup DEFAULT. Space can be created by dropping
objects,
adding additional files, or allowing file growth.
The tempdb is on autogrow.
The drive on which the tempdb resided was filling up after running for
a number of minutes. The drive had a couple of GB of space left until
the routine started. I looked into it a bit and read that temp
tables were better performers with larger tables than table vars so
tried using a temp table instead. The routine finished in a timely
manner without expanding the tempdb significantly from it's previous
size before the routines initial run(rebuilt tempdb). Any idea why the
big difference in performance and effect on the tempdb? The rest of
the routine remainded the same with the exception of the table var -->
temp table.
ThanksUse of temp tables and table v ariables will affect the query plans used
in a procedure.
It is likely that the use of the table variables has resulted in a query
plan that involved heavy use of worktables i.e sorting and thus the filling
up of tempdb.
If you can post the query plans for the different code (and the code) it
migth help to diagnose the problem.

> I recently had a routine using a table variable involved in a fairly
> complex query using a number of fairly large tables which ended with
> the following error.
> Server: Msg 1101, Level 17, State 10, Line 40
> Could not allocate new page for database 'TEMPDB'. There are no more
> pages
> available in filegroup DEFAULT. Space can be created by dropping
> objects,
> adding additional files, or allowing file growth.
> The tempdb is on autogrow.
> The drive on which the tempdb resided was filling up after running for
> a number of minutes. The drive had a couple of GB of space left until
> the routine started. I looked into it a bit and read that temp
> tables were better performers with larger tables than table vars so
> tried using a temp table instead. The routine finished in a timely
> manner without expanding the tempdb significantly from it's previous
> size before the routines initial run(rebuilt tempdb). Any idea why
> the
> big difference in performance and effect on the tempdb? The rest of
> the routine remainded the same with the exception of the table var -->
> temp table.
> Thanks
>|||I took a look at the query plans, they are identical. Odd thing is
that this used to run on the same environment in a timely manner using
the table var a w ago. Finding out the truth here may be difficult
as our application is being hosted by a client and we do not have
security permission to view their sensitive data.
Thanks for the post!

Could not allocate new page for database TEMPDB.

I tried to create a new table in VIEW to accomplish some urgent work and I have received the following message:

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

It should be recognized that the MS SQL Server performance is slower than before. What files the TEMPDB contains, which need to be cleared from time to time, and how can I solve this above mentioned matter.
Thank you.Hi,

execute following statment;
use tempdb
go
sp_helpfile

If growth is set to 0 (Zero), your tempdb can't allocate more space. See books online to read how to change growth and how to allocate.

/Mada|||Thank you !!
I will try to use it and I will search for books online to have the solution.

Best regards.

Could not allocate new page for database TEMPDB

I get the following error when doing a variety of basic queries on other databases:

"Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth."

This doesn't make any sense since they are set to auto grow and there is plenty of disk space to do so.

Both data and transaction files of tempdb are set to:
"Automatically grow file" is checked
"Maximum file size" is set to "Unrestricted file growth"
Growth rate of 10%

Both tempdb data file and transaction file are on D: but all drives have ample space:
c: 25 GB free
D: 69 GB free
E: 175 GB free

sp_spaceused returns the following for tempdb:

name size unallocated space
tempdb 4.00 MB 1.45 MB

reserved data index_size unused
568 KB 176 KB 304 KB 88 KB

sp_helpdb returned for tempdb:

tempdb
4.00 MB sa 2 Apr 23 2004 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80

This is with SQL Server 2000 (Personal Edition)
Product Version: 8.00.760 (SP3)

Is this some strange limitation of Personal Edition? Is it worth upgrading to Standard Edition?doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.|||doesn't have anything to do with edition, it's your transaction. if it affects a lot of data, and in your case if work tables are needed to be created (this happens in tempdb) for sorting or grouping purposes, or if your indexes created with sort_in_tempdb, the data device will continue to grow for as much as it's needed based on your increment setting (default of 10% is not a good idea) once the limit is reached but the transaction has not completed, - it gets rolled back and the allocated size goes back to almost 0. if the "auto shrink" option is set, upon completion of rollback the allocated size goes down too. this explains why your sp_spaceused shows such a small number.

Bingo! The query actually caused tempdb to use the whole 27GB available on the C: drive, failed, shrunk to a few MB, and gave the illusion that space wasn't an issue. I moved tempdb to the E: drive, and it works perfectly.

Thanks!