Microsoft KB Archive/818202

= A &quot;Database log truncated&quot; error is logged in the Event Log when you try to back up the transaction log =

Article ID: 818202

Article Last Modified on 5/16/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



SYMPTOMS
When a database is in the full recovery model, and you try to back up the transaction log files with either a NO_LOG or TRUNCATE_ONLY option, the following message is logged in the Application Log of the Event Viewer:

Event Type: Error Event

Source: MSSQLSERVER

Event Category: 6

Event ID: 17055

User: 

Computer: 

Description:

18278 :

Database log truncated: Database: .

Note The same message is also logged in the SQL Server 2000 error log.

If you use SQL Server 2005, the following message is logged in the Application event log:

Event Type: Error

Event Source: MSSQLSERVER

Event Category: (6)

Event ID: 8309

Description:

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log. For more information, see Help and Support Center at http://support.microsoft.com.



CAUSE
This warning message may be logged because the NO_LOG and TRUNCATE_ONLY options of the BACKUP statement truncate the transaction log files, and you might need transaction logs for the full recovery of the database.



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:

:\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:  Open SQL Query Analyzer.  Create a new database, and name it test. For example: CREATE DATABASE test

GO   Set the database recovery mode to FULL. For example: ALTER DATABASE test SET RECOVERY FULL

GO   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   Back up the database that is named test to a disk device. For example: BACKUP DATABASE test TO DISK = 'c:\Testdb.dmp'

GO </li>  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 </li>  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. </li>  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. </li>  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. </li></ol>

<div class="references_section">