dbase Autogrow of file time out
-
Environment: sql2000 sp4 with EMC SAN
I have a large dbase 200 gig that twice now I receive the below error.
Autogrow of file 'xyz_DS17' in database 'xyz' cancelled or timed out after 30468 ms.
I have confirmed via Microsoft article it is a warning in sp4 and to increment the dbase in smaller chunks which I have done.
My concern is the file was 25 gig and set to grow at 3 gig and it failed.
I manually expanded it by 5 gig with no problem. Why ??
My boss does not buy it as acceptable so is there anyhting I am missing?
The EMC folks claims no maintenance was being done at that time.
Below is the main article I am referring to.
http://support.microsoft.com/default.aspx?scid=kb;en-us;822641
Any help would be appreciated
-
Did you check whether or not the database was set as autoshrink? If so, turn it off.
-
Auto Shrink is definately off.
-
the issue is the size of
memorydisk space the system is trying to grab in a single operation...30468 ms. is 30 seconds, right? .... i don't think the harddrive operation where it tries to find 3 gig of contiguousmemorydisk space can be completed in that time. remember a harddrive that is 80% empty will find 3 gig easy, if it's 80% full, it takes time arranging data to get it's 3 gig slice of disk space as mostly contiguous.I'd create a job that expanded the db if the free space got below, say 500 meg at night, instead of auto growing by such a large slice.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell by why memory here when this is a disk operation. Why does the operation need 3GB of memory to expand the disk space. I can't understand what you say. Clarify mee if i am wrong.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
"Autogrow of file 'xyz_DS17' in database 'xyz' cancelled or timed out after 30468 ms."
Please refere to "PRB: A Timeout Occurs When a Database Is Automatically Expanding" at http://support.microsoft.com/kb/305635/
When the client has set the timeout interval to 30 seconds for a SQL statement and the autogrow takes longer than 30 seconds, the client will cancel the SQL statement that caused the growth to occur, which cancels the autogrow.
"I manually expanded it by 5 gig with no problem."
How long did the expansion take ?
Was it more than 30 seconds ?
Did you perform the expansion during normal usage of the database or during a quiet period ?
SQL = Scarcely Qualifies as a Language
-
i guess I could have been more clear...3 gig of disk space whenever I mentioned memory.
I admit my servers and development machine are much slower than any SAN, but for me to expand a datafile by 3 gig, it takes much more than 30 seconds for the disk subsystem to allocate the 3 gig of disk space. that was the point i was trying to say. actual RAM doesn't have any real impact in the issue, didn't mean to imply otherwise.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the info from all. I think the 30 second timeout via the client is our issue and we will pursue that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply