October 10, 2012 at 9:50 am
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.
October 10, 2012 at 10:20 am
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.
October 10, 2012 at 11:14 am
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?
October 10, 2012 at 11:52 am
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'
October 11, 2012 at 2:38 am
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
October 11, 2012 at 3:01 am
use this
ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'DATABASENAME_Log', SIZE = 512000KB , FILEGROWTH = 0)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply