SQL Server Tacklebox- P22: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 4 Managing data growth Figure Free space in data file after truncate table statement. Figure Minimal log file growing with data load. 105 4 Managing data growth This time there were no Auto Grow events for the data file and only 20 for the log file. The net effect is that the average time to load 50 000 records is reduced from seconds to seconds. A time saving of just over 1 second per load may not seem significant at first but consider the case where the same process normally takes an hour. Just by ensuring log and data growth was controlled you have cut the process down to under 30 minutes and saved a lot of I O processing at the same time. Handling space problems I ve shown that having incorrectly sized data and log files and inappropriate Auto Grow properties both inherited from the model database can significantly increase the I O load during bulk insert processes. I ve also demonstrated the dangers of unabated log file growth unless you change the default recovery model or perform regular log backups. Even for a database that is subject to as few as 50K transactions per day I have seen the database log file grow to over 220G over the course of a few months because no log backups have been taken. The reason for this is that generally there are databases with low level SLAs meaning that a full nightly backup is all that is required. As I ve stressed previously handling these space issues is mainly about planning. The DBA needs to Correctly size the files if you know that the database you are managing can expect a 2 Gig growth per month size the data file s at 4G initially not the 3 MB size that will be the default from the Model database. Set correct auto grow properties while 10 growth for data and log files may be sufficient for low utilization databases typically I set at least 500 MB for the auto growth settings for the data and log files. Unless I expect there to be unusually high data growth 500 MB represents a good average growth rate .
