resize .ldf file

Question

resize .ldf file

little1

Say Hey Kid

Points: 706
More actions
January 27, 2010 at 6:00 am

#229392

Hi
I hope someone can help. I have 2 databases with 4gb log files which only use around 3mb. I want to reduce the log files from 4gb to free up the space and put on autogrow from 3mb.
The size of the logs was set at install stage is it possible?
When i try to change it in properties, click ok, looks fine but when i go back into it, its reverted back to the original state.
I shrunk the log files before i tried to change the size.
Is it different for SQL 2008?
Thanks for reading

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

log files will grow in size automatically. they typically grow for two reasons: either a huge transaction occurred, or more likely, the database option for recovery is set to "FULL" and regular backups are not occurring, so all changes are still a part of the log file, awaiting backup.

once a log grows to a large size, SQL expects that that is a normal occurrance, and preserves the space for future expansion...that's why you are seeing a lot of free space.

if you don't have a backup plan in place, this log expansion can continue until you are out of harddrive space and SQL stops running.

you'll want to get a regualr backup schedule set up, and shrink the trnasaction log once to at least start it at a more normal size.

Gail has a nice article about backups and this issue here:

http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

little1 Say Hey Kid Points: 706 More actions · Answer 2

Thanks for your reply. We have backups in place and the log file doesnt grow more to than 3mb when sql was installed someone set it to 4gb but we need that space back from both the DB's so is there no way to reset the size of the log to around 5mb?

Lowell SSC Guru Points: 323518 More actions · Answer 3

sure; simply go to the database and choose Shrink >>Files like you see above.

change the drop down from Data To Log and run it.

then if you want, you can change the defined size in the database properties:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

little1 Say Hey Kid Points: 706 More actions · Answer 4

Thanks for your reply sorry for the late response i have been off work. I do this and when i try to reduce the log files to autogrow and no more than 10mb (advised customer wanted 3mb but thought that was a little too low) i get this message

Modify file failed. Size is greater than MAXSIZE

Is this because the file was orginally set to 4gb?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 5

you cannot resize it to be smaller than it currently is...did you perform a shrink before trying to modify the size? that is the most likely issue.

I think you'll need to shrink, THEN try to modify the default size.

in my example, see how my log is 505 meg with 489 free, but the second dialog says the Initial size is 740?

tha tmeans the smallest i could make it is 505, unless i shrink it.

if i shrink it, i would make it 505 - 489 = 26 meg.

then i could make the Initial size 26 meg, but never 5 meg.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

little1 Say Hey Kid Points: 706 More actions · Answer 6

thanks i backed up the transaction log then preformed the shrink. Nothing seems to work. But i have now set up trn jobs to back them up so hopefully this should solve the problem.

Many thanks for getting back to me