In most cases, we do not set the database file large, initial size is always less than 1GB, unless a decision is taken to make it suitable for next few months. If the database has to be created with a larger file, then of course, we need to set the file size with database creation, and it takes long time for creation. Why it takes long time? We dont have data, and it is just an allocation from the space in the disk. But it takes time if the size is in GBs.
It takes long time for initializing the space required, by filling the file with zeros. This makes sure that previously deleted files cannot be accessed and no security violation. This Zeroing Process applies to;
- Create a database
- Add files to an existing database
- Increase the size of the file
- Restore a database
If required, this process can be stopped for SQL Server and reduce the time it takes. SQL Server 2014 and before, it has to be done by adding the SQL Server service account to a policy group called Perform Volume Maintenance Task. However SQL Server 2016 allows us to set this at installation itself.
If you select this checkbox during the installation, it will not take long time for peforming above mentioned operations. Here is a comparison I just did.
This code is based on SQL Server 2014 with default setting. Its service account has not been added to policy task and as you see, it has taken more than 2 minutes for creating a database with 10GB size file.
This shows the same code execution with SQL Server 2016. Note that I have enabled the option during the installation. As you see, it has taken only 14 seconds.
Note that this does not applicable for log files. You should consider this option if you frequently perform these operations against your SQL Server.
No comments:
Post a Comment