SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reducing Initial LDF/MDF size


Reducing Initial LDF/MDF size

Author
Message
mikehunsaker
mikehunsaker
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 180
Hello,

I have a database (version 9.0.4060) that has an initial log size set to 322GB. I would like to change the initial size to around 100GB but I don't know if that will cause any problems. I already went through the process of backing up the database/log. As of right now, the log file has 99% available free space according to the "Shrink File" dialog box in SSMS. Is it safe to reduce the initial size of the log from 322GB to 100GB in the Database Properties with no problems? This is a production system.

Thank you in advance!

Mike
mikehunsaker
mikehunsaker
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 180
Any ideas? This might not be possible but I wanted to ask the experts. Thanks again.
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15225 Visits: 25280
Items I believe you should consider. (Not knowing your exact operation).
1. I assume that you have NOT found any evidence/documentation left by your predecessor(s) as to why the log file is at its current size.
2. Are there any periodic (end of month / end of quarter / end of year / large imports of data) operations (reports etc.) that would require such a large log file or cause a smaller log file size to grow to what you now have?
3. Do you have any desperate need for the disc space that would be "freed up" ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
mikehunsaker
mikehunsaker
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 180
Thanks Ron. I appreciate the response. I talked to the System Owner and they are unaware of why the log size was set to 322GB. We are not hurting for disk space so this is not a major issue. Just trying to get a handle on things before they become an issue.

Given that we have not overlooked anything, is it possible to reduce the log size without causing problems? I will pass this information along to the System Owner and let them decide.

Thank you again.

Mike
Sqlsavy
Sqlsavy
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 868
What is the size of the database generally log should be 25-30% of database size. If your database is in full /Bulk logged recovery model take log backup and shrink log to required size and then schedule log backups frequently (eg: every one hour). Log backup will truncate the transaction log and make the space available.

But you’ve to shrink the log manually because truncation does not change the size of the log file on disk. So when the log file is shrunk, the size of the log file is reduced to the size requested, or the minimum size to contain all in use log segments in the log.
mikehunsaker
mikehunsaker
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 180
The size of the database is roughly 15GB. I might shrink the log down and remove some of the unnecessary space. I'll talk to the System Owner to make sure before I do anything. Thanks for the input. I really appreciate it.
Sqlsavy
Sqlsavy
SSC Eights!
SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)SSC Eights! (855 reputation)

Group: General Forum Members
Points: 855 Visits: 868
If DB size is 15 GB then i would keep my log to 4GB .There will not be any issue with shrinking the log you have lot of free space avaialble on the drive instead for use
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search