4

I recently inherited a poorly-managed SQL server.

One of the databases is in rough shape. Here are the details:

  1. The .mdf file is a modest 369MB.
  2. The .ldf file is a bloated 29.7GB.
  3. The recovery model is set to FULL.
  4. The data file is backed up nightly without issue, and is 365MB.
  5. The transaction log has NEVER been backed up.

I've restored the nightly backup to the same SQL server and poked around in it to make sure data looks to be intact. When I restore that nightly .bak backup file it recreates the .mdf and .ldf files at 369MB and 29.7GB, respectively.

Being decent with SQL, but not what I would call an expert by any means, I have a few follow up questions on this situation that I am hoping to get feedback on:

  1. How is a 369MB .bak file restoring both a 369MB .mdf file and a 29.7GB .ldf file?
  2. Any suggestions on what to do regarding the recovery model and bloated .ldf file? We are only concerned about doing backups once nightly - we don't need to do differential backups at this time - but it's something I will tackle later.
Improve this question
0

3 Answers 3

Reset to default
5

How is a 365 .bak file restoring both a 369MB .mdf file and a 29.7GB .ldf file?

It's not restoring the data from the log file, or backing it up, it's just creating it with the same size as it was when backed up. Check the free space and it should be 99% free.

Any suggestions on what to do regarding the recovery model and bloated .ldf file? We are only concerned about doing backups once nightly - we don't need to do differential backups at this time - but it's something I will tackle later.

If you are only worried about doing backups once a night then change the recovery model to simple. If you leave it in full you will need to perform log backups as well. Once you switch the recovery model or start performing log backups you could do a one time shrink to get back the space.

Improve this answer
1
  • Additionally, if you do shrink down the log file regardless if it's in FULL or SIMPLE recovery mode, keep an eye out for the LDF file to grow again. If it continues to grow quickly after you shrink it, then there are some HUGE transactions occurring and you will need to spend some time tracking down why the commits of huge transactions are so huge and get those tuned or split down into smaller transactions/batches. I've seen LDF files grow like this even in SIMPLE recovery mode because of poorly written queries from developers, report writers, and DBAs that don't understand this.
    – IT Thug Ninja
    Commented Feb 25, 2017 at 9:34
1

The fact that the backup is 365 MB while the transaction log is almost 30 GB and the database is in FULL recovery, and yet there's never been a transaction log backup is a bit perplexing. There may be something else going on that's allowing for transaction log reuse besides a backup.

If you want to double-check whether log backups are happening, you can do this from Management Studio. Right-click the database, and go to Reports, Standard Reports, Backup and Restore Events. Expand "Successful Backup Operations" and see if any log backups are happening.

If not, then check whether or not the database is regularly being changed from FULL recovery to SIMPLE and back to FULL again. This will show up in the SQL Server error log as something like Setting database option RECOVERY to SIMPLE for database 'Scratch'..

With that out of the way, you can shrink the transaction log to something more sensible - probably around 25-50% of the size of the database - using DBCC SHRINKFILE. You'll want to make a transaction log backup first to make sure active log space is marked for reuse. Next, run EXEC sp_helpfile to get the name of the transaction log file (the logical name in the name column, not the physical filename), then shrink it to an appropriate size, e.g. DBCC SHRINKFILE('Scratch_log', 128) (the size is given in MB).

Note that it may not fully shrink to the target size depending on what portion of the log file is in use. If that's the case, you'll want to run CHECKPOINT, then run another log backup, and DBCC SHRINKFILE again.

Then for ongoing maintenance, I would recommend leaving the database in FULL recovery, and adding a nightly transaction log backup just before the full backup. This way, you'll at least be able to recover the database in the event of a failure of whatever volume the data file is on (assuming the transaction log file is still safe), and also retain the ability to do point-in-time restores.

Improve this answer
-2

1) Backups are typically compressed, they will be less than the size of your data and the size of your log.

2) If you run a database in full recovery model you need to back up your transaction logs or they will grow indefinitely. If you don't care about transaction log backups you should switch back to simple recovery.

Improve this answer
0

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