asked
Autogrowth settings
Dear Friends, I Create a database with default auto growth setting. in which my database have setting of .mdf file is Enabled auto growth is checked and file growth section is selected with in Megabyte option with 1 MB value, and Maximum file size section is selected with unresticted file growth. another side for .ldf file is autogrowth is checked and in file growth section is selected with in percent and value is 10 and in maximum file section is selected with unrestricted file growth. now application becomes popular and a huge transaction occur on this database. at present database size become approximate 25GB. I have to much space on disk approx 800 GB. MDF and LDF file is configured on seperate disk. Recently I notice event id 833 log in SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [L:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\TMS_DB2.0_1.LDF] in database [TMS_DB2.0] (7). The OS file handle is 0x0000000000000798. The offset of the latest long I/O is: 0x000000da2c1800 Now I'm bit confused about this, it is due to autogrowth settings. or auto growth setting can improve performance? if yes is any precaution required before to change this settings. Thank you With Regards Mukesh Kumar +919433424630
The site runs on voting. Please indicate all helpful answers by clicking on the thumbs up symbols next to those answers. Please indicate any one answer that lead to the best solution by clicking on the check mark next to that answer.
Autogrowth of 1mb on the log is very bad. You get a large number of individual file segments and this can absolutely lead to poor performance. I would recommend shrinking the log file as small as you can, then regrow, manually, to a larger file size. Then, yes, set the auto-grow number to something much larger like 10gb or something. You'll need to experiment around that. You're seeing long log operations because the log has to scan all those individual files which are scattered all over your disk.
Auto growth is your friend. Without it if you had a high transaction database you may need to monitor your database daily. Depending of course on the initial size of the database and the growth settings. A better solution and recommended by many in the SQL Community is to use autogrowth but set the growth to be in size (MB/GB) increments instead of percentage (10%, 20%) A good practice is to pre-grow the database to a certain size. The main reason is growing a database is resource intensive. Pre-grow it initially to a projected size in 2 months (4 or 6 months if you have the space). If the database is already created, then you can still manually grow it to a larger size but you should do so during a maintenance window. Logs files the same thing. Pre-grow to 1.5-2x the largest size. Of course you should set up monitoring for growth. You can use Extended Events or the default trace for that. Then track the occurrence and adjust sizes accordingly.
16 People are following this question.
