Initial file size for the .ldf

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Initial file size for the .ldf

Initial file size for the .ldf

Rate Topic

Display Mode

Topic Options

Author

Message

jdbrown239

Posted Wednesday, October 10, 2012 9:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:35 AM
Points: 112, Visits: 212

Hi
Can I change the initial file size of the log file while the database is online. The last DBA mistakenly set the the initial log file size to 26GB. I tried to truncate the log but it did not work. When I checked the file I found the initial size was set to that number.

Post #1371019

brendan woulfe

Posted Wednesday, October 10, 2012 10:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:51 AM
Points: 192, Visits: 915

Are you on SQL Server 2005? Truncate_only was taken out in 2008, for good reason. If you are certain your log file is larger than it needs to be you can shrink the file. But it will only clear space if you have any free.

If you shrink the file and it grows back to the level it's at now and you keep repeating that process your going to cause fragmentation. So be sure you absolutely need to do this. While you're at it you might as well check your log backups to make sure they are working as well.

Post #1371032

jdbrown239

Posted Wednesday, October 10, 2012 11:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:35 AM
Points: 112, Visits: 212

I am in sql 2005 (wrong forum sorry). Yes the log file needs to be decreased. Shrink did not work because the initial file size is set to the current size. Can I reset the initial file size while the database is online and then truncate the log?

Post #1371047

brendan woulfe

Posted Wednesday, October 10, 2012 11:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:51 AM
Points: 192, Visits: 915

Have you checked log_reuse_wait to see what it the log reuse is waiting on? If it's nothing I don't see why the shrinkfile wouldn't work.

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'db name here'

Post #1371067

Jason-299789

Posted Thursday, October 11, 2012 2:38 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:30 AM
Points: 803, Visits: 2,124

Regardless of SQL version I tend to use a 20% rule of thumb for the initial logfile size based on the Sum size of the datafiles, and with a proper backup strategy you shouldnt see it grow much over this.

That said each database is unique and a lot will depend on the number transactions going through the system, more transactions=more space getting filled thus bigger growth potential.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1371288

Bhuvnesh

Posted Thursday, October 11, 2012 3:01 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

use this

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DATABASENAME_Log', SIZE = 512000KB , FILEGROWTH = 0)

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1371296

« Prev Topic | Next Topic »

Permissions