-1

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?

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
4
  • 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, 2010 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, 2010 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, 2010 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, 2010 at 14:46
0

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.

Improve this answer
1
  • 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, 2010 at 14:44

You must log in to answer this question.