January 25, 2008 at 7:59 am
We have a transaction log that will not trunctate, even after a full backup or a full log backup. The database is in 'full' recovery mode. In addition, when a log backup is performed....it seems to only backup the 'active' portion of the log.
any ideas???
January 25, 2008 at 8:44 am
The log does truncate after a successful portion and it does remove the active portion, but are you expecting the file size to change? It will not.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 25, 2008 at 8:50 am
Do you have replication running (transactional or merge?) Full backups never truncate the transaction logs. Only log backups truncate the inactive portion of the log.
What are you checking to see the log's truncation?
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
January 25, 2008 at 9:01 am
u can use DBCC SHRINKFILE to truncate log, if just log truncation is the mission
January 25, 2008 at 9:04 am
here is the detail of what occurs.
we have our database that is in full recovery mode. a transaction log backup (lets say the log is 2 gb) is conducted at noon everyday. when the log completes...it will backup 2gb, but does not release the occupied space, it still read 2gb of active log. On subsequent backups...the backup only backups the 'active' portion of the transaction log (lets say 500mb)....but again it does not release its occupied space...and will now read that the log size is 2.5 gbs...
i hope this made clearer
January 25, 2008 at 10:08 am
ok...i issued a truncate command from query analyzer...and here is what i get....
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.
now, im not replicating this database (yet)....
January 25, 2008 at 10:16 am
Can you run DBCC LOGINFO and DBCC OPENTRAN and post the output?
Has the database been published before? Or restored from a backup of a published database?
January 25, 2008 at 10:17 am
Well - it seems to think that you are, which is why the error is there. Sounds like you started to set it up, but haven't finished, so you might need to back out what you've done so far.
Meaning - either disable the replication until you're ready to do it, or follow the instructions it gave you as far as using sp_repldone.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 25, 2008 at 10:19 am
here is the info
Transaction information for database 'JDE_PRODUCTION'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (597947:274:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This database was restored from a backup when we had to rebuild our server. And at the time, it was being replicated.
January 25, 2008 at 10:37 am
Then check this: http://support.microsoft.com/kb/317375 and this:
http://support.microsoft.com/kb/198514/
Basically you have to publish the database somewhere on a server which has Publishing enabled and then unpublish it if you want.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply