please help: 'PRIMARY' filegroup is full

Hi, all,
I got the error message 'Could not allocate space for
object 'B_Table' in database 'UDB' because the 'PRIMARY'
filegroup is full..' and I check the error log file there
is error message like this 'C:\MSSQL7\data\UDB_Data.MDF:
Operating system error 112(There is not enough space on
the disk.) But the truth is there still is 44G free space.
Both the data and log files are setting auto grow 10%.
why I get this kind of error? how can i fix it?
Thanks!
HawkHello Hawk!
1.Could it be that there are quotas on the disk ? Therefore, remove qutoas
for the SystemAccount you are starting SQL Server with. (What does the event
log say ?)
(2.If not, try to resize the data file via QA and scrpt code using ALTER
Database.
3.If This doesn´t do the right thing, add another data file to the primary
file group and try to get around. (but remeber, this is only a woraround,
not the best idea, it is only to test the case that the file could not be
resized))
Jens Süßmeyer.
"hawk" <abchawk@.hotmail.com> schrieb im Newsbeitrag
news:01c401c36671$a339fd10$a301280a@.phx.gbl...
> Hi, all,
> I got the error message 'Could not allocate space for
> object 'B_Table' in database 'UDB' because the 'PRIMARY'
> filegroup is full..' and I check the error log file there
> is error message like this 'C:\MSSQL7\data\UDB_Data.MDF:
> Operating system error 112(There is not enough space on
> the disk.) But the truth is there still is 44G free space.
> Both the data and log files are setting auto grow 10%.
> why I get this kind of error? how can i fix it?
> Thanks!
> Hawk|||To make it bigger, use
ALTER DATABASE xxx
MODIFY FILE...
Info in Books Online.
Or use EM, Properties for the DB and specify a bigger size for the file.
To shrink, yes, use DBCC SHRINKFILE. I do not recommend regular shrinking. That just mean that the
db jumps up and down in size which is a costly operation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hawk" <abchawk@.hotmail.com> wrote in message news:0e9501c36722$2c3362e0$a301280a@.phx.gbl...
> Hi, Dan, Thank you very much.
> If my Data File size is 4.2GB right now and I get the
> primary filegroup is full. then I should resize the data
> file bigger, for example resize to 6.0GB or 7.0GB,right?
> Could I use DBCC DHRINKFILE to shrink the data file?
> I don't know what should I do to down size the data file
> or resize it bigger.
> Thank you very much.
> Have a good one
>
>

Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anything
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it.
Thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200609/1
>
|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:[vbcol=seagreen]
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>[quoted text clipped - 26 lines]
Message posted via http://www.droptable.com
|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even more specific, it will
move pages towards the beginning of the files, page-by-page, possibly resulting in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous index before the
shrink). This is easy to test...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via droptable.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with what
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>

Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anythin
g
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it
.
Thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your syste
m
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:[vbcol=seagreen]
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>[quoted text clipped - 26 lines]
Message posted via http://www.droptable.com|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even mo
re specific, it will
move pages towards the beginning of the files, page-by-page, possibly result
ing in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous
index before the
shrink). This is easy to test...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via droptable.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...agreen">
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your sys
tem
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with wh
at
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks
!
> Andrew J. Kelly wrote:
> --
> Message posted via http://www.droptable.com
>

Please help, database will not truncate free space at end of file

I'll explain the process, then I'll explain the problem.
SQL Server Enterprise Edition, Windows NT, SP2
We have a main database server A, and a report server B.
This is all done through Enterprise Manager:
We reindex database A, which causes it to grow in size, backup the
transaction log, shrink the database, truncate free space at the end of the
file, back it up, then restore it to the report server. We HAVE to shrink it
because there's little room left on server B for the database.
We did this same process recently, however the free space at the end of the
database file will not truncate. It did before, but not now.
I've read some of the articles posted, and I'm shy about trying any of them
since this is main production and would be bad, to say the least, if anything
should happen. One article I read said that I should do this in QA with a
TRUNCATEONLY switch, will the data be affected?
If any of you have a simple explanation or help I would really appreciate it.
Thanks in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1It sounds like you have an open transaction in the database. What does DBCC
OPENTRAN() say for that db? The process you are going thru is very flawed
in that the shrink process undoes most of what you tried to accomplish with
the reindexing in the first place. The proper answer is get more disk space
on the reporting server and you can alleviate that whole process and have a
much better operation overall.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Andrew J. Kelly SQL MVP
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:6662f70524ce3@.uwe...
> I'll explain the process, then I'll explain the problem.
> SQL Server Enterprise Edition, Windows NT, SP2
> We have a main database server A, and a report server B.
> This is all done through Enterprise Manager:
> We reindex database A, which causes it to grow in size, backup the
> transaction log, shrink the database, truncate free space at the end of
> the
> file, back it up, then restore it to the report server. We HAVE to shrink
> it
> because there's little room left on server B for the database.
> We did this same process recently, however the free space at the end of
> the
> database file will not truncate. It did before, but not now.
> I've read some of the articles posted, and I'm shy about trying any of
> them
> since this is main production and would be bad, to say the least, if
> anything
> should happen. One article I read said that I should do this in QA with a
> TRUNCATEONLY switch, will the data be affected?
> If any of you have a simple explanation or help I would really appreciate
> it.
> Thanks in advance.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
>|||I ran opentran and this is the message:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Is it true that shrinking the database fragments it? Which ties in with what
you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
Andrew J. Kelly wrote:
>It sounds like you have an open transaction in the database. What does DBCC
>OPENTRAN() say for that db? The process you are going thru is very flawed
>in that the shrink process undoes most of what you tried to accomplish with
>the reindexing in the first place. The proper answer is get more disk space
>on the reporting server and you can alleviate that whole process and have a
>much better operation overall.
>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> I'll explain the process, then I'll explain the problem.
>[quoted text clipped - 26 lines]
>> Thanks in advance.
--
Message posted via http://www.sqlmonster.com|||> Is it true that shrinking the database fragments it?
Yep. Or, to be more specific, it will fragment the indexes. Or to be even more specific, it will
move pages towards the beginning of the files, page-by-page, possibly resulting in an index which is
more fragmented than it was before the shrink (assuming you had a contiguous index before the
shrink). This is easy to test...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message news:666545dd8a649@.uwe...
>I ran opentran and this is the message:
> No active open transactions.
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> Is it true that shrinking the database fragments it? Which ties in with what
> you said in your post. Yeah, bottom line, disk space is the answer. Thanks!
> Andrew J. Kelly wrote:
>>It sounds like you have an open transaction in the database. What does DBCC
>>OPENTRAN() say for that db? The process you are going thru is very flawed
>>in that the shrink process undoes most of what you tried to accomplish with
>>the reindexing in the first place. The proper answer is get more disk space
>>on the reporting server and you can alleviate that whole process and have a
>>much better operation overall.
>>http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> I'll explain the process, then I'll explain the problem.
>>[quoted text clipped - 26 lines]
>> Thanks in advance.
> --
> Message posted via http://www.sqlmonster.com
>