Queries re SQL Backup, log files and Log Shipping

Question

I was given a MS-SQL server backup .bak file to restore on my machine which was about 25mb in size , but required 10Gb of free disk space because the log file was that size, which indicates that they log file is not getting backed out nor truncated. (Note that the 10Gb log file must be mostly empty otherwise the .bak file would be a lot bigger than 25mb)

This database is for a little used (and possibly not very important) database, and the same site has another much more important and much larger database, (size somewhere in the region of 5 Gb) which has a smaller log file, so I am assuming the log file for that database is getting trimmed.

I am told that the site uses Log shipping to backup their main database to a second server, but I'm not sure if the smaller database is also being backed up.

So, I am guessing that either they are only using log shipping for the main database or they are only backing up the main database.

Which raises a few questions

1. Is there any sort of backup that a user/administrator on site can perform that interferes with or breaks log shipping?
2. If you use log shipping, is that enough to ensure the logs don't continually grow, or do you still need to backup the logs (using for example a maintenance plan)?
3. This one isn't too important, but if I ever get a backup that needs more space for a log file than I have available, is there any way of restoring it without the log file or without fixing the issue at source and getting a new backup.

Answer 1

SQL doesn't actually resize the log after a log backup - instead it just changes internal metadata to mark portions of the log (called Virtual Log Files or VLFs) that can be re-used. This process is called log truncation. So, your logfile will grow if there are no free VLFs but it will never shrink unless you manually tell SQL Server to do so.

As the log size of the database is 10Gb, restoring the backup will also create a 10Gb log file regardless of the amount actually in use. The same would apply to any data files.

You can shrink the log file using the 'DBCC SHRINKFILE' command - although unless you are certain that the log will not grow to 10Gb again then I recommend against doing it (there is a performance impact of growing the file). Long running transactions or database maintenance activities can use up a lot of transaction log so it is worth finding out why the log is at 10Gb before shrinking.

To answer your 3 questions:

Is there any sort of backup that a user/administrator on site can perform that interferes with or breaks log shipping?

Yes - if you break the backup chain by backing up the log to a destination outside your log shipping configuration, resulting in the backup not being applied to the secondary server. You should use COPY_ONLY backups in this situation as these will not break the log chain. This only applies to log backups as full backups do not truncate the log.

If you use log shipping, is that enough to ensure the logs don't continually grow, or do you still need to backup the logs (using for example a maintenance plan)?

That should be fine unless there is something else which stops the log being truncated, such as database mirroring, long running transactions, replication or even database snapshot creation. The way to find out why your log is not being truncated is to look at the log_reuse_wait_desc column in sys.databases.

It's also possible (but unlikely) that the backup job for log shipping is taking backups using COPY_ONLY/NO_TRUNCATE which would mean the log file will continually grow once full.

This one isn't too important, but if I ever get a backup that needs more space for a log file than I have available, is there any way of restoring it without the log file or without fixing the issue at source and getting a new backup.

If you shrink the log file then take a full backup, when you restore it SQL Server will create the database using the smaller log file.

If you can't shrink but you are restoring to a development/test server then you could mount some temporary storage and use 'WITH MOVE' on your RESTORE statement to move the log file to that drive, then shrink the log and move the log file off the temp storage (caveats about whether you should shrink the log or not still apply!).