I wanna have it this time about growing data-files.
Databases do growth as more data (and indexes) is (are) added. A nice feature available in Sql Server (already from version 7.0) is the auto-grow database-setting. However, you have to see this option more as an insurance. An insurance for that given moment you did not follow-up your database-growth pattern and forgot to manually resize your database data-file accordingly. Not setting the auto-grow option and forgetting to resize your database data-file does mean that when the data-file(s) is (are) full, no data can be added anylonger.
If Sql Server automatically grows your database-files, this will happen with the increment you specified (default (only) 1 Mb in Sql 2005, previous versions 10% for data-files). As you do not follow-up your database-growth, there is a big change that external file fragmentation occurs often, degrading your performance.
So far the introduction.
With Sql Server 2005, there comes a new advantage for growing data-files: fast file initialization (FFI). From now on, it does not take minutes anylonger to grow data-files by several Gb's. FFI let you create and grow data-files almost immediately.
How does it work? Till version 2000, the data-files were initialised with zeroes. With FFI this is not longer the case.
Does it has disadvantages? I already have running several databases in Sql 2005 and I do not encounter disadvantages, nor read something about it.
Are there requirements? Yes, 2:
- Sql Server has to be running on Windows 2003 (NTFS of course). Windows XP does also support this feature.
- The account under which Sql Server is running has to be granted the "Perform Volume Maintenance Task" local security right, which is the case for the local administrator group by default. So if you are running with a service account which make part of the local administrator group, you do not have to do anything.
Can you disable it? Yes,
- See previous windows requirement.
- Traceflag 1806 always zeroes out files on creation and expansion.
And the log file?
- Same behaviour as previous versions, no FFI, so you will have to "wait".
CU, PDS