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?

migrated from stackoverflow.com Dec 31 '17 at 4:40

This question came from our site for professional and enthusiast programmers.

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.

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.

  • 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 Jan 2 '18 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

Your Answer

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