Need to change the log file to grow un-restricted

Question

Need to change the log file to grow un-restricted

bubby

SSCertifiable

Points: 5638
More actions
August 26, 2010 at 9:18 am

#140394

Hello,
I am in SQL 2008, I have adatbase in Simple Mode, and I am dbowner of the database, trying to change the log file to grow un-restricted,
When I go through properties it is at 10% growth, and restricted to say to 2 GB, when I change to unresticted and go back to properties it goes back to same old 10%
How I can get that done, help appreciated,
Thanks,
Dev

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

There's nothing wrong with that. The 10% refers to the amount it will grow by each time it grows, not the amount it is allowed grow by.

FYI, 10% (or any %) is not a good value for the growth. Each time the database grows, it will grow by a larger amount each time. Set it to a set number of Megs instead.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

bubby SSCertifiable Points: 5638 More actions · Answer 2

Actually I changed that to MB, but the maximum it grow is 2064 MB, I want to make it as unrestricted

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It won't be 2046 MB, it's 2046GB i.e. 2 TB and that's the maximum allowable size for a log file. Hence it's the same as unrestricted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bubby SSCertifiable Points: 5638 More actions · Answer 4

Thanks actually, it was my fault, it is 2 TB,

I actually ran the below statement to convert into un-restricted,

ALTER DATABASE ShavlikScans

MODIFY FILE

(NAME = ShavlikScans_Log,

MAXSIZE = UNLIMITED);

GO

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

So, when you look at the file properties, what does it say now for the maximum file size?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

bubby SSCertifiable Points: 5638 More actions · Answer 6

It still says restricted to 10 MB growth till 2 TB, which is I guess SQL 2005 is,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

The maximum size that a log file can be in SQL (all current versions) is 2TB. You can look that up in Books online. Hence a log that's got unrestricted growth is actually limited to 2 TB because that's the maximum size of the log file. Hence a log with a max size of 2 TB is exactly the same as a log file that's got an unrestricted max size.

Expecting a 2TB log any time soon?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pankushmehta Ten Centuries Points: 1217 More actions · Answer 8

The Max LogSize allowed in 2TB. it is pretty big a size for a log file and i will be surprised if you are reaching that limit!

You can specify multiple log files, similar to multiple database files.