January 7, 2014 at 11:57 am
Hello,
Today I ran into a very strange problem I have never experienced before.
I have a database with a initial size of 2,097,152 MB, around every 1-2 months we manually increase the initial size by 10%.
When I tried to increase the initial size today and clicked "OK" the old size of 2,097,152 MB immediately returned and the database data size didn't grow as expected. Funny thing is I can decrease the value without a problem, just not increase.
Also when I open the default Disk Usage report I see a message stating "There is no data file for xxxxxx database."
The database is running fine and users are working on it without any problems, has anyone ran into this problem before and got it fixed?
P.s. the database is running in a failover cluster, I allready tried a failover to see if that would fix the problem which it didn't.
January 7, 2014 at 12:41 pm
Some extra information I just found out:
Whatever I try to do the file simply won't grow past 2,097,152MB which happens to be 2TB exactly.
Does anyone know of a possible limitation of filesize on a NTFS volume? The volume is formated as a GPT partition so it is bigger then 2TB.
January 7, 2014 at 7:21 pm
3nrico (1/7/2014)
Some extra information I just found out:Whatever I try to do the file simply won't grow past 2,097,152MB which happens to be 2TB exactly.
Does anyone know of a possible limitation of filesize on a NTFS volume? The volume is formated as a GPT partition so it is bigger then 2TB.
Is this mdf file or ldf file?....if its ldf file, then that's your answer, an ldf file can only grow upto 2tb. An mdf file will grow up to 16tb.
So, please clarify if its data or log file you are talking about.
Regards,
SQLisAwe5oMe.
January 8, 2014 at 1:09 am
It is the database data file, so the MDF.
January 8, 2014 at 1:30 am
What is the file max size set to?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 8, 2014 at 1:35 am
Unrestricted growth.
The database is on a SAN disk but the storage admins tell me there is no file size limit.
January 8, 2014 at 2:04 am
Sounds like a limit imposed by the operating system then.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 8, 2014 at 2:13 am
Allright I just fixed the problem.
There seems to be somekind of bug in the Database Properties GUI when you want to increase the initial size above 2TB.
When I increased the initial size by using a T-SQL query to 2,3TB it worked without a problem.
I'll report this as a bug to Microsoft.
Tnx for the help!
January 8, 2014 at 2:21 am
Offcourse after searching Microsoft Connect (which I should have done in the first place) I found a bug report for SQL Server 2008:
It should have been fixed in SQL Server 2012, I have no way of checking it though so I'll just believe them 😉
January 8, 2014 at 5:10 am
thanks for posting back, this will certainly help others i'm sure
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply