November 6, 2008 at 1:50 pm
clive (11/6/2008)
2.If you truncate a log file which is usually around 2GB, but is now 7GB due to bulk inserts, truncating it is recommended (unless you have plenty disk space and don’t mind the extra time it takes for backup).
This doesn't warrant a truncate. If the log size is larger than you want it, the space isn't used (which it won't be after the next log backup) and you want to shrink it, then just shrink it. Empty space within the file doesn't affect backup time. Only the used space does that.
If you don't like your bulk inserts taking up 5 GB of log space then set the recovery model to bulk logged. Then the bulk operations minimally log and they won't put 5 GB into the log in the first place. Then you don't have to truncate it and shrink it.
BACKUP LOG [AdventureWorks] WITH NO_LOG
-- This step is to clear & set the size of physical log file.
ALTER DATABASE [AdventureWorks]
SET RECOVERY SIMPLE
Those two steps are quite redundant. Setting the DB to simple and doing a checkpoint will truncate the transaction log. No need to do both.
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
November 6, 2008 at 3:06 pm
BACKUP LOG [AdventureWorks] WITH NO_LOG
FYI: This operation is deprecated
* Noel
November 8, 2008 at 3:25 am
noeld (11/6/2008)
BACKUP LOG [AdventureWorks] WITH NO_LOG
FYI: This operation is deprecated
So is Backup log with truncate only and both have been completely removed in SQL 2008.
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 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply