August 25, 2008 at 5:34 am
Dear All,
I have createda job for taking the backup of our database. It also deletes the backup files older than 10 days.
But I have not done anything for the LOG files. Can anyone guide me how to manage the LOG files, I mean how to take the backup of LOG and how to clear it. Also how often to take the LOG backup idealy etc;
Thanks in advance,
Santhosh Nair.
August 25, 2008 at 6:05 am
For all of my databases, I do two things: 1 - backup the transaction log and then move it to another server. 2- Truncate the log to conserve disk space. See the code below (substituting your database name in the code).
Note the size of the logfile after performing the DBCC truncate is 128. Choose your logfile size keeping in mind how much AUTOGROW you might want to avoid be presizing your logfile.
--
-- SQL BASED BACKUP
-- BackupLog.sql
-- DATE VER PROGRAMMER REASON FOR MAKING CHANGES
-- 03/07/04 1.0 JWS Initial development
--
-- Check for backup currently running
declare @RET as varchar(15)
select @RET = cmd from sys.sysprocesses where cmd = 'BACKUP DATABASE'
if @RET = 'BACKUP DATABASE'
BEGIN
RAISERROR('Database Backup already in progress. Skipping transaction log backup', 16, 1)
RETURN
END
-- Backup Transaction Log
BACKUP LOG [YOUR_DATABASE_NAME] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\YOUR_DATABASE_NAME.TRN
-- Truncate transaction log
dbcc shrinkfile(YOUR_DATABASE_NAME_log, 128)
August 25, 2008 at 6:38 am
For performance it is better to NOT shrink the LOG-file. It takes lots of I/O for the log-file to expand each time. When a full- or logfile-backup is taken, completed trancactions are cleared from the log-file but the diskspace is reserved. New entries will be added in this reserved space.
It depends on your business requirements how often a (log-)backup has to be taken. When the database is heavaly used and log-file is growing rapidly, you must decrease the time between backups. This will prevent the log-file from growing too large.
Keep in mind that space in a log-file can not be free'd after the start of any uncommitted transaction.
August 25, 2008 at 8:47 am
Thanks for guidance..
August 25, 2008 at 10:33 am
HanShi (8/25/2008)
For performance it is better to NOT shrink the LOG-file. It takes lots of I/O for the log-file to expand each time.
In addition it causes internal log fragmentation (lots of little virtual log files) which can make the backups slower.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply