November 11, 2015 at 2:09 pm
I have a database where I need to make a change to the log file size, in that I want unrestricted growth, and to grow by 100 MB. I used the GUI to make the changes, but if I query the sys.database_files, the max size is still set to the 256 MB setting, although going back into the GUI does show the changes. I have an SSIS package that is causing a lot of logging (no way around that), and this is becoming a problem. If there is a need to recycle the instance to have this take effect, I need to know that. Anyone?
EDIT: I run the package and it fails with a transaction log full error.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 11, 2015 at 3:14 pm
I wouldn't use the gui for that, although, to be fair, the gui usually does ok on that particular task. But it's so relatively flaky on others that I wouldn't risk it. And you certainly shouldn't need to recycle SQL for that.
Instead use the command:
ALTER DATABASE db_name MODIFY FILE ( NAME = <logical_file_name>, FILEGROWTH = 100MB )
Edit: Added closing > on NAME value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 11, 2015 at 4:49 pm
Turned out I was misinterpreting the values from sys.database_files. The max_size value coming back was 256 MB, but as that value represents the number of 8K pages, or 2 GB. I still don't know why it wouldn't just set that value to -1 like some of the other databases, as that represents unrestricted growth. However, I did find out that we had a disk space problem (I do NOT have RDP access to the server, so I couldn't go look for myself), that was cleared up when another databases' log file was dealt with, as it was occupying 72 GB on the same drive. Live and learn...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply