1

I have a database and it is in full recovery model. Transaction log file is restricted to 100 MB.

I inserted some rows to a table in the database. When I see the logspace used using dbcc sqlperf(logspace) it showed the log space used is 60 percent.

Next I did a full backup of the database and restored with new database name. When I see the logspace used using dbcc sqlperf(logspace) it showed the log space used is 9 percent.

Full backups do not truncate the log file. I do not understand why the space used by log files in these two DBs are not the same?

Improve this question
0

3 Answers 3

Reset to default
1

A full backup only backs up enough of the log to restore to the point in time of the backup. Only those committed transactions needed are included in the full backup.

Improve this answer
0

Generally speaking, in Full Recovery Mode, the transaction log is kept until it is truncated, or until it is backed up. I'd be willing to put money on the backup and restore of your db being what caused the log to use less space after the restore process.

Improve this answer
1
  • In Full Recovery Mode, only a "BACKUP LOG" will allow the transaction log to be overwritten (not a "BACKUP DATABASE"). Dan Guzman's response is the correct one.
    – Razvan Socol
    Commented Jan 2, 2018 at 15:18
0

Agree with Dan Guzman, To explain the point further a TechNet article explain how backup works:

https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx?f=255&MSPPError=-2147217396

Improve this answer

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.