May 5, 2010 at 7:15 am
In our environment , we have 3 log files for TempDB. my database director asked me to removed 2 tempdb file. he sail that we are fine with one tempdb log file. i know that since log files has sequential writes and we are keeping it on separate disk (enough space) so there is no requirement to keep other 2 log files.
but is there any scenario that more than one log file can help us ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 5, 2010 at 7:21 am
Bhuvnesh (5/5/2010)
In our environment , we have 3 log files for TempDB. my database director asked me to removed 2 tempdb file. he sail that we are fine with one tempdb log file. i know that since log files has sequential writes and we are keeping it on separate disk (enough space) so there is no requirement to keep other 2 log files.but is there any scenario that more than one log file can help us ?
It used to be the case that MSFT recomended a one TempDB file (MDF not LDF) per processor core. For log files, I do not know of any reason to have several, unless you do a lot of work in TempDB and the load justified adding the additional files.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
May 5, 2010 at 7:25 am
GregoryF (5/5/2010)
Bhuvnesh (5/5/2010)
In our environment , we have 3 log files for TempDB. my database director asked me to removed 2 tempdb file. he sail that we are fine with one tempdb log file. i know that since log files has sequential writes and we are keeping it on separate disk (enough space) so there is no requirement to keep other 2 log files.but is there any scenario that more than one log file can help us ?
It used to be the case that MSFT recomended a one TempDB file (MDF not LDF) per processor core. For log files, I do not know of any reason to have several, unless you do a lot of work in TempDB and the load justified adding the additional files.
if we forget MSFT for a while , how multiple log would behave ? any added advantage ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 5, 2010 at 9:15 pm
There is no advantage of having multiple log files in SQL 2005 and later. The files are not used at the same time. Second file will be used after the first one is filled out. I would add the second log file only if there is no space to grow for the first log file (to have the second file on different disk drive). he Microsoft recommendation is for SQL 2000.
May 6, 2010 at 12:06 am
magasvs (5/5/2010)
I would add the second log file only if there is no space to grow for the first log file (to have the second file on different disk drive). he Microsoft recommendation is for SQL 2000.
AGREE .. but having multiple files can give us performance edge anytime ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 6, 2010 at 2:27 am
No. They are used sequentially, first one, then the second. There's no striping of log writes (which you give you an improvement if there was more than one log file)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2011 at 4:01 am
---checking file location
select * from sys.database_FILES
-- Empty the data file.
DBCC SHRINKFILE (templog1, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE tempdb
REMOVE FILE templog1;
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply