May 1, 2008 at 1:34 pm
when I issue the command:
ALTER DATABASE db_name
MODIFY FILE
(NAME = filename_Log,
maxsize = UNLIMITED)
it does not keep that setting. It is set to restricted file growth with a max of 2,097,152
I also tried changing it in management studio with the same results.
Has anyone else run into this bahavior
May 1, 2008 at 3:22 pm
In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB. Altering the database without specifying the MaxSize will let the file grow until the disk is full.
May 1, 2008 at 3:29 pm
yeah, I realize the cap on the sizes, but it's frustrating to me that I can't actually set it to unlimited growth. I'm trying to setup monitoring for consistent settings across databases, and some of them are set to unlimited, and the ones that are not, will not take the unlimited setting.
for instance, take a database that has a log set for unlimited growth. if you change it to be restricted to say a 10GB log, it will accept that, but when you try to change it back to unlimited, it will act like it accepts it, but when you go back in it says it's restricted to 2TB. It would be nice to have consistency.
May 2, 2008 at 2:52 pm
Try altering like this.
ALTER DATABASE db_name
MODIFY FILE
(NAME = filename_Log)
Since the MAX Size is not specified , it will be set to default i.e. unlimited.
May 4, 2008 at 10:01 pm
As kk mentioned, the maximum size of the log file is 2 TB. Refer the comments from BOL
UNLIMITED
Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.
Also refer this link, https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=175549
[font="Verdana"]- Deepak[/font]
July 15, 2009 at 2:32 pm
To clear things up a bit more! The data files and log files are not the same as the database size.
http://www.sql-server-helper.com/sql-server-2005/maximum-capacity-specifications.aspx
SQL Server 7 SQL Server 2000SQL Server 2005 (32-bit)
Database size 1,048,516 TB1,048,516 TB1,048,516 TB
Databases per instance of SQL Server 32,76732,767 32,767
Filegroups per database 256256 32,767
Files per database 32,76732,767 32,767
File size (data) 32 TB32 TB 16 TB
File size (log) 4 TB32 TB 2 terabytes
SQL Server 2008 : http://msdn.microsoft.com/en-us/library/ms143432.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply