Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
cleaning up T Log backups at the end of a full backup
In prod full backup starts at 12.02 and completes at 4.00am .our full backup have all data in it and also getting t-log backup files same time .but the transaction log doesn't get truncated by backups .we can delete all t-log files from 12.02am -11.30 pm .or 11 pm . for cleaning up T Log backups at the end of a full backup, what would the TSQL look like? My full backup job query is :
DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10),
SYSDATETIME(), 112 ) + N'.bak';
BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10
DECLARE @backupSetId AS INT
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id
= ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' );
IF @backupSetId IS NULL
BEGIN
RAISERROR( N'Verify failed. Backup information for database ''MYDB'' not found.', 16, 1 )
END
RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId
how do i add cleaning up T-log backups at the end of a full backup ?
we are getting t-log backups every 24 minutes and getting full backup at night .
DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10),
SYSDATETIME(), 112 ) + N'.bak';
BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10
DECLARE @backupSetId AS INT
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id
= ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' );
IF @backupSetId IS NULL
BEGIN
RAISERROR( N'Verify failed. Backup information for database ''MYDB'' not found.', 16, 1 )
END
RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId
how do i add cleaning up T-log backups at the end of a full backup ?
we are getting t-log backups every 24 minutes and getting full backup at night .
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In prod full backup starts at 12.02 and completes at 4.00amThat's only for one database? How big is the database?
but the transaction log doesn't get truncated by backupsTruncated or shrunk? Transaction log backups truncates the tlog files (zeroed the file) but if what you want is really to shrink the file then you'll need to explicitly run a DBCC SHRINKFILE command after the backup.
we can delete all t-log files from 12.02am -11.30 pm .or 11 pm . for cleaning up T Log backups at the end of a full backupAnd what if for some reason you'll need to restore to that point in time? How you'll do it without having the necessary backup files?
yeah only for one database .Database size is about 700 GB .
i meant Truncated .log files are still big while full backup is running .
for now we are having space issue .cant keep solid two full backups .full backup job is failing 3/4 times in week .most of the time we are cleaning T-log files manually after full backup completed .
For temporary solution i thought good idea is to delete t-logs files after full backup completes .so the way we gonna have full backup .
i meant Truncated .log files are still big while full backup is running .
for now we are having space issue .cant keep solid two full backups .full backup job is failing 3/4 times in week .most of the time we are cleaning T-log files manually after full backup completed .
For temporary solution i thought good idea is to delete t-logs files after full backup completes .so the way we gonna have full backup .
yeah only for one database .Database size is about 700 GB .I can't imagine in these days a backup of 700GB database taking 4h to complete. Is this an old system? If not, it's something running at the same time that may slow down the backup time? I would expect something like less than one hour to complete a full backup for that database.
for now we are having space issue .cant keep solid two full backupsHow much space to you have for backups? How your backup plan looks alike? You may need to review the backup plan to something better.
For temporary solution i thought good idea is to delete t-logs files after full backup completesWhy not add more disk or use some network share for backups?
yeah its pretty old server .SQL server 2008R2 .we do not use any third party software .
drive is 2.18TB .problem is T-log and full backups are using same drive . our full day T-log backup size is almost 765GB .backup plan is full backup everyday and t-log every 24 minutes .do not do diff backup anymore because they told me it was taking same time as full backup and do not have much space to do .
i offered to manager to add more space .all i know its coming but do not know when .
drive is 2.18TB .problem is T-log and full backups are using same drive . our full day T-log backup size is almost 765GB .backup plan is full backup everyday and t-log every 24 minutes .do not do diff backup anymore because they told me it was taking same time as full backup and do not have much space to do .
i offered to manager to add more space .all i know its coming but do not know when .
yeah its pretty old server .SQL server 2008R2 .we do not use any third party softwareI managed for several years a SQL Server 2005 instance with a 11TB database and full backup ran around 12 hours, so almost 1TB per hour. The network and storage speeds are also important.
drive is 2.18TB .problem is T-log and full backups are using same driveCan't see big problem on that. Would be a problem if you're backing up to the same drive where database data and log files were stored.
backup plan is full backup everyday and t-log every 24 minutes .do not do diff backup anymore because they told me it was taking same time as full backupWas taking the same time but how about the space? Was less? And if you have a SQL Server 2008R2 you should be able to perform compress backups. In average will reduce the backup size and time to 3 times less.
By the way, you should assure that during the Full Backup period, no transaction log backup is launched, i.e. transaction log backups should run from 05:00AM to 11:00PM, leaving the remaining period to Full backup.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial