| Author |
Topic |
|
MediaPirate
Yak Posting Veteran
52 Posts |
 Posted - 2006-01-18 : 14:35:52
|
| I just had a very bad experience this morning. One of our production servers (SQL 2000) SP3 818 didn't autogrow the data file. We have the data and log set for autogrow 10%. The DATA file is 27GB and the disk drive has 130GB free. The odd thing is there's no errors in the logs about running out of space. The log file had plent of disk space allocated. Has anyone experienced this problem before? All other databases on the server were growing fine and didn't have any problems what so ever. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2006-01-18 : 14:58:24
|
| I would wager that the client process timed out waiting for the 2.7GB of space to be zeroed out. Once the client timed out, the auto-grow was rolled back. Try setting the auto-grow to a flat amount (say 50MB), rather than a percentage. |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-18 : 15:01:26
|
| Well, we have had issues relating to auto grow on a good size DB. I ran a myriad of tests and found out that the auto grow process kills other processes thus causing the problem. The easiest thing to do is give your DB a nice chunk of space and adjust the auto grow to something reasonable. Then, as time goes on, manually give the DB plenty of space. its easier to "defrag" etc.So give it a few gig, depending on growth expectations and then just keep tabs on it.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-18 : 17:09:07
|
quote:
Originally posted by Vivaldi
Well, we have had issues relating to auto grow on a good size DB. I ran a myriad of tests and found out that the auto grow process kills other processes thus causing the problem.
Auto-grow doesn't kill other processes. Do you mean that you're seeing queries timeout?Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-18 : 17:33:42
|
quote:
Originally posted by paulrandal
quote:
Originally posted by Vivaldi
Well, we have had issues relating to auto grow on a good size DB. I ran a myriad of tests and found out that the auto grow process kills other processes thus causing the problem.
Auto-grow doesn't kill other processes. Do you mean that you're seeing queries timeout?Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Well, I developed a program that inserted data until the auto grow was invoked, and by the tracing we could tell that when the auto grow was invoked, everything else went haywire. I then gave the database plenty of space and the tests ran fine. But when I turned on auto grow, and hit the grow point, the data would no longer insert. Of course, we were/are storing images in the database (not my choice) but that is the issue I experienced. I would love to hear how something else other than the auto grow caused it. ________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-18 : 18:03:16
|
| Vivaldi, the autogrow most certainly caused it, however it didn't kill the other processes. The queries would have been timing out, which means your application would have killed it. So to avoid this problem, you should perform the expansion during a maintenance window or have autogrow set to a smaller size so that it doesn't impact your queries for that long.Tara Kizeraka tduggan |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-18 : 18:11:19
|
| Exactly. What's your query timeout set to? Remember that autogrow of 10% will be zeroing out 2.7GB of disk space.There is an option in SQL Server 2005 to have autogrow finish instantly, with no zeroing. As Tara said, the best strategy is to manually manage your database file growth rather than letting a random query kick it off.ThanksPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-18 : 18:24:27
|
| Not sure on the Query Timeout (on Production). I just know that saving the images to the DB started failing and continued failing for several hours. We did some quick cleaning of the images in the DB and then it ran fine again. In attempting to recreate the problem. I was using the TextCopy.exe and running in loops inserting an image. Then utilizing a tracing tool that showed when the auto grow fired up, that the inserts started to timeout. I made sure there was adequate space on the SAN, but it "appeared" to be directly tied to the auto grow. Don't know if using the BLOBS is the issue with it or not.... We have since went to manually resizing (it wasn't my call in the first place to use auto grow). 2005 is definitely got some great improvements, I look forward to using it....Thanks.________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-18 : 18:48:21
|
| Using the blobs isn't the issue. The issue is with the autogrow running while other processes are trying to do work in the application. To avoid the issue without turning off autogrow or manually doing the expansion, you'd have to increase your timeout value in your application to a large number. Adequate space on the SAN just ensures that the autogrow will eventually complete. It's a slow process in 2000.Tara Kizeraka tduggan |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-18 : 19:18:35
|
quote:
Originally posted by tkizer
Using the blobs isn't the issue. The issue is with the autogrow running while other processes are trying to do work in the application. To avoid the issue without turning off autogrow or manually doing the expansion, you'd have to increase your timeout value in your application to a large number. Adequate space on the SAN just ensures that the autogrow will eventually complete. It's a slow process in 2000.Tara Kizeraka tduggan
Ok, our problem certainly didn't suggest otherwise. When the db is attempting to do a (35 gig db)3.5 gig growth, I know everything else would have to wait. But the strange part was the fact that little things still worked. Just the Files Table on the IMAGES filegroup was dying. The application could still do basic CRUD ops, but saving a file wouldn't work. In other words, when we had the problem, everything pointed to the blobs and the auto grow. But clearly setting a huge timeout would have helped. I read the original post that auto grow doesn't "kill" other processes on the database, and my point was that it certainly was (not in the technical kill, but the users see it as a process is killed cause it wouldn't work.)________________________________________________The only cure for thinking is picking rocks, or drinking beer. |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-01-18 : 19:49:37
|
| Anything that needs to allocate space from the file being grown will wait - anything else is free to continue.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-01-18 : 19:57:29
|
quote:
Originally posted by paulrandal
Anything that needs to allocate space from the file being grown will wait - anything else is free to continue.Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Good to know. What I wonder is why the application basically would not allow the files to be saved (over several hours). But once we did a quick delete of some of the files, the inserts started working again.Thanks for the insight. Haven't seen this info in any of the books..:) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-19 : 02:26:55
|
| "I would wager ..."found out that the auto grow process kills other processes ...This has been my experience too. Database extension causes delays to the applications which, in our case (web site with lots of concurrent users) caused timeouts and mayhem!On a fairly basic 2CPU server which also has the web server on it (I know, I know!!) a 500MB file extension would take about 30 MINUTES to calm down.Since we changed autogrow to use a fixed, more modest, size its been fine.Kristen |
|
|
|
|
Autogrow failed