Hi,
We have 32 individual publications setup on SQL 2000 and have 32 distribution agent running against it. I think each distribution agent opens a process called distrib.exe in Windows task Mgr.
Problem I'm having is that these 32 distribution agents are taking up fair amount of memory 1/2 gig and each process seem to be ever increasing in memory usage so I suspect a memory leak?
Is there something I can do to fix this problem? I don't want to upgrade memory as we already have over 1 1/2 gigs on this machine and it would not be feasible if we have a memory leak.
I have a custom .net application that uses SQL 2000 server. All users are compaining performance issues and white-outs while they are using the application. I am almost certain that it's the SQL server that is the curprit. All the other components involved in the application hardly has any CPU or memory usage when I check the performance in the task manager.
On SQL server, I see that the process sqlserver.exe is taking like 2.8GB of memory. Is there a way to find out which exact SQL query or process is taking so much of memory? I belive there may be a bad SQL process that is stuck and taking all the memory? Is there a way to find out?
I have created a SSIS package that reads 500 text files splits them into 4 raw files then reads them again and writes then to 4 database tables different Tables.
The reason form this is that my raw files have multiple types of records in them and it is only 1 Coolum. I split this out into the different types of records and load whole rows into the database.
End up begin rows in the T6 Table 1000178 18148821-00 40204043 1 EX201036259NZ 0000304862 1511158 18084863-00 40617044 1 EX201033969NZ 0000302981
T5 Table gets a new record 1511313 18126485-00 2006032510230300 EX201033399NZ
and T1 Table get a record 18148821-00
Anyway all this works find but I find that the DTExec process work fine until it has used up all the memory in the laptop in general it take 400megs to run this SSIS. I'm wondering am I missing something like don't run in a transaction. I know in the old DTS you could commit on each package and how do I turn all logging off eg what you see in the DOS box (can I do this?) would love some help on this and if anyone want a copy of this ssis package ie your trying to do the same then I'm more than happy to email it.
Hi, Situation: I have a web application that is running on a server. IT has told me that the application I developed is taking up enormous amount of ram. It seems that the amount of ram that is taken up, is slowly increasing. About 10 megs per hour (memory leak). Furthermore, he has informed me that the ram is taken up by the sql. My senior developer told me that I probably have sqlconnections open that are not closed. I have checked through the application and it seems they are all closed properly. But I may have missed some, as the application is pretty big. Questions: 1. Is there an application or piece of code that can monitor how many sql connections I have open during runtime? 2. Could there be any other common causes to such a kind of memory leak. Any help with this matter would be very much appreciated. Thank you for your time. Sincerely, Jeff
I am running SQL Server 2000 on a desktop PC. Just recently upgraded my PC to 2gig of ram from 1 gig, in part, to try and fix the problem below. Didn't work. Have SP3 installed but not SP4 at this point.
When I open up query analyzer and edit some code, regardless of whether I actually execute the code, SQL server eventually sucks up available and cached memory to the point that my system comes to an effective halt --takes forever to do anything either in SQL server or other applications. In the task manager PF Usage climbs to just over the 2gig memory limit.
I conceptually understand the dynamic memory operation of SQL server ... but why is it sucking up most available memory when nothing is executing?
Is there a way I can release/clear the memory? Ideally, code that coould be run under a stored procedure would best meet my needs. Right now, I am "fixing" by shutting down and then restarting SQL server.
Hey everyone, sorry for the lame questions, but: 1) I have a transactional replication service set up. However, whenever we select a new table or column within a table to replication to the subscribers, the changes never seem to get picked up unless we make an actual change within some of the other data. Any idea why this is? Even after I've reinitialized snapshots, the new changes don't want to proprogate to teh subscribers until data is actually changed. 2) where do I need to go to manually stop and restart a a distribution agent to force these changes to be proprogated without having to change any data within the tables? Thanks
I seem to be having trouble with the distribution agent not doing what I want it to do. I have set up a few publications to run all the time but when I want to schedule a publication to run once a day this doesn't happen it defaults to run all the time. Can the distrubition agent run some publications continusly and some publications scheduled?
Please, i need some help with the replication i had configured. I have two publications to the same subscriber, one with data filters and other not. The one without data filters i have test manualy. But now after i have created the other one, the log reader says that '145 transactions with 347 commands' were delivered, but the distribution agents says that 'no replicated transactions are avaiable'. I know that have many things to be replicated, but doesn´t work. The Sql server agent is working on the distributor. There is something that can be causing this error ?????
Maybe I'm just totally not getting it, but can someone tell me how to stop/start the distribution agent? The Books Online document - "How to: Start and Stop a Replication Agent (SQL Server Management Studio)" - shows how to stop/start the Snapshot Agent, Log Reader Agent and Queue Reader Agent, but not the Distribution Agent. Is it that stopping one of these agents in turn stops the Distribution Agent. I am trying to work through how to add an article to a peer-to-peer topology and need to stop the agent as part of that.
I have a transactional replication going on from Server A to server B (Sql Server 2005)
The replication was working well till yesterday..
But now, the replicated transactions are not getting applied at the subscriber. When I see the Log reader agent, its working fine...delivering the transactions to the distributor. When I see the replication monitor status ..it says 'delivering replicated transactions'...however the number of undistriuted transactions is very high..its getting bigger and bigger..not decreasing...I dont see any errors..So my guess is the distribution agent is not working eventhough its says 'delivering replicated transactions'..Other thing is if i see the replication monitor it says status excellent...and latency as 00 ..but i dont see the latest data at the subscriber..the subscriber is way behind the publisher..
can anyone help me on this?? how do i resolve this problem??? I stopped the distribution agent and again started it..but it dint help...what should i do??
I have setup a replication and after 24 hours this are the error messages I have got:Last action; The database 'supersite' on server 'solomon' does not exist. Full error message: Could not locate entry in sysdatabases for database 'superiste'. No entry found with that name.Make sure that the name is entered correctly. Can anyone out there help me out?
I seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.
How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.
I have a replication Push Subscription configured that works most of the time but I get occasional errors satating that "The process could not connect to the Subscripber 'xxxx'". The details are
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Source: PYRRXNDAL341WIN (Data source); Error number: 18452)
This happens randomly and when it does fail, I can either what for the next occurence in the schedule or I can start the agent manually and it completes successfully. I can also ping the other box when it happens. I've looked at the event logs and don't see anything going on around the times the distribution agent is having the problems.
Any suggestions on where to start looking for more detail?
I have been seeing this issue for several weeks now. The distribution agent will become idle even though there is activity occurring within the replicated database. If I manually start the distribution agent, it will begin retrieving the transactions. I thought that this might be due to inactivity but I have modified the inactivity threshold and I am not receiving an actual inactive agent warning. There are no warnings -- it simply sits there and does not resume sending transactions.
Any thoughts? This is a transactional replication with a distribution agent that is run at the subscriber.
Hi,For some magical reason, my database replication has suddenly stoppedworking. I modified the publication but the Distribution agent willnot run; the snapshot does not get applied to the subscriber, andchanges made at the publisher do not get replicated.My configuration is: Transactional Replication with a 'Pull'Subscription. The Subscriber is Updateable with Immediate UpdatesOnly. All created via the SQL Server Enterprise Manager. Both thePublisher and Subscriber are on the same server. I'm running SQLServer 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).I manually run the snapshop agent. It runs fine with the status'Succeeded' and the Last Action is 'A Snapshot of X article(s) wasgenerated'.When I look at the distribution agent, things start to get a bitconfusing in ( <SERVERNAME>/Replication Monitor/ Publishers/<SERVERNAME?>/ Publication/ Subscription), the status staysconstantly at 'Never Started'. All other columns are either blank or0. When I right click over the subscription, both Start Synchronisingand Stop Synchronising are NOT greyed out. If I click on either StartSynchronising or Stop Synchronising (absolutely) nothing happens(except the mouse turns 'busy' for a fraction of a second). There areno error messages.I added -output c:sqlout.txt -outputverboselevel 3 to thedistribution agent command. C:sqlout.txt did not get created.It worked fine up to a couple of days ago. I'm not sure exactly whatcaused the problem, as I made a few changes at the same time. Thechanges included: adding a new transactional publication, adding morearticles to an existing transaction publication, and adding a mergepublication. I've tried to go back to the set up before I made thechanges, even disabling replication and starting again from scratch.Still the problem persists.The owner of the distribution agent has dbo permissions in both thepublishing and subscribing database. Under 'Publisher and DistributorProperties' I've set the 'Replication Agent Connection to Publisher'and 'Agent Connection to the Subscriber' both to Use SQL ServerAuthentication, both with the same account that has dbo permissions inboth publishing and subscribing database.Has anybody come across this problem before? Can anybody make anysuggestions? Thanks in advance for your help.RegardsDamian HelmeIS ManagerINTEC Engineering (UK) Ltd.
I am trying implement replication and having problem when creating push subscription to an existing transactional replication publication. The distribution agent is failing to run its job with the error:
Agent message code 14260. You do not have sufficient permission to run this command. Contact your system administrator.
I followed the http://msdn2.microsoft.com/en-us/library/ms151868.aspx article instructions when I set the distribution agent properties
What did I miss?
The following is the step error message: Date 1/12/2007 2:30:01 PM Log Job History (105342-DB3PROD-MOMA-ArchivedTransactions-105337-DEV2-15)
Step ID 2 Server 105342-DB3PROD Job Name 105342-DB3PROD-MOMA-ArchivedTransactions-105337-DEV2-15 Step Name Run agent. Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message 2007-01-12 19:30:01.258 Microsoft SQL Server Replication Agent: distrib 2007-01-12 19:30:01.258 2007-01-12 19:30:01.258 The timestamps prepended to the output lines are expressed in terms of UTC time. 2007-01-12 19:30:01.258 User-specified agent parameter values: -Subscriber 105337-DEV2 -SubscriberDB MOMA -Publisher 105342-DB3PROD -Distributor 105342-DB3PROD -DistributorSecurityMode 1 -Publication ArchivedTransactions -PublisherDB MOMA -XJOBID 0x65C41EBC553D96439BAF69E4DC3CC823 -XJOBNAME 105342-DB3PROD-MOMA-ArchivedTransactions-105337-DEV2-15 -XSTEPID 2 -XSUBSYSTEM Distribution -XSERVER 105342-DB3PROD -XCMDLINE 0 -XCancelEventHandle 00000000000006E8 2007-01-12 19:30:01.258 Startup Delay: 214 (msecs) 2007-01-12 19:30:01.477 Connecting to Distributor '105342-DB3PROD' 2007-01-12 19:30:01.618 Agent message code 14260. You do not have sufficient permission to run this command. Contact your system administrator.
Replication Distribution Agent often dies with the following log entry. At that hour little is ever going on so I am surprised that the error occurs.
Failed Job -> JobName: Instance1-DB1-Instance2-33, StepName: Run agent., Message: Timeout expired. NOTE: The step was retried the requested number of times (10) without succeeding. The step failed..
We have quite a lot of Transactional replication, and over the past few weeks it had become quite slow. I spent the last few days trying to resolve the problem and found that a Push subscription was delivering transactions at about 50/sec and a Pull subscription was delivering at around 700/sec.
This test was run on the Same publication, same subscriber so hardware and links are all the same, the only difference was the type of subscription. I've since changed a few of our subscriptions for the larger publications and have found they run quicker than previously.
Has anyone else seen this behavour and is there a valid reason why this would occur. The distributor and subscriber are both running SQL2005 SP1 and the publisher is SQL2000 SP4.
If anyone could shed some light on this it would be appreciated.
We have run into an issue on a dedicated SSAS 2012 SP1 server where the allocated memory is not being utilized, causing some slowness in use, connections, and queries.
Total Memory on the server is 512, and after startup, the utilized memory gets up to a max of 60GB and stops there. Checking the Resource Monitor, msmdsrv.exe is only taking around 39GB overall. With the current properties, that should be at 330GB. Am I missing something in the settings or in configuration that should be changed?
Version: SQL Server 2012 SP1 Enterprise (11.0.3000) OS: Windows Server 2012 Datacenter - Fully patched and up to date Databases: 2 Tabular models Server: 512GB RAM
I've been using replication for a long while now but have never come across this error. It's a basic transactional replication from ServerA to ServerB, where ServerA is also the distributor. Everything had been running fine on it until yesterday, when this error started popping up and no further transactions could be delivered.
After some quick googling I was able to determine that the distribution agent account needed write access to C:Program FilesMicrosoft SQL Server100Com. According to the MSFT article it's because the distribution agent is running under a non-default profile. I didn't change this. However, what I did change around the time that these errors started occurring was the server's Replication Max Test Size setting. It would be far too coincidental for this to not be the cause, but what I don't understand is *why* that would have changed it.
how do I change this? It is definitely not preferable to create temp files in this directory in our environment.
We have replication setup on a sql server 2000. We encountered issue that the distribution agent goes down (distrib.exe stop running) in the event of network connection broken. We would like to know:
is this the expected result that the distribution agent will go down in the event of communication failure between the distribution server and subscriber server? if not, is there a way to programmatically control and restart the agent? Is there any sp in SQL server which can monitor the replication communication error message? is there any sp in SQL server which can be run to restart the agent? For the best practice, what do you think we can do to achieve an €˜event-driven€™ kind of mechanism so when an communication breaks, the agent can be restarted by the triggered event (or at least a simple way to automatically restart)?
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
On SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.) Replication past two months working fine, Now 1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber). Iam attaching thw file. Thanks Sandeep
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------ Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets() at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font) at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate) at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate) at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate() at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
