Another index rebuild issue?

Question

Another index rebuild issue?

Martin Clapp

SSC Journeyman

Points: 91
More actions
August 9, 2005 at 3:32 pm

#165574

I'm very new to SQL so I might not be giving enough pertinent info here. Anyway, running Standard Edition of SQL Server I keep getting an 1105 error during the index rebuild, which is run through the maintenance plan. This is similar to another thread here, but the error is different so it might be a different issue. Here are the highlights:
1. The Error 1105 occurs after the same table rebuild each night and has never NOT occured since I installed and began using this instance of SQL. By the way, I'm only using and doing maintenance on a single database (ie. 'MYDATABASE')
2. The full error is Error 1105: Could not allocate space for object '(SYSTEM table id: -XXXXXXXXX)' in database 'MYDATABASE' because the 'PRIMARY' filegroup is full.
3. I am definitely running the Standard Edition, not merely the desktop version.
4. I have already deleted and rebuilt the maintenance plan.
5. If you tell me to do this with scripts instead, please point to some instructions.
Thanks for any help!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Martin,

It sounds as thought the primary datafile your 'MyDATABASE' has a finite size which has been reached therfore not allowing the datafile to grow which it needs to do inorder to rebuild the index. Have a look at the properties of the database in EM and make sure that the primary file is set to auto grow and that there is enought space on the drive for it. You might want to increase the size of the file as well to give it some free space.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

Depending on how you do it, and index rebuild can require empty space in the DB equal in size to the largest table it contains.

You might also want to keep this in mind when looking at capacity planning.

Martin Clapp SSC Journeyman Points: 91 More actions · Answer 3

Thanks for the replies!

I have the database file property settings such that the file should automatically grow by 10% with the maximum file size unrestricted.

However, I do seem to remember that these might not have always been the settings. In fact, looking at the 'Data Files' tab of the 'MYDATABASE' Properties in EM, there is a greyed out 'Restrict file growth(MB)' value of 3979 and the 'Space allocated(MB)' value of the database file is 3978. Maybe the 'Unrestricted file growth' setting never took???

I guess maybe I'll try re-instating the 'Restrict file growth' setting and change the value from 3979 to 9999???

Does this seem to make sense, or can anyone think of anything else to try?

Thanks!

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 4

This approach sounds ok but will have the added side effect of introducing OS file fragmentation, which is something else that you'll have to worry about.

If it were me i'd look at how much the DB has grown over the last 6 months to a year and expand the .mdf file by at least that. Hopefully you know the data and have an idea of how fast it's growing so this shouldn't be too difficult.

Mike

Martin Clapp SSC Journeyman Points: 91 More actions · Answer 5

When you say 'expand the .mdf file' do you mean changing the 'Restrict file growth' setting or is there a manual way to 'force' expansion of the file?

Thanks

Jules Bui SSC Eights! Points: 857 More actions · Answer 6

Martin,

To think further ahead, you might want to consider creating an index filegroup totally separate from the primary filegroup and move your indexes there because it really isn't a good idea to mix your indexes and data with the primary filegroup where all your system files are. Give your indexes filegroup plenty of space to grow if you have the space.

Jules Bui
IT Operations DBA
Backup and Restore Administrator