Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.
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 .
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MCP MCSA Microsoft Certified Partner & Solutions Ass… 377 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Truncated 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.
And 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?