Can't set unrestricted max size of LOG file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can't set unrestricted max size of LOG file

Author

Topic

Largo
Starting Member

22 Posts

Posted - 2010-08-13 : 02:50:29

I have the following T-SQL which simply creates new database. However, in properties of this DB I see that LOG's max file size is restricted to 2 GB. Where's error?


CREATE DATABASE Bonus
ON PRIMARY
(
	NAME = N'Bonus',
	FILENAME = N'E:\Bonus.mdf',
	SIZE = 3072KB ,
	MAXSIZE = UNLIMITED,
	FILEGROWTH = 2048KB
)
LOG ON 
(
	NAME = N'Bonus_log',
	FILENAME = N'E:\Bonus_log.ldf',
	SIZE = 1024KB,
	MAXSIZE = UNLIMITED,
	FILEGROWTH = 2048KB
);

-----
There is no knowledge that is not power.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-13 : 08:01:07

quote:
Originally posted by Largo

I have the following T-SQL which simply creates new database. However, in properties of this DB I see that LOG's max file size is restricted to 2 GB. Where's error?

CREATE DATABASE Bonus
ON PRIMARY
(
NAME = N'Bonus',
FILENAME = N'E:\Bonus.mdf',
SIZE = 3072KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 2048KB
)
LOG ON
(
NAME = N'Bonus_log',
FILENAME = N'E:\Bonus_log.ldf',
SIZE = 1024KB,
~~MAXSIZE = UNLIMITED,~~
FILEGROWTH = 2048KB
);

-----
There is no knowledge that is not power.

No, you're never too old to Yak'n'Roll if you're too young to die.

Largo
Starting Member

22 Posts

Posted - 2010-08-13 : 08:39:28

No changes. Max size of LOG is still 2 GB. :(

-----
There is no knowledge that is not power.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-13 : 08:48:25

Create your DB in SSMS-Dialog, don't execute it but choose "create script" and maybe you can see...

No, you're never too old to Yak'n'Roll if you're too young to die.

Largo
Starting Member

22 Posts

Posted - 2010-08-13 : 08:55:22

Yes, I did it too, but result is always the same. I even used the following query, but still no changes.


ALTER DATABASE Bonus
MODIFY FILE
(
	NAME = N'Bonus_log',
	MAXSIZE = UNLIMITED
);

-----
There is no knowledge that is not power.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-13 : 11:28:09

2 GB or 2 TB? (check what unit the size is shown in)

2TB is the maximum possible size of the log. It's the same as unrestricted

--
Gail Shaw
SQL Server MVP

Largo
Starting Member

22 Posts

Posted - 2010-08-16 : 01:33:52

Oops, you're right! 2TB, of course! Thanks for tip. :)

-----
There is no knowledge that is not power.

captgus
Starting Member

1 Post

Posted - 2013-11-13 : 17:53:39

I can accept that there is a 2 TB log file size limit but can someone please explain that when I look at different databases, on the same 2008r2 instance, why some databases have log files set with unlimited growth and others have the 2 TB cap?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources