Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I just ran into an SQL Server database (2005 or 2008, I'm not sure) which had autogrowth set to "By 10 percent, unrestricted growth" on the primary data file. It had no free space, and inserts were failing because of lack of free space.

The drive had over 100 GB free, and the data file was about 15 GB. I wish I had kept the error message, but it was something about "insufficient free space on PRIMARY". It was not a timeout exception like I've seen on a few occasions with 2000, where it could take long periods of time to resize.

I increased the "Initial Size" setting by about 5 GB, and it completed instantly, and everything started working again after that.

Why does SQL Server sometimes not autogrow when it should?

share|improve this question

2 Answers 2

How did you verify and change the file size and auto-growth? Via T-SQL, or via the Management Studio? SSMS had a number of bugs on which it displayed and set erroneous auto-growth rates on data files, see Attaching a database increases autogrowth % to 12800.

share|improve this answer
    
It's the value displayed in management studio. I'm trying to get results from sp_helpdb, and see if they're different. –  user59599 Nov 10 '10 at 14:45
    
BTW, You see immediate failure and immediate growth because the system has Instant File Initialization enabled, see msdn.microsoft.com/en-us/library/ms175935.aspx –  Remus Rusanu Nov 10 '10 at 15:07
    
To view current size and growth, check sys.database_files: technet.microsoft.com/en-us/library/ms174397.aspx –  Remus Rusanu Nov 10 '10 at 15:09
    
I finally got the results, and they match what's shown in Management Studio. Max size -1, growth 10, is_percent_growth 1. –  user59599 Nov 11 '10 at 14:46

Try setting the auto grow option to a smaller value: 300 - 500 MB. Allocation of 1,5 GB (10% out of 15 GB data file) could have caused the insert queries to timeout waiting for the space to be allocated. What was the timeout set for? The default value in SQL is 600 sec but if the queries where run from a third-party application the timeout can be a lot smaller.

share|improve this answer
    
I specifically pointed out that it's not a timeout. I've seen situations where it times out on the resize, and in those situations, doing a resize manually takes 5-10 minutes, and all inserts result in a timeout. In this situation, the commands fail instantly with no free space, the manual resize completes instantly, and the inserts immediately start working after the resize. –  user59599 Nov 10 '10 at 14:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.