SQL Server Quick Tip: Growing your database past 2TB on SQL Server 2008
A couple of days ago I had to manually grow the database data file for a client, they do not use autogrowth and plan growth events. They are running on a SQL Server 2008R2.
The database data file was 1,9TB before the growth event, since we always increase the data file with 10% the new size should be 2,09TB.
So I opened up the SQL Server Management Console and connected to the database, opened the file properties and changed the old value into the new value and clicked “OK”, after that the funny stuff happens…
I noticed that my database data file didn’t grow to the desired size but returned a size of 2TB exactly, now this was very strange and initially I suspected a type of storage limitation on maximum file size. After asking around we verified there was no such limit set on the storage level.
Now keep in mind the database data file was on a partition of 2,5TB (GPT formatted) and there was enough free space left for the database to grow, there also wasn’t a maximum size set in SQL Server for the database data file…
So after a few hours of google resulting into nothing I decided to check the Microsoft Bug site and look what I found:
SMS interface restircts you from updating the “initial size” of any database data file, using to a size larger than 2TB, even though the OS supports it and there is enough disk space available. Using the ALTER DATABASE statement does enable you to configure such a data file.
And this was reported for SQL Server 2008.
So I tried to script the new initial size, and what do you know, it actually worked!
The bug report said it was repaired in SQL Server 2012, I checked and the problem is indeed gone!
So if you manage some databases with data files larger then 2TB and running on SQL Server 2008 or SQL Server 2008R2 keep this in mind and save yourself hours of searching for a rather simple solution!