SQL Server Performance Forum – Threads Archive

Index rebuid fails- Filegp full

Index re-buid job fails with following error : [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object ‘outbound_transaction_fact’ in database ‘ViaView’ because the ‘DATA2’ filegroup is full. following are the statistics . DATA2 file group :
*******************
data3.MDF 17GB — used 11.8GB
data4.MDF 16GB — used 5GB i found enough space on both file, does it mean rebuit takes more space than anyother activity? Any remedy to this situation ? thanks in advance . rajiv

it seems that actual used space is less then shown means data3.mdf use only 11.8 gb and occupied size is 17 gb and same as for data4.mdf … i think you need to shrink the files and would you mind if i ask how many space remain on hdd where data2 file group resides !
Regards
Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

I dont want the file to grow automaticaly hence provided max size to files but still its not satisfyin the need of index re-buid . In my opintion SHRINKing is not a solution here . asn to ur second question . DATA2 file group <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />datafiles in two diff drives L and M) ******************* data3.MDF Drive L: freespace 10 MB data4.MDF Drive M: freespace 100 MB Both drives are dedicated to these files .I am thinking whether i shud add one more file to SAME group,(on another drive,N

.What you all say about this ? rajiv

What is the size of ‘outbound_transaction_fact’ table? Are you using DBCC DBREINDEX (outbound_transaction_fact)
— while dbreindex a object require equivalent empty size for rebuild.
For a quick solution you may try with adding new file located on another drive with free space. Deepak Kumar –An eye for an eye and everyone shall be blind

I am not sure what statement it uses to index-rebuid as its just a maintanance plan job.when i checked it has the following statement in that . EXECUTE master.dbo.xp_sqlmaint N’-PlanID 4EA6869C-F64E-4388-AE07-3EC6B61ECC86 -Rpt "N:MSSQLBackuplogsViaView_daily_maintenance0.txt" -DelTxtRpt 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 ‘ size of that table …
exec sp_spaceused ‘outbound_transaction_fact’
************************
ROWS
63971812
RESERVED
21702480 KB
DATA
17999096 KB
INDEX SIZE
3701816 KB
UNUSED
1568 KB

Any hlp ???

Same issue
http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1058

That post didnt solve my issue .It would be helpful to get some ans , reffering to my table size.shoudl i go for adding one more fileto thefile gropup

It seems the table is huge and can you confirm how many indexes are defined on that table, how often they are used. For instance there are six indexes on a table and only 2 are used frequently means you’re wasting SQL resources by allowing to reindex all the ones, that will contribute to space increase in the data file. For ease of operation you can disable maintenance plan to reindex and perform a seperate job to take care of those indexes. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hi, if you have to re-index it will need more space to re – built, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object ‘outbound_transaction_fact’ in database ‘ViaView’ because the ‘DATA2′ filegroup is full. following are the statistics . DATA2 file group : ******************* data3.MDF 17GB — used 11.8GB data4.MDF 16GB — used 5GB<hr height="1" noshade id="quote"></blockquote id="quote"> and as your another post described that you have only 10 MB and 100 MB left in your L and M drive respectly and as i suggest you earlier i think you need to free some space rather inserting new file – creating a new file would be temporary solution and its not good practice – i again suggest you to free up some space . BTW is this drive formatted with NTFS file system ?! <blockquote id="quote">quote:<hr height="1" noshade id="quote"> DATA2 file group <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />datafiles in two diff drives L and M) ******************* data3.MDF Drive L: freespace 10 MB data4.MDF Drive M: freespace 100 MB Both drives are dedicated to these files .I am thinking whether i shud add one more file to SAME group,(on another drive,N

.What you all say about this ? rajiv <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

Rajiv
Any joy on what referred above. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>