Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reducing Initial LDF/MDF size Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 11:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, 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
Post #1424197
Posted Thursday, February 28, 2013 2:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, Visits: 180
Any ideas? This might not be possible but I wanted to ask the experts. Thanks again.
Post #1425280
Posted Thursday, February 28, 2013 2:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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
Post #1425289
Posted Wednesday, March 6, 2013 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, 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

Post #1427601
Posted Thursday, March 7, 2013 7:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:00 AM
Points: 232, Visits: 823
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.
Post #1427974
Posted Thursday, March 7, 2013 10:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 11:31 AM
Points: 26, 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.
Post #1428132
Posted Friday, March 8, 2013 3:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:00 AM
Points: 232, Visits: 823
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
Post #1428481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse