Datafile Capacity

Are your data files full? What does it matter so long as they auto grow? Won’t that take prevent the system from crashing?

It is true, that when a data file gets full, it will automatically grow by allocating another block of space on the disk (by default, 10% of the existing size of the data file). However, this growth can take some time – several seconds or minutes – right in the middle of the day!

Every time SQL Server needs to grow a data file, it locks the whole file for the duration of the growth. This means that your system will suddenly halt for a few seconds or minutes right when everyone is trying to use it.

One popular method that Vendor created applications ‘solve’ this is by setting the growth size from 10% to 1MB (Megabyte). 1 MB is a very small size for a database which is usually 500MB to several GB (Gigabytes). Application vendors, which are not DBAs, think that this small setting will make each growth quick and not very noticeable.

Well, they are right. However, it still causes constant growths throughout the day which all adds up. What’s worse is that this constant allocation of small pieces on the disk makes the underlying data files horribly fragmented! This slows down SQL Servers ability to retrieve data quickly because the hard disk heads must bounce all around the platter in order to read a contiguous ‘Customers with a name starting with an L’, for example. This is harder on the disks, and slower for the users day in and day out.

The real solution is simple – just pre-grow the data files at regular intervals during off hours while users are not using the system. This way, there is no delay at all in the day time, and the files can be grown in large blocks of hundreds of MB or several GB, removing physical disk file fragmentation. With SQLWatchmen monitoring your systems, we keep an eye on this also! We gauge how quickly your databases fill up, and as they get closer to filling up, we stay late and grow the files as needed. We typically watch this each month, and grow the files one or two times a year to always stay ahead of the curve.

This is proprietary code not found in SQL Server. Based on years and years of experience with working with SQL Server, we wrote our own code to solve this, and many other common problems.

© 2001-2014 SQLWatchmen, LLC.

1766 FM 967, Suite A., Buda, TX 78610          (877) SQL-Watchmen