November 19, 2009 at 6:20 am
Hi all - Recently our transaction log started to increase in size massively.
We run hourly tans log backups which are working fine, but the log is not being correctly truncated it seems I have tried to shrink the file but this has not worked either. I have run DBCC OPENTRAN and get the following:
Transaction information for database 'ukcrn'.
Replicated Transaction Information:
Oldest distributed LSN : (1290026:5600:4)
Oldest non-distributed LSN : (1290026:10715:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Which indicates to me that there are no open transactions currently there.
I have also run DBCC LOGINFO and can see a status of 2 on lots of my virtual logs
How can I tell what is causing the problem and how can I shrink the file in keeping with our recovery policy of hourly transaction logs?
Thanks
November 19, 2009 at 7:14 am
What recovery model? Are you doing log backups? Is the replication log reader running? (check the replication monitor)
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
November 19, 2009 at 8:08 am
Its in Full recover mode and we are doing hourly log backups which are still working. Its interesting that you mention replication as we recently setup a snapshot replication, however the log reader is not running at present.
I am thinking I may have to perform a backup with truncate_only. However if I do this will it throw my maintenance plan out of synch? Will I need to run my backup maintenance plan straight away again and just let my transaction log backups continue?
Thanks
November 19, 2009 at 8:15 am
Snapshot replication or transactional replication? Very important to know the difference.
Backup with truncate will do nothing for the bloated log if the problem is replication. It'll just break the log chain and result in all of your log backups failing from this point onwards. Not a good idea.
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
November 19, 2009 at 8:47 am
With replication, the log cannot clear until the transactions are sent across through replication. If they don't get marked as replicated, they stick in the log. Be sure the log reader is running and that replication is working properly. My guess is that's your issue if the log backups are working.
November 19, 2009 at 9:03 am
Well its a snapshot replication we have setup does the same reasoning apply?
November 19, 2009 at 9:08 am
Are you seeing a log reader agent? What do you get when you run the following on the distributor?
use distribution;
go
select * from MSlogreader_agents
Do you see a log reader agent for the database in question?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 19, 2009 at 9:14 am
I get nothing returned when I run that query.
Should there be a log reader agent for snapshot replication?
November 19, 2009 at 9:35 am
What do you see when you run select log_reuse_wait_desc,name from sys.databases for the database in question?
Check out this post and see if it helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 19, 2009 at 9:44 am
Ok just found out that a colleague setup transactional replication around the same time that we started to see an increase in the log. That publication does not exist anymore so is it possible that the transactions are stuck in there now? If so how do I resolve this?
November 19, 2009 at 9:52 am
If you see nothing in the output from the following query then you can disable replication for that database.
select * from syspublications -- will check if there are any publications on that database
If none then you can use sp_removedbreplication - details on the use of this procedure can be found HERE.
Please don't do this unless you are sure that there are no other publications on that database. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 19, 2009 at 12:58 pm
Kwisatz78 (11/19/2009)
If so how do I resolve this?
Providing there is no existing transactional replication publication....
Create a new transactional replication publication.
Run sp_repldone in the published database
Drop the newly created publication.
Run DBCC OPENTRAN and see if the replicated and non-replicated lines remain.
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
November 20, 2009 at 2:16 am
There is still a used publication in place so dropping replication isn't an option, I will look at using your method Gila and let you know how I get on.
Many thanks.
November 20, 2009 at 4:36 am
What kind of publication is that? You had stated no logreader agent and you were trying to create a snapshot (which should be easily recreatable). Merge?
Regardless read the documentation for sp_repldone before you go and execute it. http://msdn.microsoft.com/en-us/library/ms173775.aspx
Especially considering you have other replication for that database....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 20, 2009 at 5:05 am
Hi this has worked a treat. I actually didn't need to create a new publication but by running:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
and then waiting for the next transaction log backup I was able to shrink the file.
Thanks for all your help.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply