SQL Server Performance Forum – Threads Archive
Could not allocate space for object ‘XXX’ in datab
The following are the details of the database. SQL Server 7(SP4)Database has three datafiles in the primary file group(XX_01Data, XX_02Data & XX_03Data).
each datafile allotted space is 6144MB and in all three files free space is 2433MB.
Logfile allotted space is 2233MB.(used space is 2%)
AutoGrow option is disabled for datafiles and enabled for Logfile.
Auto shrink is disable, update statistics is enabled. Problem: The Job for DBReindex for all indexes(for this we created a SP) in the database is getting failed and the error we got from SQL Server Log is “Could not allocate space for object ‘XXX’ in database ‘YYY’ because the ‘PRIMARY’ filegroup is full.†Even though the datafiles is having 2433MB of free space we are getting this error so we enabled the autogrow option and executed the job again and the job executed successfully
but the size of the first datafile in the primary group has increased with 700MB, now the free space in XX_01Data datafile has 3GB and other two is having same 2.4GB free space
Any body please help me why this is happening even it has a large amount of free space. Shyam Sunder Gajendula SQLServer DBA Wissen Infotech Pvt. Ltd. T +91 40 55313031 / 27892897 M +91 9848496693 E [email protected] www.wisseninfotech.com
Hi,<br />What is your recovery model ? <br />are you doing it as a part of maintanance plan ?<br /><br />if you are re-indexing it will increase <br />please refer topics / thread that are already covered in this forum as similar to yours ,<br />here are some FYI :<br /><br /><a href=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11528′ target=’_blank’ title=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11528′<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11528>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11528</a></a><br /><br /><a href=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11417′ target=’_blank’ title=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11417′<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11417>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11417</a></a><br /><br /><a href=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10069′ target=’_blank’ title=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10069′<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10069>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10069</a></a><br /><br /><a href=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10381′ target=’_blank’ title=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10381′<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10381>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10381</a></a><br /><br /><a href=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9235′ target=’_blank’ title=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9235′<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9235>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9235</a></a><br /><br />And tips for rebuiling and index tunning :<br /><br /><a href=’http://www.sql-server-performance.com/rebuilding_indexes.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/rebuilding_indexes.asp'<a target="_blank" href=http://www.sql-server-performance.com/rebuilding_indexes.asp>http://www.sql-server-performance.com/rebuilding_indexes.asp</a></a><br /><br /><a href=’http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx’ target=’_blank’ title=’http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx'<a target="_blank" href=http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx>http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx</a></a><br /><br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />HTH<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br />
This is quite normal. You should allow for about 1.5 times (sometims a bit less) the amount of space in your tables for index rebuilds. SQL Server was unable to complete the index rebuild the first time because it had started to use the free space on the first data file but was unable to grow that first data file and unable to continue the growth onto the second data files. Karl Grambow www.sqldbcontrol.com
]]>