June 18, 2009 at 2:18 am
Hi,
For one of my databases, I am getting a message the following message:
Could not allocate space for object
But the datafile in the Primary file group is set to 'autogrowth' enabled and there is almost 60gigs free space in the drive. After getting this message, I changed the growth parms for that db file from 1000 MB to 10% growth. But that too did not work. Can someone please help?
Regards
Sandhya
June 18, 2009 at 3:41 am
I have had this once in SQL 2k.
I had to add a secondary Data File / transaction log File
Then setting the secondary Data file to primary.
hb
June 18, 2009 at 4:38 am
If the disk on which the data file resides is very busy, SQLserver may get a timeout on the extend operation.
It then raises this error.
In the background the OS is still performing the file extend. If there is space available, it will complete the extend, if not, if will not.
General attitude is to :
- "same player shoot again, hit ball when lights are on" 😉
- set autogrowth to MB in stead of percentages !
a file of 100MB growing 10% only needs 10MB
the next grow will be from 110MB and need 11MB, ...
Can you imagine the 10GB growth being completed in some seconds ?
So setting your filegrowth to e.g. 100MB most of the time will have the extend completed withing the timeout period.
Keep in mind that by default the data file will be zeroed out during this operation !
If you activated instant file initialization (EntEdtn) this zeroing out would not occur at extend time, but at page usage time.
More info on that can be fond in BOL http://msdn.microsoft.com/en-us/library/ms175935(SQL.90).aspx
or
http://www.sqlservercentral.com/scripts/T-SQL/67185/
or https://www.sqlservercentral.com/blogs/steve_jones/archive/2007/06/05/1769.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 19, 2009 at 6:11 am
It could be also that the drive containing your TempDB is full. That error comes up for me when that happens. TempDB in our place is on a different drive than the user db.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply