Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

db autogrowth problems Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 12:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
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
Posted Wednesday, January 9, 2013 1:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 3,893, Visits: 7,136
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
Posted Wednesday, January 9, 2013 2:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
Thank you very much and I will look into sizing the database.



Jeff
Post #1405020
Posted Friday, January 11, 2013 11:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
how frequently the DB grows with 500MB growth settings

take this into consideration while setting auto growth option.



Regards
Durai Nagarajan
Post #1406202
Posted Monday, January 14, 2013 8:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
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
Posted Monday, January 14, 2013 8:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 3,893, Visits: 7,136
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
Posted Monday, January 14, 2013 9:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
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
Posted Monday, January 14, 2013 9:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 3,893, Visits: 7,136
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
Posted Monday, January 14, 2013 9:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
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
Posted Monday, January 14, 2013 1:49 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 3,893, Visits: 7,136
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 »

Add to briefcase 12»»

Permissions Expand / Collapse