January 10, 2012 at 8:38 am
I had a database that would not auto grow. It was setup to auto grow by 10%, which for this database was 450Mb. I have 200 Gb of space on the drive, so disk space is not an issue. This problem start right after the first of the year. The database was manually grown by 100 Mb to get past the problem, for the time being. This will last about 2 months, assuming normal growth. It seems to me that 450Mb is not alot of disk space to grow the database, but the server made several attempts to do so and failed. Would an initial failure for some reason (i.e. high CPU or memory usage) cause subsequent attempts to fail as well?
I have reviewed a few of the posts on this site and understand that the goal should not be to use auto grow as the means to expand the size of the database. However, I am concerned that the next time that the database needs to auto grow and the space isn't there that auto grow will again fail.
This is on SQL 2000. We are upgrading to 2005, but this particular database is not slated to be upgraded until 2-3 quater this year. Of course, we could manually grow to the expected size and leave it at that, but I am a curious sort and want to know why this happened.
January 10, 2012 at 8:45 am
Have you checked the SQL errorlog and the Windows evenlog for error messages?
John
January 10, 2012 at 8:47 am
Please verify Autoshrink settings as well.
Considerations for the "autogrow" and "autoshrink" settings in SQL Server
January 10, 2012 at 8:49 am
execute sp_spaceused for the database in question and look to see whether or not the allocated space shows as a negative number. This can prevent the database from allocating additional space. If the value is negative, run DBCC UPDATEUSAGE.
January 10, 2012 at 9:29 am
This is the error message from the event log:
5144, Autogrow of file 'VBBridge_Data' in database 'VBBridge' cancelled or timed out after 40000 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
There are quite a few of these, so it tried several times to execute the grow and failed.
January 10, 2012 at 9:34 am
Thanks! I have reviewed these and feel comfortable with how to proceeed. I really am looking for why the auto gorw failed since the grow size was small.
January 10, 2012 at 9:37 am
This is an interesting fact. I cannot check it now without recovering to a second server, which I don't currently have available (all my extra servers are now dedicated to the upgrade to 2005). I will definitely put this in my support documentation to check, if this happens again.
January 10, 2012 at 10:35 am
As Richard mentioned, check sp_spaceused and perhaps manually run dbcc updateusage.
Also, don't depend on autogrow. Monitor and grow the db as needed, in large chunks that will get you through 3-4 months at a time.
January 10, 2012 at 11:33 am
I wonder if the timeouts are because the service account doesn't have Perform Maintenance Tasks rights.
January 10, 2012 at 11:41 am
I suspect the timeouts are due to the fact that unallocated space is a negative number which is preventing the database from growing at all. There was / is a bug in SQL 2000 which causes this issue and can only be corrected by running updateusage or sp_spaceused @updateusage. This is only a temporary fix, but seems to be somewhat corrected by changing autogrow to a fixed value instead of a percentage.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply