Transaction Replication causing continuos growth of tempdb database at Subscriber
-
quarta-feira, 30 de dezembro de 2009 23:39I am working with a SQL Server 2005 Enterprise environment that is using a Push Transaction Replication topology consisting of a Publisher, dedicated Distributor and Multiple Subscribers.
Recently I have come across an issue where by a number of servers subscribing to the Publication are experiencing continuous growth of the tempdb database and Replication has been identified as the probable cause. Interestingly the issue is only affecting certain servers although all are identical.
I reviewed the contents of the tempdb database to identify what was taking up the space and found that the majority of pages were allocated to neither internal extents or user object extents but instead to mixed extents.
I was then able to identify the sessions responsible for the majority of internal object page allocations and this was the replicaiton agent.
Has anyone come across this issue previously or can offer any suggestions?Many Thanks,
John
Todas as Respostas
-
quinta-feira, 31 de dezembro de 2009 18:32
-
quinta-feira, 31 de dezembro de 2009 20:17
http://social.msdn.microsoft.com/Forums/en/sqlreplication/thread/169f6b14-02d0-47ce-adab-73ac974c07b7
Thanks,
Krishna
Hi Krishna,
Thank you for your reply.
The content however does not appear to address this particular scenario. Tempdb use/growth is expected when working with SQL Server Replication however what is odd is that in this particular case the growth is significantly different, some 40GB, on seperate servers that Subscribe to the same Subscriptions. In other words, the tempdb growth is significantly different across several servers.
John Sansom | Blog: www.johnsansom.com | -
sexta-feira, 1 de janeiro de 2010 17:40Hi John,
tempdb growth can be due to following:
>> REindex on tempdb /online index.
>> replication snapshot
>> huge temp objects.
>> isolation level (snapshot)
>> big queries. cursor
in your case it looks like on
>> same subsriber you might have set different isolation level.
>> some server restarted recently (restart creates tempdb)
>> on that subsriber some queries or due to above reason by user can cause tempdb to grow.
I would monitor the subsriber closely, could be trace.
my one cent.
HTH
Vinay
Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay -
segunda-feira, 4 de janeiro de 2010 12:05ModeradorI have not noticed any significant increase in replication related tempdb growth after the snapshot has been applied.
One possible problem could be the way transactions are held as they are applied on the subscriber. For example if you update 1000 rows on your publisher, this will be applied as a 1000 row batch on your subscriber within the confines of a transaction.
If your commitbatchsize is very large and you are doing large numbers of batch updates on the publisher you may see some tempdb growth on the subscriber, however I would expect this to be the same on all subscribers.
Do all subscribers have identical roles? Is it possible there is another process running on these problem subscribers which might account for it? Perhaps a maintenance plan?
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941 -
segunda-feira, 4 de janeiro de 2010 12:40Hi Hilary,
Thank you for your reply. I really appreciate your input.
We have a number of identical (at least the should be) read only servers that are all Subscribers in a Replication topology. I say “at least they should be” because the issue is only present on the newer servers.
Logic would dictate therefore that there must be something different in the configuration between these two groups (old and new servers).
I have checked the obvious items such as:
Rogue Index Optimization jobs/Maintenance plans
Different Recovery Models
Use of Row Versioning
Your comment regarding the commitbatchsize is an interesting one and is worth investigating further.
Many Thanks,
John Sansom | Blog: www.johnsansom.com -
sexta-feira, 3 de agosto de 2012 20:05
I have not noticed any significant increase in replication related tempdb growth after the snapshot has been applied.
One possible problem could be the way transactions are held as they are applied on the subscriber. For example if you update 1000 rows on your publisher, this will be applied as a 1000 row batch on your subscriber within the confines of a transaction.
If your commitbatchsize is very large and you are doing large numbers of batch updates on the publisher you may see some tempdb growth on the subscriber, however I would expect this to be the same on all subscribers.
Do all subscribers have identical roles? Is it possible there is another process running on these problem subscribers which might account for it? Perhaps a maintenance plan?
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941Hi,
I'm having the same problem, an additional information, when I Restart the Agent Service, Tempdb is cleaned. The replication Jobs was running. Replication Jobs start when Agent Service starts.
John, This happens to you too?
.png)
