June 19, 2007 at 1:46 pm
i am trying to change the log file growth to unrestricted. Using the management studio UI, simply clicking on the properties of the DB and then files and then change the option to unrestricted growth on the log file. WHen i hit ok and check the properties, it flips back to unrestricted.
my role is set up as sa.
any help?
thank you in advance
June 19, 2007 at 6:46 pm
did u refresh it ????? else check it in sysaltfiles.........
select * from master..sysaltfiles
[font="Verdana"]- Deepak[/font]
June 20, 2007 at 1:47 am
You want to set it to unrestricted growth. After you hit ok you have unrestricted growth. Then whats the problem. Do you mean to say that you need to set resticted growth or vice versa. your query is not clear friend.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 20, 2007 at 7:05 am
I miss-typed my problem. the second part should read it returns to restricted growth.
June 20, 2007 at 7:11 am
I've had occasional problems with the UI, but a script will get the job done for you. Something like this:
USE
[master]
GO
ALTER
DATABASE [yourDB] MODIFY FILE ( NAME = N'logFileName', MAXSIZE = UNLIMITED)
GO
Change the DB name and logFileName and run this on your server.
Hope this helps!
Carter
But boss, why must the urgent always take precedence over the important?
June 20, 2007 at 7:18 am
There problem might be because you are trying to enter a value for the restricted growth to a value lower than what is being used now. check the same and make necessary changes.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 25, 2007 at 11:58 pm
even i have the same problem .. i have even tried running the script to change the log file size to unlimited. but it doesnt seem to be working... any one with solution for this,,, is this a bug in sql 2005....
Harry
Thanks
June 26, 2007 at 5:56 am
AN update on my problem.
The script also has not worked. I am going through an exercise today to archive some data out of the database to free up disk space. I am about at my limit for space and am wondering if SQL Server is detecting this and not allowing the log to grow unrestricted. I should be finished in a few hours and will post back.
June 27, 2007 at 6:25 am
I am having the same problem. (SQL2005 SP2a)
When I change the Maximum File size to "unrestricted" click OK and then check back, it defaults back to limited size 2.097.152 MB
Sometimes I also notice that the Autogrow settings on some db's gets set to 32768% or even 65536%. I don't know why or how and this is probably yet another problem. I can then no longer change it in the Manager, and have to use the ALTER DATABASE to change it back to normal....
June 27, 2007 at 6:34 am
Do you have recent Service Packs installed. This morning i read an article in another website saying that there were lot of bugs with SSMS in RTM and they ar fixing it and most have been fixed in SP1 and SP2.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 27, 2007 at 8:38 am
On the Connect Site :
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=175549
MS stated that this behaviour is *by design* and that setting a log file MAXSIZE to UNLIMITED will make it 2TB which is the Max Log Size in 2005.
ON 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.
* Noel
December 24, 2007 at 7:20 am
the only i know off to get this 'unrestricted' radio button enabled for the log file growth again is to;
- backup db
- detach db
- rename log file to something xx_old.ldf
- attach db again using only the MDF file again and letting SQL create a new log file for you
- delete xx_old.ldf file if all successful
the above method worked for me. hope it helps
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply