June 18, 2014 at 12:19 pm
when I run this query:
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;
Results (approx):
Total SizeAvailable Space
Log1_file E:\30GB29.5GB
Log2_file F:\25GB24.5GB
how the best way to configure Log files? can I reduce total size to 1 or 5 GB on both Log files?
June 18, 2014 at 1:46 pm
My preference is to: 1) Use a single log file per database 2) Ensure auto-growth settings are set to some reasonable amount in MB (not percentages) and 2) size the log file to a size that equals the largest clustered index size + 10%
I recommend you read through this excellent article [/url]by Kimberly Tripp
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 18, 2014 at 2:15 pm
smtzac (6/18/2014)
when I run this query:SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;
Results (approx):
Total SizeAvailable Space
Log1_file E:\30GB29.5GB
Log2_file F:\25GB24.5GB
how the best way to configure Log files? can I reduce total size to 1 or 5 GB on both Log files?
The primary good reason to have multiple files is if you have run out of space on the drive holding the first file and you need to keep things running. It should only be a temporary fix though.
The other reason to have multiple log files is if you need a log file bigger than 2TB.
Having multiple log files provides no real benefit for processing. When transactions are processed, they have to fill one log file before they move on to the next log file. You cannot use both in parallel like with data files.
That said, if you do not have a space issue on one of the volumes, then remove the second log file.
I would not necessarily shrink the log file. Leaving the log file at a preset size is a good thing. You want to make sure it is preset to the right size though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 21, 2014 at 4:16 am
Thank you for your input.
June 21, 2014 at 4:16 am
Thank you.
June 21, 2014 at 8:39 am
smtzac (6/21/2014)
Thank you.
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply