Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.
The T-SQL being run in each case was
Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:
BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD
5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,
BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db')
RESTORE VERIFYONLY FROM <device> WITH FILE = @.i
This is then backed up to tape each night.
Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.
If I issue a
BACKUP LOG <database> to <device> WITH INIT, SKIP
then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?
Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?
Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?
Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated!
Matt
By default in the backup database command if you do not specify anything it will append ,eg...
backup database xxxx to disk='F:\test\xxxx.bak' and check the bak file size
now again reissue the same command,
backup database xxxx to disk='F:\test\xxxx.bak' and once again check the bak file size.......it will be double the size
next time issue the same command using WITH INIT option,
backup database xxxx to disk='F:\test\xxxx.bak' WITH INIT now see the bak file size it will be the same size as in 1st case.........if you do not specify anything SQL server will use the default With NOINIT option..........so you need to specify them explicitly if you needed to overwrite........
the log gets truncated if you specify the command,
backup log dbname with truncate_only.........this command will be depreciated in future versions........once you issue this comand, all the committed transactions will be rolled forward and written to data file and uncommitted trans will be rolled back.......
since you have a mission critical db i suggest you go for Log shipping or Database mirroring in sql 2005.........instead of relying on your db backups.........if you have sufficient disk space go for full backup and if you feel the db is important and if you want to have db consistency you can have tran log backups and differential backups else not required........
|||Am I right about when the transaction log should get truncated - i.e., on a full database backup?|||You are right, that the transaction log should be truncated after a full backup. However, note that the contents of the log will be truncated and the physical file will maintain its size. Therefore, you may see a large transaction log file although it won't be full. In order to reduce the size of the file, you'll need to run some form of SHRINKFILE operation.
HTH!
