6

Inherited a database server to maintain and found that the transaction log is 92 GB. My plan is to do the backup of that and get it brought back down to a regular size, then fix it. The problem is I don't have enough disk space on the machine to do the backup locally, then offload. Is there a way I can run the backup for a specified size or time period so I can do it in stages?

Improve this question
0

5 Answers 5

Reset to default
20

No, you can't do partial log backups to split this work into multiple files.

What you can do depends on your goals.

  • If you need to maintain the log backup chain, your only option is to provision more storage (locally, or via a network share) and do a complete log backup
  • If you don't need to maintain the log backup chain, and your goal is simply to get this file back to a manageable size, you have a number of options. The gist of what you need to do, though, is this:
    • "truncate" the transaction log (by switching to the SIMPLE recovery model, or doing a backup log TO DISK = 'NUL', or probably a number of other things)
    • shrink the log file to an appropriate size for your workload (using the SHRINKFILE command)
    • If you switched to the SIMPLE recovery model, you should switch back to FULL now*
    • Regardless of the approach you take to truncating above, you'll want to re-initialize the backup chain by taking a full backup and then a log backup, so that you are back in a good state, recovery-wise

*By the way, if you're intentionally not taking log backups, and don't plan to start, you should leave the database in the SIMPLE recovery model. Otherwise the log file will grow again.

Improve this answer
1
  • @AaronBertrand That's a good point, the log backup could be tiny!
    – Josh Darnell
    Oct 15, 2021 at 20:32
16

Assuming this is a standalone instance and the database isn't part of an AG, you should be able to abandon the current log chain and start over, by:

  • switch to simple recovery model
  • run CHECKPOINT; twice
  • DBCC SHRINKFILE(filename, 1);
  • ALTER DATABASE db MODIFY FILE(name = filename, size = <some appropriate size>);
    • I usually do this in multiple steps if I want the file to be big - e.g. first to 4GB, then to 8GB, etc.
    • you don't want to leave the file tiny, it just means it'll have to grow again, and log file growths are expensive and disruptive
  • switch back to full recovery model
  • take a full backup
  • create a schedule for taking log backups regularly enough that this doesn't happen again and you stay within RPO/RTO, but not so frequently that you have thousands of log files to manage
  • find some disk space where you can take backups not on this machine ... not much point of a backup if that machine goes away and takes both your database and the backups you took locally with it
  • read this post in full:
Improve this answer
10

No, there is no concept of a "partial transaction log backup".

Do you need to maintain the transaction log chain for restores, or otherwise hold on to the 92GB of transaction log backups?

If you want to just get rid of it & start over, and can confidently say "I don't need to do point in time recovery to a point in time before now!" then you can just start over.

You can effectively discard the whole thing by backing up to NUL:

BACKUP DATABASE MyDatabaseWithHugeTLog
    TO DISK = 'NUL:'; --Sends it to nowhere

Then you would be able to shrink the transaction log to make it be less huge:

USE MyDatabaseWithHugeTLog
DBCC SHRINKFILE(MyDatabaseWithHugeTLog_Log,10000);

Note, you may need to backup/shrink/backup/shrink more than once to get the log down to the desired size. Shrinking the log effectively just lops off the tail end of the file, so if there is a portion that is in-use, you may need to get that active portion to wrap around to the start to shrink to the desired size.

Then, you've ruined your ability to do point-in-time recovery, because we sent the log off to NUL. So you'll want to take a fresh full or differential backup so that you have a starting point for future restores.

BACKUP DATABASE MyDatabaseWithHugeTLog
    TO DISK \\SomeServer\MyBackupShare\MyDatabaseWithHugeTLog_YYYYMMDD_HHMISSMS;
Improve this answer
3

Some great advice above, but just a quick question before you do anything drastic - is the 92GB you quoted the size of the transaction log file, or is that the amount of space that is in use within a larger file?

If only a few GB is in use within the transaction log then the transaction log backup will be a lot smaller than you might be expecting.

Also you have the option to use backup compression which will help to reduce the transaction log backup file size.

Another option could be to backup the log to Azure Blob Storage to avoid having to add an extra volume, this could even be the basis of a long term backup solution for you:

https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver15&tabs=SSMS

Improve this answer
0
0

You can do the backup to a network drive.

Improve this answer
2
  • 1
    Yeah. If I can't do a partial backup locally, I'll end up getting a drive mounted temporarily to do this. It's hosted on Azure so it's easy enough, but then I have to get other people involved, requests, approvals, etc. It'll take a week+ to get done then, most likely, so was hoping to get it done in chunks.
    – sdouble
    Oct 15, 2021 at 18:33
  • @sdouble Don't you have some kind of operational / petty-cash budget in Azure? I doubt every expense needs a weeks' worth of approvals (if it is that bad, I suggest looking for a better employer) - you can provision a new multi-terbayte VHD, mount it, do the backup, download the data, and delete the VHD all within an hour (probably!) and won't cost more than a couple bucks.
    – Dai
    Oct 18, 2021 at 4:07

Your Answer

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

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