SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


db autogrowth problems


db autogrowth problems

Author
Message
jayoub
jayoub
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 540
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6664 Visits: 7393
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 :-D

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
jayoub
jayoub
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 540
Thank you very much and I will look into sizing the database.

Jeff
durai nagarajan
durai nagarajan
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1607 Visits: 2775
how frequently the DB grows with 500MB growth settings

take this into consideration while setting auto growth option.

Regards
Durai Nagarajan
jayoub
jayoub
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 540
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6664 Visits: 7393
Is the LDF file 39.3MB?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
jayoub
jayoub
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 540
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6664 Visits: 7393
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?" :-D)

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" ;-)
jayoub
jayoub
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 540
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
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6664 Visits: 7393
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search