May 4, 2004 at 6:06 pm
I am running into a special problem on SQL server 2000 under windows 2K server.
I have job in which last step rebuilds the indexes for all the base tables.
This job fails once or twice in a month. It does not show much information in job history except step failed but when I checked sql server log i found following error:
Error: 1105, Severity: 17, State: 2
and
Could not allocate space for object '(SYSTEM table id: -959080535)' in database 'MyCompanyDB' because the 'PRIMARY' filegroup is full..
I current database file size is 2932MB and database has 737MB available space. The HDD has about 10GB free disk space
To fix this error I am trying to change data file size from 2932MB to 3932MB in-case re-indexing needs more space.
When I change file size it stays there for some time but after few minutes it reverts back to old setting.
The database is set to autoshrink and autogrow. Is this the reason?
In other database which has similar settings on different server I am able to change the file size and it stays there.
Any help or clue will be good.
May 4, 2004 at 7:32 pm
Autoshrink is the reason that your database size keeps reverting back to the old settings.
Personally, I would not set this option on a production server. It is a better option to determine the appropriate size for the database and set the size to that. Otherwise, whenever the database needs to grow, your users, will have to wait. With autodhrink and autogrow turned on, the database will increase in size and then a short while later, it will most likely release most of the space that it just grew by. This will happen quite often and will have an impact on the performance of your application.
May 5, 2004 at 9:56 am
I had a similiar problem, but will agree with Happycat, NEVER use AutoShrink.
I do use autogrow, but usually by an amount, not %. I'd rather wait for a big grow less often.
My problem was actually related to the partition table. We had moved 2 mirrored drives from another server. I dutifully reformatted them, full and full scan disk. But after a couple of grows and shrinks I began getting similiar errors.
Moved everything off the drive, repartitioned, reformatted, and so far no problems.
What convinced me was after moving everything off the drive, I figured I back up the DB to the drive, then move it off to tape before repartitoning. The backup crashed with an IO error, but the RAID card should no physical errors. So I decided it was logical, not physical.
KlK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply