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.

BACKUP LOG DatabaseName WITH NO_LOG

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:
    BACKUP LOG DatabaseName WITH NO_LOG
  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 http://www.replicationanswers.com/Default.asp

Thanks Paul.

Cheers!!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: