Vikram Kamath

November 11, 2005

Backup Log Failing: SQL Server 2000

Filed under: SQL Server — Vikram @ 17:04

We are in the process of upgrading a database schema from one version to a later version. In order to do that we had to take a backup of the database that was under Transactional Replication and restore it on a different system to do the upgrade. We did the backup and the restore without any problems. The next step was to modify the schema and data in the database to bring it up to the new version. When we started executing our upgrade scripts we realized that the transaction log was growing rapidly and was eating up a large amount of disc space. At this point anyone would say that we should truncate the log and we are alright. I did the same. I issued the following command so as to truncate the log and make some space.


When I executed the command SQL Server came up with the message

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

This started a wave of questions in my mind. Why is the database still behaving as if it was under replication? Is there any way to truncate the log file? Does the BOL (Books Online) have any clues? After much efforts to find a solution, I ended up posting it on the Microsoft SQL Server Replication Newsgroup.

As a reply to my query, I was asked to issue the following statement before I truncated the log

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

But, when I executed this statement SQL Server came up with another error:

Server: Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
The database is not published.

I was finally asked to try one more thing. I was surprised to see that this solution did work. So here it is:

  1. Publish the database in Transactional Replication.
  2. Issue the following command:
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
  3. Truncate the log using the following command:
  4. Drop the publication.

This was so simple, but I could not imagine doing that. Thanks to Paul Ibison. For more answers on replication visit

Thanks Paul.


November 9, 2005

Passed Driving Test

Filed under: General — Vikram @ 21:14

I am finally a fully qualified driver for the roads in the UK. I passed my practical driving test today. The most important thing about it is that I passed it in one go. First attempt and I passed. This is something that I am very glad about.

Next thing… Buying a car!!!!


Blog at