Autogrow Timeout Expired

Question

Post reply

Autogrow Timeout Expired

SQLZ

SSChampion

Points: 12872
More actions
January 15, 2008 at 4:50 am

#127508

I got several timeout expired error messages related to the autogrow of a data file. I reduced the auto-grow increment and everything's fine. But I'm curious about a couple of things.
Firstly, the timeout values ranged from as little as 156 ms to as much as 30,000 ms. The auto-grow increment was 1.5 GB, which isn't a massive amount. If I manually increase the size of the data file by 2GB it takes around about 10-15 seconds.
Still, let's assume that the auto-grow of the data file took longer than 30 seconds. I can understand why I'd get the timeout errors. But why/how would I get timeout errors for timeout values of 150 ms?
Any ideas?
Here's a couple of the errors, just so you see what I'm talking about:
Autogrow of file ' ' was cancelled by user or timed out after 11922 milliseconds.
Autogrow of file ' ' was cancelled by user or timed out after 156 milliseconds.
Karl
source control for SQL Server

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Is this on SQL Server 2000 or 7? SQL Server 2005 should have instant file growth support.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 2

If I could make a suggestion... I think auto-grow is a bit overrated... sure, in 2k5, they made it almost instantaneous... but auto-growth frequently means that the DBA hasn't planned on what the database is expected to do over time. I think it's important for the DBA to have some idea of how much disk capacity will be needed over time because, face it, if you run out of disk space, you run out of the ability to auto-grow. The DBA has just gotta know these things and should grow the database on a planned basis rather than the apps doing it "by accident".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLZ SSChampion Points: 12872 More actions · Answer 3

This is on SQL Server 2005 by the way.

I agree with everything you're saying Jeff but at the same time, with all the best laid plans, sometimes things don't work out as you expected. So as much as auto-grow is overrated it does help you out of situations sometimes. A DBA can't know everything so has to allow for a little bit of lattitude for the unexpected.

As it turns out, there was over 89 GB of space on the hard disk so I wasn't in danger of running out of disk space. And I did have a max cap limit on the file growth (I'd never leave it on unlimited growth).

This happened last night and the person on call managed to sort it out. But I'm still curious as to the disparity in the timeout values.

Karl
source control for SQL Server

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 4

Heh... the DBA should know enough to keep from having to set auto-grow to 1.5GB 😉 DBA should setup an alert of sorts when the size of the database is within 10 or 20% of an auto-growth happening. Auto-growth is never convenient for the database.

The DBA should know everything about their systems... that's what they're getting paid for 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Instant file initialisation requires that the account that runs SQL has permission to do volume maintenance.

Check the local security policy for the server, find the item 'Perform Volume Maintenance tasks' and ensure that the SQL Service account is in that list.

Also, which OS are you running on?

Regarding auto-grow... If it happens at the wrong time (peak business) you can get a very nasty slowdown while the file grows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLZ SSChampion Points: 12872 More actions · Answer 6

GilaMonster (1/15/2008)
Instant file initialisation requires that the account that runs SQL has permission to do volume maintenance.
Check the local security policy for the server, find the item 'Perform Volume Maintenance tasks' and ensure that the SQL Service account is in that list.
Also, which OS are you running on?
Regarding auto-grow... If it happens at the wrong time (peak business) you can get a very nasty slowdown while the file grows.

Thanks Gail,

Instant file initialisation isn't enabled (or rather, the sql account doesn't have the permission). I'm toying with giving it that permission but I've reduce file growth to a tiny 200mb. I can't imagine that it would take long for the file to grow by 200mb if it had to.

Karl
source control for SQL Server

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

I'd say give it the permission. I haven't heard of a downside to it.

Instant initialisation also applies when backing up a database (I think), restoring a database and creating new databases.

And, of course, when you manually grow the database so that auto-grow won't need to run..

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

The only downside I've heard is something about a "security flaw" which when described seemed a bit far-fetched to me. Because the file doesn't reinitialize the data - it is possible for it to pick up the contents of some recently delete file, and somehow that content could be accessed (by detaching the file and using a text editor/hex editor against the MDF file)

from BOL:

Security Considerations
Because the deleted disk content is overwritten only as new data is written
to the files, the deleted content might be accessed by an unauthorized
principal. While the database file is attached to the instance of SQL
Server, this information disclosure threat is reduced by the discretionary
access control list (DACL) on the file. This DACL allows file access only to
the SQL Server service account and the local administrator. However, when
the file is detached, it may be accessed by a user or service that does not
have SE_MANAGE_VOLUME_NAME. A similar threat exists when the database is
backed up. The deleted content can become available to an unauthorized user
or service if the backup file is not protected with an appropriate DACL.

As I said - it's a stretch in my mind.

One caveat though - remember that Instant File Initialization only applies to the DATA files. Transaction logs MUST be zeroed out.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?