Issue with changing MaxFileSize
-
Here's an odd one. SQL Server 2005, SP1. I created a DB through SSMS the other day and was doing data loading. Then I had to delete it because I screwed it up, but before I did, I scripted the database creation out. I ran the script to create the database and started loading data all over again and promptly ran out of space on my Transaction log.
When I looked at the Transaction Log, the Size was restricted. So I set it to Unrestricted growth, hit okay and went back to loading data. 5 minutes later, my Transaction Log was full again. Mind you, my DB is on 160 GB drive with 140 GB free space. So, knowing I had plenty of HD space, I checked the database properties and the Log Size was back to being restricted to the same size as BEFORE I set the Unrestricted Growth property. (BTW, so far I have been unable to replicate the "same size" issue, but I have replicated the Restricted Growth issue).
I know I'm crazy, but I don't think the whipped cream incident has anything to do with my problems...
Anyway, if you run the following code...USE [master]
GO
/****** Object: Database [MyTest] Script Date: 06/05/2007 06:05:08 ******/
CREATE DATABASE [MyTest] ON PRIMARY
( NAME = N'MyTest2', FILENAME = N'D:\PC SQL DBs\MyTest2.mdf' , SIZE = 4000KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyTest2_log', FILENAME = N'D:\PC SQL DBs\MyTest2_log.ldf' , SIZE = 4000KB , MAXSIZE = 20MB ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
And go into the SSMS -> MyTest -> Properties -> Files window, you'll see the log is restricted to 20 MB in size. Right? Now click the ... button and change it to Unrestricted. Hit OK twice. Open the properties back up. If your DB does anything like mine did, the Log Size will be back to restricted. This time, though, it'll say "restricted growth to 2097152 MB" which is a far sight bigger than 20MB (and bigger than any hard drive I have), but it still says "restricted".
Does anyone have any thoughts as to why the log size keeps saying "restricted Growth" when the data file sizes can say "UnRestricted Growth"? Is there an upper limit on how big Log files can grow? I can't find it in BOL, but I might be looking under the wrong things.
Would there be a reason why, if I specify larger sizes on the Transaction Log files that it wouldn't allow me to change this?
-
I've run into similar situations. Mine are usually some variant of an obscenely high percentage growth number. I generally set databases to automatic growth, but with a max size. I usually allow development datafiles to grow by 250 or 500 MB to a max of 20-25GB.
However, occasionally my nice 250MB growth setting will show as 32000 percent. Adding insult to injury, SSMS will not allow me to the screen to change this property.
I have to build a script, which always comes to the rescue:
USE
[master]
GO
ALTER
DATABASE [yourDB] MODIFY FILE ( NAME = N'logFileName', MAXSIZE = UNLIMITED)
-- OR
ALTER
DATABASE [yourDB] MODIFY FILE ( NAME = N'logFileName', FILEGROWTH = 256000KB )
GO
GUIs always leave me feeling a little sticky anyway...
Carter
But boss, why must the urgent always take precedence over the important? -
Thanks, Carter. It's nice to know I'm not the only one with issues regarding this.
I might submit this to MS as a bug in the GUI unless someone can point out to me what I'm doing wrong. Anyone? Help would be greatly appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply