August 22, 2009 at 5:38 pm
Hi,
We are using MS SQL 2005. In the Management Studio, I am trying to increase the size of a field in one table. I get the following message:
'tblCustInfo' table
- Unable to create index 'PK_tblCustInfo'.
Could not allocate a new page for database 'OurDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Could not create constraint. See previous errors.
The statement has been terminated.
Our hard drive has 73GB free.
Our current database file in only 340MB.
The file in the file group is set to unrestricted growth.
I've tried all the following:
Autogrowth by: 80%
Autogrowth by: 100MB
Autogrowth by: various smaller numbers
I've also tried adding a 2nd file in the file group and making that one Autogrowth by: 100MB and unrestricted growth.
None of these change the error message.
The change I am trying to make is to increase a varchar by only 20 characters.
Any ideas?
Thanks.
August 22, 2009 at 11:49 pm
can you post the output of
SELECT * FROM sys.database_files
so that we can all see the file settings for the database?
I've only ever seen that error message when the file is unable to grow to accommodate the change that is being made.
Steve
August 23, 2009 at 6:23 am
Check the autogrowth of your database files !
If it is in percentages, it would be better to switch it to extend in MB !
(e.g. per 100 or 500 MB, depending on your needes)
In many cases you receive this error if SQLServer has encountered a timeout during the OS-level file extention !
The OS will still extend the file, but just needs more time.
So, by now this file extend will have completed.
Same player, shoot again .... hit ball when lights are on 😉
ps also keep in mind you log file is always involved !
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 and code to get the best help
- 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
August 23, 2009 at 9:09 am
Do you have more than one filegroup? I've seen this before when someone has a second one and it is not set as the default.
August 23, 2009 at 9:40 am
good point, Steve.
Or more than one file in the filegroup. SQLServer is supposed to be able to balance the data over all files of a single filegroup, so in that case, it is the smallest file that will determine the maximum total size ....
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 and code to get the best help
- 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
August 23, 2009 at 1:19 pm
Thanks for all the replies.
Yes we have set autogrowth by MB and by very large amounts.
We have tried adding a 2nd file in the filegroup and set that to very large autogrowth. But we have removed that file since it didn't fix it.
We have also set the query timeout to a very large number.
Here is the output of sys.database_files:
1,NULL,0,ROWS,1,OurDB_Data,C:\Program Files\Microsoft SQL Server\MSSQL\Data\OurDB_Data.MDF,0,ONLINE,34912,-1,80,0,0,0,1,0,NULL,NULL,NULL,NULL,2951000000004700136,C2042E16-5B11-46B9-B10E-13BC6050490B,2009-08-23 06:00:10.663,NULL,NULL,NULL,NULL,1991000000428800011
2,NULL,1,LOG,0,OurDB_Log,C:\Program Files\Microsoft SQL Server\MSSQL\Data\OurDB_Log.LDF,0,ONLINE,128,-1,30,0,0,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
August 23, 2009 at 2:56 pm
If you try creating a new database with an initial mdf size of 512MB in the same C:\Program Files\Microsoft SQL Server\MSSQL\Data\ path, does that succeed?
From your output, it looks like it should work fine.
August 23, 2009 at 11:54 pm
helpmhost (8/23/2009)
Thanks for all the replies.Yes we have set autogrowth by MB and by very large amounts.
Lower the autogrowth number of MB !
Seems to me your OS is having troubles alocating the extend.
Did you check your C-drive for (disk level) file fragmentation ?
We have tried adding a 2nd file in the filegroup and set that to very large autogrowth. But we have removed that file since it didn't fix it.
If that was on the same physical disk, I wouldn't be surprised.
We have also set the query timeout to a very large number.
Here is the output of sys.database_files:
1,NULL,0,ROWS,1,OurDB_Data,C:\Program Files\Microsoft SQL Server\MSSQL\Data\OurDB_Data.MDF,0,ONLINE,34912,-1,80,0,0,0,1,0,NULL,NULL,NULL,NULL,2951000000004700136,C2042E16-5B11-46B9-B10E-13BC6050490B,2009-08-23 06:00:10.663,NULL,NULL,NULL,NULL,1991000000428800011
2,NULL,1,LOG,0,OurDB_Log,C:\Program Files\Microsoft SQL Server\MSSQL\Data\OurDB_Log.LDF,0,ONLINE,128,-1,30,0,0,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
Looks like your files are stil set to extend in percentages (80 % and 30%) !
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 and code to get the best help
- 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
August 24, 2009 at 12:14 am
Just a thought, I have had several cases where the OS reports xxGB of free space but there really isn't that much or it is confused, in those cases I do a check disk and select both checkboxes, the OS complains and says they are in use and would I like to do it at the next boot, I tell it yes and then do a restart. After that it usually works fine..
CEWII
August 24, 2009 at 10:37 am
I tried again today. We have the auto-shrink property set as well, so today the Initial size of the data file was significantly less. I also added a second data file again, but this time on a different partition. And it worked. Not sure which of those 2 changes fixed it.
After making the change, the Initial size of the original file went up 200 MB whereas the new one was still at 2 MB.
Anyways, thanks for the help.
Actually, another quick question, what happens if I now try to remove the 2nd data file? Is there risk of lost data?
August 24, 2009 at 1:47 pm
helpmhost (8/24/2009)
I tried again today. We have the auto-shrink property set as well, so today the Initial size of the data file was significantly less. I also added a second data file again, but this time on a different partition. And it worked. Not sure which of those 2 changes fixed it.After making the change, the Initial size of the original file went up 200 MB whereas the new one was still at 2 MB.
Anyways, thanks for the help.
Actually, another quick question, what happens if I now try to remove the 2nd data file? Is there risk of lost data?
If you want to remove the second data file, lookup SHRINKFILE in Books Online and focus on the EMPTYFILE option. Basically, you shrink the file and empty it which forces all data in that file to be moved to the other file - once that is done, you should then be able to remove the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 24, 2009 at 2:35 pm
helpmhost (8/24/2009)
I tried again today. We have the auto-shrink property set as well, so today the Initial size of the data file was significantly less. I also added a second data file again, but this time on a different partition. And it worked. Not sure which of those 2 changes fixed it.After making the change, the Initial size of the original file went up 200 MB whereas the new one was still at 2 MB.
Anyways, thanks for the help.
Actually, another quick question, what happens if I now try to remove the 2nd data file? Is there risk of lost data?
I'm glad you got it to work again 🙂
Keep in mind "auto shrink" should not be used on production database (because you have no control over "when" it will perform the shrink, and the shrink operation will cause (b)locking !)
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 and code to get the best help
- 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
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply