April 2, 2009 at 9:14 am
I have two things to tell here
You would not be able to shrink the log file unless you truncate it. When the sys.databases "log_reuse_wait_dec" coulmn status is "LOG_BACKUP", then you should first take a log backup which truncates the log and then shrink the log file.
-- You cannot truncate a transaction log file in SQL 2005 or above versions. The only option is to change the recovery model if you are not concerned about point in time reocvery or log backups
Regarding the number of TempDB files and the Core CPU count, here is the fact
If you have
1 proc = 1 data file
2 proc = 2 Data file
4 proc = 2 to 4 data file
8 proc = 4 to 8 data file
16 proc= 8 data file
you are not going to gain any more performance having more than 8 Data files for TempDB irrespective of the higher number of CPU's
subban
April 2, 2009 at 9:40 am
Going back to more than one log files issue .. its true this will not improve performance , but real life is tougher than our recommendations .. yes, in enterprises its a truth, we need to have more than one log files to handle the disk space issues... (pls try to see the SA's over our head)
If you have space issue restrict log file growth in one disk and try to get more space on another disk (if at all required), this way you will gain some benefit as a DBA ... Behave like a cop and any application over utilizing your log space as culprit, ask them to restrict it ... optimize there code ... point the issue to there manager .. (whatever, as long as you job is safe ;-)) ...
April 2, 2009 at 10:09 am
Krishna Potlakayala (3/26/2009) You would not be able to shrink the log file unless you truncate it. When the sys.databases "log_reuse_wait_dec" coulmn status is "LOG_BACKUP", then you should first take a log backup which truncates the log and then shrink the log file.
No, you don't really need to take a transaction log backup. The goal is to Truncate or Shrink the transaction log as quickly as possible, in order to bring thedatabase back to usable state.
Once you switch the database to simple mode, the transaction logs can be discarded quickly. Below is the script I use to Truncate my over grown log files.
/*set to simple mode*/
USE [master]
GO
ALTER DATABASE [DB_Name] SET RECOVERY SIMPLE;
go
/*Shrink Log file*/
USE [DB_Name]
GO
DBCC SHRINKFILE (N'DB_Logfile_Name' , 0, TRUNCATEONLY)
GO
/*set back to full*/
USE [master]
GO
ALTER DATABASE [DB_Name] SET RECOVERY FULL;
go
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 2, 2009 at 10:31 am
shivaram challa (4/2/2009)
Krishna Potlakayala (3/26/2009) You would not be able to shrink the log file unless you truncate it. When the sys.databases "log_reuse_wait_dec" coulmn status is "LOG_BACKUP", then you should first take a log backup which truncates the log and then shrink the log file.
No, you don't really need to take a transaction log backup. The goal is to Truncate or Shrink the transaction log as quickly as possible, in order to bring thedatabase back to usable state.
But this will break the log chain when you change the recovery model to simple from Full. Yes, the goal is to shrink the log file but without breaking the log chain isn't it?
April 2, 2009 at 12:25 pm
Shivaram Challa (3/26/2009)One should monitor log file regularly and should be taking regular transaction log backups.
Now, if taking log backups isn't needed, then you can do a "dbcc Shrinkfile" on the logfile when the "Log_reuse_wait_desc" in the sys.databases status is "LOG_BACKUP". Or check the size of the logfile, and shrink it when deemed needed.
Krishna Potlakayala (4/2/2009)
But this will break the log chain when you change the recovery model to simple from Full. Yes, the goal is to shrink the log file but without breaking the log chain isn't it?
I completely agree with you that it would break the log chain. Now, the point I am trying to make is: "If we don't need to take a Transaction Log backup, Only then use shrink-log-file." If we don't need to take a transaction log backup, it really means that we don't need point-in-time recovery, so we don't have to worry about breaking the log chain.
In essence, one can monitor the sys.databases and never let the log-file-size grow out of hand.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 2, 2009 at 1:39 pm
shivaram challa (4/2/2009)
"If we don't need to take a Transaction Log backup, Only then use shrink-log-file."
Even if we need or we don't need tlog backup we can still shrink the log file. Shrinking the log file does not break the log chain. Only truncating the log file explicitly breaks the log chain and changing the recovery model to Simple breaks the log chain.
April 2, 2009 at 8:54 pm
Hi Krishna, I was talking about shrinking log file with TruncateOnly option. I thought it was apparent in the script I posted, perhaps not. For the record, we both are on the same page. The approach I am suggesting may not be the preferred option for every one, but it gets the job done (specifically to those who don't have to worry about point in time restore).
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 3, 2009 at 6:49 am
shivaram challa (4/2/2009)
Hi Krishna, I was talking about shrinking log file with TruncateOnly option. I thought it was apparent in the script I posted, perhaps not. For the record, we both are on the same page. The approach I am suggesting may not be the preferred option for every one, but it gets the job done (specifically to those who don't have to worry about point in time restore).
Hi Shivaram,
Yes, that's true. WE are on the same page. Hope OP has resolved his issue though
May 27, 2010 at 12:48 pm
Hi Everyone
How can I delete one of the transacton log or combine two into one, if I have multiple transaction log?
Thanks
May 27, 2010 at 12:50 pm
Hi Wilfred,
How can I delete one of the transacton log or combine them into one if I have two transact log?
Thanks in advance.
January 25, 2012 at 1:52 pm
I believe EMPTYFILE is the better idea then truncate in your script .
BOL says the following.
EMPTYFILE
Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.
So this is my code to share...
--find the file name
select * from sysfiles
--Shrink the file with EMPTYFILE option
DBCC SHRINKFILE('Some_logical_log_name',EMPTYFILE)
GO
--Backup transaction log on DB if model is FULL
--Remove the file
ALTER DATABASE [Some_DB] REMOVE FILE [Some_logical_log_name]
GO
January 25, 2012 at 2:05 pm
Oh and I would also add that the real reason you make a second log file yes is for space emergencies. But I'm not talking after the fact I'm talking before one occurs. By simply creating the second file on a different drive you can stop a DB from locking up. Many things can fill up a DB transaction log like a stuck backup job or rebuilding all indexes or some huge transaction that is running for days etc.
Making sure the 2nd log is on a different drive ensures those events wont bring down prod.
You can set up an alert that only shows when the second file autogrows then that way you know problems are in play.
Just me 2 cents..
January 25, 2012 at 2:44 pm
The EmptyFile option is solely for data files. It will have no effect on log files. Log records cannot be moved by anything, and logs aren't considered part of filegroups like data files are.
To remove a log file you need to wait until no part of the active log is within that file, then simply ALTER DATABASE ... REMOVE 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
January 25, 2012 at 7:37 pm
shivaram challa (4/2/2009)
Krishna Potlakayala (3/26/2009) You would not be able to shrink the log file unless you truncate it. When the sys.databases "log_reuse_wait_dec" coulmn status is "LOG_BACKUP", then you should first take a log backup which truncates the log and then shrink the log file.
No, you don't really need to take a transaction log backup. The goal is to Truncate or Shrink the transaction log as quickly as possible, in order to bring thedatabase back to usable state.
Once you switch the database to simple mode, the transaction logs can be discarded quickly. Below is the script I use to Truncate my over grown log files.
/*set to simple mode*/
USE [master]
GO
ALTER DATABASE [DB_Name] SET RECOVERY SIMPLE;
go
/*Shrink Log file*/
USE [DB_Name]
GO
DBCC SHRINKFILE (N'DB_Logfile_Name' , 0, TRUNCATEONLY)
GO
/*set back to full*/
USE [master]
GO
ALTER DATABASE [DB_Name] SET RECOVERY FULL;
go
In your previous post,, you also said something about when log backups aren't needed or maybe not wanted. If that's true, then save yourself a headache and leave the database set to SIMPLE recovery. Of course, no one should expect Point-in-time restores that way.
--Jeff Moden
January 26, 2012 at 1:37 am
p.s. 3 year old thread.
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
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply