Page allocationError

Hi all,
There a quite strange error i am getting :
"Could not allocate new page for database 'rough'. There are no more pages available in filegroup PRIMARY. Space can be created by dropping objects, adding additional files, or allowing file growth."

This database has unrestricted filegrowth, the current size of the datafile is 4.6GB and there is 9 GB space free on the hard disk.

The recovery model is full logged.
sql server 2k with sp3a.

any thoughts?

harshal.Run SP_SPACEUSED & SP_HELPDB for information on this database.|||Run SP_SPACEUSED & SP_HELPDB for information on this database.

what exactly should i be looking for?
It shows 4081.94 MB space used. And unallocated space is shown negative i.e. -9.74 MB.|||CHeck what is the space left on PRIMARY FILEGROUP.
Run SP_HELPFILE and use DBCC UPDATEUSAGE before running.|||Hi satya,
thanks for the reply.
after running dbcc updateusage, it updated the usage.
after running sp_helpfile it shows for primary filegroup size=4116480 KB
Max=5121024 KB
Growth=10%
when i try to increase the size of the primary datafile it again gives the same error.

thanks
harshal.|||Have you disabled auto grow on the primary filegroup?
Ensure there are no disk space issues where Primary filegroup is hosted.|||Have you disabled auto grow on the primary filegroup?
Ensure there are no disk space issues where Primary filegroup is hosted.
That is the whole problem auto grow is enabled and there is ample space on the drive where primary filegroup is hosted, still it is not allocating the space.|||See what happens when you manually expand the file by say 100 MB. You may be running into a timing conflict of some kind, with the growth set to 10%. At this point, the file will try to grow by almost half a gig. This will take some time. I would consider setting the filegrowth to a flat value. Probably 10 - 50 MB, depending on the growth rate of the database.|||See what happens when you manually expand the file by say 100 MB. You may be running into a timing conflict of some kind, with the growth set to 10%. At this point, the file will try to grow by almost half a gig. This will take some time. I would consider setting the filegrowth to a flat value. Probably 10 - 50 MB, depending on the growth rate of the database.

Ok I tried increasing the size of the database in units of 50 MB, it worked fine the first time when the size was 4095MB but after that if i try to increase it more even by 1 MB it gives me the same error.
What can possibly be the reason? can there be some kind of quota or some thing which may be configured?|||How much available disk space do you have on that drive?|||How much available disk space do you have on that drive?

free space on the drive is 9.04 GB.|||Hmm. Maybe there is an O/S limit on files? Never heard of such a thing, though. But, just to test out if it is the O/S playing with your head, try backing up the database a couple times to the same file. You should be able to create a file greater than 4GB in size. What O/S is this? 2K or 2K3?|||ok i'll try that.
its 2k server|||ok i'll try that.

But only on MONDAY :D

Time to call it a week !!! :rolleyes:|||Hmm. Maybe there is an O/S limit on files? Never heard of such a thing, though. But, just to test out if it is the O/S playing with your head, try backing up the database a couple times to the same file. You should be able to create a file greater than 4GB in size. What O/S is this? 2K or 2K3?Don't even go there! The SQL Server limits (http://msdn.microsoft.com/library/en-us/architec/8_ar_ts_8dbn.asp) aren't an issue here!

-PatP|||Don't even go there! The SQL Server limits (http://msdn.microsoft.com/library/en-us/architec/8_ar_ts_8dbn.asp) aren't an issue here!

-PatP

yeah thats right but then what is causing this?|||ok here is the error again:
"Error 5149: MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file."
it says that it is operating system error.
As if the os is not able to "SEE" the existing empty space.|||Harshal ... Yes ... Now that Mcrowley mentions it .. i remember reading about it somewhere...

I will bet a bottle of whiskey on your file system being FAT32. FAT32 file systems have a 4 GB file size limit ... you will need to convert your file system to NTFS in case you want a db file bigger than 4 GB.

Do tell if I am right ...|||http://support.microsoft.com/default.aspx?scid=kb;en-us;314463
Enigma: Looks like you are on to something there.|||http://www.microsoft.com/resources/documentation/Windows/XP/all/reskit/en-us/Default.asp?url=/resources/documentation/windows/xp/all/reskit/en-us/prkc_fil_tdrn.asp

take a look at this too|||http://sqljunkies.com/WebLog/enigma/archive/2004/05/14/2483.aspx|||Harshal ... Yes ... Now that Mcrowley mentions it .. i remember reading about it somewhere...

I will bet a bottle of whiskey on your file system being FAT32. FAT32 file systems have a 4 GB file size limit ... you will need to convert your file system to NTFS in case you want a db file bigger than 4 GB.

Do tell if I am right ...

Hey Enigma I owe you a Grand Single Malt on this.
Just now converted the file system to NTFS it was indeed fat32 and Bingooo !!|||Was this your prod system ?|||Was this your prod system ?
No Way!!
It is my personal workstation.|||Great zot man!

I'm certainly glad that you found your solution. I'm also deathly curious as to why on earth you'd run FAT32 on an NT workstation ?!?! We used to run FAT32 and/or unformatted volumes in the days of Win-NT 3.51, but the thought that you might be running FAT today never entered my mind!

-PatP|||Great zot man!

I'm certainly glad that you found your solution. I'm also deathly curious as to why on earth you'd run FAT32 on an NT workstation ?!?! We used to run FAT32 and/or unformatted volumes in the days of Win-NT 3.51, but the thought that you might be running FAT today never entered my mind!

-PatP
:D :D ;)
Actually a couple of weeks back my secondary hard disk had crashed and so the network admin gave me a spare disk, said that this is a temperory arrangement he would be giving me a new disk soon, but i never got that disk and i continued with what i had and never checked what the file system was.
:(