Shrink Log File

• Aditya-313343

  Right there with Babe

  Points: 768

  More actions

  September 7, 2010 at 6:27 pm

  #226661

  Hi,

  I am trying to shrink log file for a testing database.This database will be restored with production database periodically.When i tried to create backup of the log file

  Processed 0 pages for database 'hcbeta', file 'HC_log' on file 6.

  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.

  BACKUP LOG successfully processed 0 pages in 0.423 seconds (0.000 MB/sec).

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

  Resulted with error:Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

  So tried to publish the database using EXEC sp_dboption 'hcbeta', 'Publish', 'true'

  resulted error mesage:Msg 15242, Level 16, State 1, Procedure sp_dboption, Line 138

  Database option 'Publish' is not unique.

  and retrived duplicate_options as "merge Publish" ,"Publish"

  Plesae help me in shrinking my database log file.

  Thanks,

  Adi.

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 8, 2010 at 4:57 am

  #1217839

  i am not good while dealing with replication but i guess below script might help you.but read it carefully

  use database_name

  go

  create table shrinkfile(

  col1 int,

  col2 char(2048)

  )

  dump tran database_name with no_log

  dbcc shrinkfile(logical_name_of_log, 50, TRUNCATEONLY)

  go

  set nocount on

  declare @i int

  declare @limit int

  select @i = 0

  select @limit = 10000

  while @i < @limit

  begin

  insert into shrinkfile values(@i, 'Shrink the log...')

  select @i = @i + 1

  end

  -- if needed

  update shrinkfile

  set col2 = 'Shrink the log again...'

  --Clean up

  drop table shrinkfile

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 8, 2010 at 5:18 am

  #1217846

  The solution to this is odd, but does work.

  Create a transactional replication publication and publish a single article.

  Stop the log reader

  Run sp_repldone

  Drop the publication that you just created.

  That should remove references to replication. Run DBCC OPENTRAN to check. There should be no references to distributed and non-distributed LSNs.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass