INF: Transaction Log Still Full After DUMP TRAN WITH NO_LOG

ID: Q184499

The information in this article applies to:

SUMMARY

After receiving an error message 1105 indicating that the transaction log is full, you may need to issue the following command to truncate the transaction log:

dump transaction <db> with no_log

where <db> is the name of database indicated in the error 1105 message.

This article is to document further steps to take if the above command does not clear the transaction log.


MORE INFORMATION

If, after running the above command, the transaction log is still reporting no space available, see the following article in the Microsoft Knowledge Base to ensure that this is not due to a reporting error:

Q183100 : PRB: Incorrect Log Size Reported in SEM or Performance Monitor

After reviewing the above article, if you have determined that the log is indeed full (99.99 percent), check SQL Server Books Online or the following article in the Microsoft Knowledge Base to see why the transaction log cannot be cleared out:
Q110139 : INF: Causes of SQL Transaction Log Filling Up

Also, note the following, as stated in the SQL Server Books Online:
NOTE: If dumping the transaction log doesn't appear to truncate the majority of your transaction log, you may have an open transaction in the log. To determine whether a transaction has been left open, use DBCC OPENTRAN.

First, run the following script to determine if you have any open transactions: 

   use <databasename>
   go
   dbcc opentran(<databasename>)

This may return information similar to the following: 

   Transaction Information for database: pubs
   No active open transactions.
   Replicated Transaction Information:

           Oldest Distributed RID     : (0 , 0)
           Time Stamp                 : 0001 0000000E
           Oldest Non-Distributed RID : (589 , 26)
           Time Stamp                 : 0001 0000363E

   DBCC execution completed. If DBCC printed error messages, see your
   System Administrator.

Notice that there are no open transactions, but there are rows of additional information in the form of Replicated Transaction Information. This signifies that the database was or is marked for replication. If you are getting replication information, verify that the Oldest Distributed Transaction RID is close to the Oldest Non-Distributed RID. This will be different if you have replication currently running in this database and the amount of difference will be based on a variety of variables dealing with replication that is outside the scope of this article. The thing to remember is that the database is marked for replication. Specifically, there are records in the transaction log that are marked for replication.

IMPORTANT: If you see Replicated Transaction Information you should first determine why the transactions marked for replication are not being distributed. You should only continue after determining that this database should not be participating in replication. For more information, see the SQL Server Books Online or the following article in the Microsoft Knowledge Base:
Q89937 : INF: Getting Started with Microsoft SQL Server Replication

Next, check to see if this database and/or server is set up for replication. Run the following script to verify that the server has replication installed: 

   use master
   go

   sp_helpserver

This will generate output similar to the following: 

   name                        network_name                  status      id
   ------------------------------------------------------------------------
   CYGNUS                        CYGNUS                      pub,sub,dis  0

The status displays the role of this server in replication. If the status is empty, the server is not participating in any aspect of replication. Be aware that the server can be participating in replication, but you need to ensure that the database with the full log and the objects in that database are not part of any replication. 

   select name, category
     from master..sysdatabases
     where name = '<databasename>'
   go
   use <databasename>
   go
   -- The query will return all objects that are published (32) or
   -- replicated (64)
   select name, category
     from sysobjects
     where type = 'U'
       and category & 32 = 32
       or category & 64 = 64

This will generate output similar to the following: 

   name                           category
   ------------------------------------------
   pubs                           0

   (1 row(s) affected)

Part of the information being displayed in the category field is how this database is participating in replication. If the category is 0, this database is not being published.

The category field says more about the objects. If you get a zero result set as shown below, none of the tables are involved in replication. 

   name                           category
   ------------------------------------------

   (0 row(s) affected)

Again, the thing to remember is you are not trying to remove replication; you are only verifying that you do not have any objects in this database that are marked for replication. If you do, you should not be trying to clear the transaction log; you should be trying to determine why transactions marked for replication are not being distributed.

If replication is not being done on this database, you may still have distributed transactions in the transaction log. It may be that replication was installed on this server at one time, but replication was not completely removed. To resolve this problem, run through the following script: 

   use master
   go
   sp_configure 'allow', 1
   go
   reconfigure with override
   go
   begin tran
   update master..sysdatabases set category = 1 where name =
   '<databasename>'

   -- verify that the correct row has been changed by running
   -- select name, category from sysdatabase where name = '<databasename>'
   -- if the correct row is changed then run the following
   commit tran

Now you have the database marked as being published, so you can mark any transactions in the log that are marked for replication as being distributed. 

   sp_repldone 0, 0, NULL, 0, 0, 1

This stored procedure is documented in SQL Server Books Online. The following is what the command does:
When page is 0, row is 0, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid (for example, a published table has been dropped) and you want to truncate the log. For example:

After the stored procedure is finished running, you should be able to dump the transaction log: 

   dump tran <databasename> with no_log

Then you can verify the state of the log by running the following command: 

   dbcc checktable(syslogs)

The transaction log should now be empty.

NOTE: Because you have dumped your transaction log, the log is now invalid. You should perform a complete backup of your database. Refer to SQL Server Books Online on how to perform backup and restore operations.

Finish cleaning up the catalog information by running the following statements: 

   -- Set any object marked for replication as not published or replicated
   update sysobjects set category = category & ~32
   update sysobjects set category = category & ~64

   use master
   go
   -- Set the database as not published
   update sysdatabases set category = 0 where name = '<databasename>'

   sp_configure 'allow',0
   go
   reconfigure with override
   go

Additional query words: tran duplicate copy 


Keywords          : SSrvGen 
Version           : WINNT:6.0,6.5
Platform          : winnt 
Issue type        : kbhowto kbinfo

Last Reviewed: April 10, 1999