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?

3
  • Hi sdouble. You've got some answers already, but a question to clarify your situation though. Is the transaction recovery model FULL or SIMPLE? If it is FULL (as I expect), did the person(s) you inherited this database server from tell you whether they used this recovery model on purpose or not? A FULL recovery model is used to specifically be able to restore to a point in time in case a database file is corrupted, or in case a "rogue and malicious" script was run e.g. deleting/masking data, ...(there are other reasons for FULL). Perhaps the database should use SIMPLE recovery model after all?
    – TT.
    Oct 16 at 14:41
  • You should be able to perform the backup by targeting non-local media, such as NAS - or even more fanciful suggestions, e.g. if you have a somewhat modern Android phone with 256GB+ storage then plug it in via USB and mount it on the server:.
    – Dai
    Oct 18 at 4:05
  • In the spirit of always asking the obvious questions first, why not simply buy some extra storage? A new drive to accommodate that much data will cost you under $100. Oct 18 at 18:00
19

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.

0
14

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:
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;
2

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://docs.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver15&tabs=SSMS

1
  • I had a comment on one of the answers above suggesting that the log might be mostly unused but it got deleted before we were really able to talk about it enough to incorporate it into an answer. Oct 22 at 19:18
0

You can do the backup to a network drive.

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 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 at 4:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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