<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
db autogrowth problems
13 posts, Page 1 of 2
1
2
»»
db autogrowth problems
Rate Topic
Display Mode
Topic Options
Author
Message
jayoub1
jayoub1
Posted Wednesday, January 09, 2013 12:38 PM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 8:43 AM
Points: 56,
Visits: 156
On two occasions we have had to change or autogrowth setting for our database files from 5 or 6 % to 500MB before transactions began processing again.
Specs:
Windows Server 2003 SP2
SQL Server Standard x64 2005
Database sizes 188GB
The other day our application/database began timing out and throwing errors. We looked at the properties of the database and found the data file growth Autogrowth settings set to 6% unrestricted. We changed the rate to 5% and still the database was not responding. We changed the rate to 500MB unrestricted and the database came back on line.
Do you have any ideas why we would have to resort to setting the file size this way.
Please let me know and any help is appreciated
Jeff
Post #1404951
MyDoggieJessie
MyDoggieJessie
Posted Wednesday, January 09, 2013 1:40 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 2,063,
Visits: 3,787
I wouldn't recommend using percentages for auto growth options on such large DBs. For instance if you have a DB that's 100GB, has autogrowth set to 5%, and the time comes when it needs to grow - that's a 5GB growth that needs to occur for the data file. Depending on your disk subsystem and whether or not you're using Instant File Initialization, it may take quote some time to grow your file (which could temporarily cause your entire DB to be unresponsive - as file growth is a very IO intensive operation)
It sounds like the percentage you had it set to resulted in a large growth rate, which honestly takes time to complete. When you changed it to a mere 500MB, the growth was pretty much instantaneous resulting in your DB coming back online
IMHO you should size your DBs large enough to AVOID growth (this operation should only occur RARELY). This will reduce the growth and will save you much time and effort
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1404990
jayoub1
jayoub1
Posted Wednesday, January 09, 2013 2:37 PM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 8:43 AM
Points: 56,
Visits: 156
Thank you very much and I will look into sizing the database.
Jeff
Post #1405020
durai nagarajan
durai nagarajan
Posted Friday, January 11, 2013 11:16 AM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 863,
Visits: 2,135
how frequently the DB grows with 500MB growth settings
take this into consideration while setting auto growth option.
Regards
Durai Nagarajan
Post #1406202
jayoub1
jayoub1
Posted Monday, January 14, 2013 8:37 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 8:43 AM
Points: 56,
Visits: 156
Very frequently so I have to increase the size but I do not know by how much at this point. I am trying to monitor my database sizes manually by running sp_databases and recording the sizes on different days and can come up with a growth rate.
I am noticing something strange. I have one database that is 48.3 MB and the mdf file is 9 MB and not sure why this would be. Do you have any idea.
Your help is appricated
Jeff
Jeff
Post #1406770
MyDoggieJessie
MyDoggieJessie
Posted Monday, January 14, 2013 8:41 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 2,063,
Visits: 3,787
Is the LDF file 39.3MB?
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1406773
jayoub1
jayoub1
Posted Monday, January 14, 2013 9:05 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 8:43 AM
Points: 56,
Visits: 156
Yes. That must be where the difference is. I guess the backup guys are not doing their job and the transaction log is not committing to the database. Correct?
Thanks for your help
Jeff
Jeff
Post #1406788
MyDoggieJessie
MyDoggieJessie
Posted Monday, January 14, 2013 9:12 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 2,063,
Visits: 3,787
Is the DB is simple, full, or bulk-logged mode?
If in bulk or full mode make certain you have transaction log backups running. The "standard" is typically every 15 minutes however, depending on your environment that may be too soon (the general rule of thumb is "how much data are you comfortable with losing?"
)
For more information on the Transaction Log, what it is, how it works, etc - Please refer to this
excellent article
by the Master, Gail Shaw
BTW everything is written/committed in the transaction log
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1406794
jayoub1
jayoub1
Posted Monday, January 14, 2013 9:44 AM
Valued Member
Group: General Forum Members
Last Login: 2 days ago @ 8:43 AM
Points: 56,
Visits: 156
Yes it is in Full Recovery Model, so I will be contact the backup group so ask what is happening. They are only using Backup Exec and no native SQL backup of transaction logs. I am planning to implement native backup job to augment the Backup Exec system.
Thanks for the help
Jeff
Jeff
Post #1406817
MyDoggieJessie
MyDoggieJessie
Posted Monday, January 14, 2013 1:49 PM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 2,063,
Visits: 3,787
Haven't used Backup Exec in a few years but my guess is it doesn't perform transaction log backups at all (I'm sure it physically backs up the LDF file itself, but that's not the same). The DBA (is this you?) needs to create these manually...
If you don't create a maintenance plan to back them up then your log files will continue to grow
______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1406935
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
