0

I hope someone can help. Please be gentle, this type of admin is not what I usually do.

I have inherited a database which is set up to do a FULL database backup at 02:00 every day. The transaction log is huge (almost 1TB) and has never had any backups (hence it is growing). The database is 172GB. The Logs and database are on different drives, as are the backups.

We would like to put a new maintenance plan in place with 1 FULL backup every week, 4 differential backups each day, and transaction log backups every 15 minutes.

The size of the transaction log is worrying me. We don't have a drive with that space in order to back it up.

Can I set the backup type to Simple, delete the transaction log, change back to Full, then set up the new jobs to do the differential and log file backups? Or will that break something? Is there a proper / better way to deal with this transaction log - I'm worried we will run out of space soon on the drive.

Very happy to provide more info if needed. I wasn't sure what would be pertinent at this point.

Thanks in advance.

Improve this question
3
  • backup tran to NUL is fewer steps. same results as switching full/simple/full
    – StrayCatDBA
    Jul 24 at 14:17
  • Hi StrayCatDBA - thanks for the quick response. Can I do a backup tran to NUL while users are using the database, or will it need to be done out of hours? If I do this backup, can I then shrink the Transaction log and then work out the plan as above?
    – Jubs666
    Jul 24 at 14:41
  • Yes, you can backup the log (to nul if you wish) while users are using the database. But all the data in the hige ldf file will be read and then "written" to nul. It is better to set recovery model to simple (which mean SQL Server don't have to read all those log records) and then back to full. Anyhow, yes, then just shrink the ldf file to a reasonable size.
    – Tibor Karaszi
    Jul 26 at 12:44

1 Answer 1

Reset to default
0

First of all look at

select name, log_reuse_wait_desc from sys.databases

to exclude anything else other than LOG_BACKUP. If it's LOG_BACKUP sure go ahead or run backup to null as StrayCatDBA suggested.

4 differential backups each day

Really bad idea. Differential backups are incremental so every page changed will be copied up to 4*7=28 times.

Instead figure out your RTO: how long should it take to restore your database. If you can make it with log backups restore only - don't bother with diffs at all.

Think about automation of log backup restore - manual creation of scripts with up to 4 * 24 * 7=672 log restore commands are error prone

Look at sp_DatabaseRestore or other custom made solution. https://www.brentozar.com/archive/2017/03/databaserestore-open-source-database-restore-stored-procedure/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.