A "Database log truncated" error is logged in the Event Log when you try to back up the transaction log (818202)

MORE INFORMATION

You might see the message only when you have the database in full recovery mode, and you try to back up the transaction log by using either the NO_LOG or the TRUNCATE_ONLY options. You may not receive the error message when you do not have the database in full recovery mode, or when you do not use the NO_LOG or TRUNCATE_ONLY options when you back up the transaction log.

Note The NO_LOG or TRUNCATE_ONLY options truncate the transaction log. When you use these options, you might not receive a full database backup.

To view the SQL Server error log, you can use the sqldiag command prompt utility. The sqldiag utility gathers and stores both diagnostic information, and the contents of the query history trace (if running). By default, you might find the output file SQLDiag.txt in the following location:

<Installation drive>:\Program Files\Microsoft SQL Server\MSSQL\LOG

The output file includes the text of all the SQL Server error logs.

For more information about how to use the sqldiag utility, visit the following Microsoft Web site:

sqldiag Utility

Steps to Reproduce the Behavior

To reproduce the behavior, follow these steps:

1. Open SQL Query Analyzer.
2. Create a new database, and name it test. For example:

   CREATE DATABASE test 
   GO

3. Set the database recovery mode to FULL. For example:

   ALTER DATABASE test SET RECOVERY FULL
   GO

4. Create a new table and name it testtab in the database. Enter some data in testtab. For example:

   CREATE TABLE testtab( numbers int )
   GO
   
   DECLARE @i int
   SET @i=1
   WHILE @i<=100
   BEGIN
   INSERT INTO testtab VALUES(@i)
   SET @i=@i+1
   END
   GO

5. Back up the database that is named test to a disk device. For example:

   BACKUP DATABASE test TO DISK = 'c:\Testdb.dmp'
   GO

6. Make a few changes to the table that is named testtab in the database that is named test.
   For example, delete some rows from the table testtab:

   DELETE FROM testtab WHERE numbers<=10
   GO

7. Back up the transaction:

   BACKUP LOG test TO DISK = 'c:\Testlog.dmp'
   GO

   
   Note Check the Application Log in Event Viewer. No error message appears for the backup.
8. Make some more changes to the data, and then back up the transaction log again by using the NO_LOG option. For example:

   DELETE FROM testtab WHERE numbers<=20
   BACKUP LOG test WITH NO_LOG
   GO

   
   Note Check the Application Log in Event Viewer. You can find the error message for the backup.
9. Create a full database backup for the database that is named test:

   BACKUP DATABASE test TO DISK = 'c:\testdb.dmp'
   GO

   
   Note Check the Application Log in Event Viewer. No error message appears for the backup.