I'm having a problem with memory and cpu increasing after I enable the
broker on my database. Before I enable the broker, my memory will
be at 70 mb, but after I enable broker, memory increases to 100, 200,
and keeps rising even until 700 mb; plus the sql server process uses
more of the CPU, ranging between 30-40 percent.
Any ideas what could be going on, and possible solutions? Thanks
Hello,
I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for? I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned? Do I need to do anything special to get it? Did I install all the right files?
Mike
I have a database with Broker_Enabled set via the following command:
ALTER DATABASE 'xxxx' SET ENABLE_BROKER
All works well, I made a backup and want to move to my development machine. When I run I still get an error saying I need to enable broker service again.
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How can I move the database and get this setup? Where are the BrokerID's Stored?
Thanks,
~ Steve
I've received conflicting information from Microsoft personnel so thought I'd see what some thoughts here are.
In summary, we upgraded a server from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2 Standard. This servers main purpose is to handle alot of merge replication to anonymous pull subscribers. We have some Transactional replication also occurring. There are 8GB memory on the server.
During the upgrade we ran into memory pressure on MemToLeave. We put the /3GB parameter in boot.ini and -g512 on the startup per Microsoft's suggestions. This got us past the upgrade process.
After the upgrade, we took off the boot.ini setting and the -g512. We enabled AWE and assigned 6GB to SQL Server. Then once in a while when the merge snapshots were running, we'd receive some "system out of memory" errors. I went ahead and put -g512 back on and haven't received the error since.
My question to Microsoft then was if we go to say 16GB of memory on the box and give say 14GB to SQL Server, would it be beneficial to set the -g option to a higher number. That's when I got into a discussion with the Microsoft person that SQL Server 2005 Standard would not use anything above 4GB, which is opposite what the Microsoft site says, others have said, and opposite to what I'm seeing for memory usage with DBCC MEMORYSTATUS showing the 6GB being used We'll be talking to our TAM about our suppport, specifically on Replication topics, as we've had some problems getting knowledgable support on this topic. If anyone knows of support outside of Microsoft on Replication topics, I'd love to hear about it.
Any thoughts on the tweaking of memory related to our environment? I know it may be site-specific and we may have to do some trial and error, but with:
1. Doing heavy merge replication processing on the server(1,500 subscribers).
2. say we get 16GB on the box(server is Windows 2003 SP2 Enterprise)
are there some suggestions on a -g setting to best utilize Buffer Pool and MemToLeave ? Some other things to do? Is there some process/method to help determine how best to define the memory settings? If there a way to see how much BPOOL and/or MemToLeave the system is using at a given moment? DBCC MEMORYSTATUS gives alot of info, but I'll be the first to admit that I don't know what alot of the info there is really telling me. If there some white paper, etc that would help determine what the system is doing memory-related, that'd be great to know.
Thanks for any help,
Doug
I backed up a database "Broker", and restored it on the same instance as "BrokerQA" (Broker db still exists, I need them both running on the same instance).
View 4 Replies View RelatedHello, I receive this error "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ... Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs) System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString) End Sub...Web.config ... <connectionStrings> <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>... Hope you could help. cheers,imperialx
View 1 Replies View RelatedFor developers, we often have a need to backup a production database and restore it on local or integration machines. This production database is enabled for service broker and operates at a relatively high traffic level. When the database is backed up, the size is nearly 12GB; when SET NEW_BROKER is subsequently executed on the restored database, the size goes down to about 800MB. It appears that most of this is residing in the xmit queue. So, my question is: how best to backup a production database with queues activated, etc. without ending up with a 12GB backup?
Thanks.
Would anyone have any suggestions/advice on how to determine what is causing the memory usage for sqlserver.exe to increase a dramatic pace in windows task manager? What would be a good resolution to slow down this memory usage?
Thanks!
Hi all,
Our Production server has 4GB RAM and is running SQL Server 7.0. By default since SQL Server 7.0 Standard Edition can take up only less than 2GB, our SQL Server is now using only 1.8GB (leaving the rest for the OS, Windows 2000 Server).
Inorder for SQL Server to take advantage of more than 2GB of RAM it is suggested that boot.ini be modified to include the switch, /3GB
Has anyone seen any issues with doing this? Is it safe to do so on the Standard Edition of SQL Server 7.0?
Thanks in advance,
Praveena
Out techs informed me that they are getting reports of a system slow down. When they look, they find sqlserver.exe has lots of memory allocated to it. They reboot the server and then it runs okay for a few weeks. They tell me this just started happening recently.
SQLServer itself has not been touched in months. They are, however, starting to use one of the databases heavier.
I found a setting where you can set max_server_memory. Any problems if I set this to a value?
Hi,
I'm hoping that some might have an answer for me after much net searching.
I have a server (200GB disk space, Dual 3.8 GHZ processors, 4GB memory) that hosts 6, very small, SharePoint sites (WSS 2.0) and SQL 2005 express handling 1 config DB and 7 content DB's.
The biggest DB at the moment is only 300 MB and the sites are not actively being used yet, they are only open to a select number of users (+ - 25 in total) who are using them as reference "areas" at the moment. Each site is running in it's own application pool as well.
I find that the sqlserv.exe process increases in memory usage and does not seem to decrease. It gets to the point of 960 MB usage and then databases cannot be used (SQL 2005 Express max memory is 1GB). SQL seems to "close" them down and site errors being received are "site is not in configuration database". When the SQL service is restarted the memory usage idles around 70 - 80 MB, the sites are 100% again but after an hour or 2 the memory usage is sitting at 560 MB again and doesn't seem to decrease.
Is there a way I can bring this memory usage down?
Any feedback would be greatly appreciated.
Hi,
I have implememted service broker in my application.My sqlservr.exe comsumes more than 2 gb of memory which eventually cause my computer to hang.I unable to track the problem.
Can anyone help me out of this.
Looking at the SQL Server logs i found following error message logged multiple times ;
The query notification dialog on conversation handle '{09B96045-99A4-DC11-802F-0013EFF142EF}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.
-Aravind cheziyan
We are looking for some guidance with an issue we have picked up with our implementation of Service Broker here on the ABSA Capital project and I am hoping you can help or point us in the direction of someone.
The architecture we have implemented for service broker is to make use of an Activation stored procedure on two queues (1 SP per queue) to process the messages received. What we have found is that the activation stored procedure runs on a background session and its CPU time and memory just grows to the point where it brought one of our UAT servers to a grinding halt.
Is there anyway we can reduce the memory consumption of the activation stored procedure or is this one of those things that still need to be ironed out in Service Broker?
Hi,
I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.
I look forward to some responses.
Kind regards,
Neeva
How to prevent the hang on the initator service broker if the target service broker is not started?
Our case has two service brokers (two databases), sometime, the target is need to turn off. But the sitation is the initator service broker (in fact, the message is sent from triggers) become hang, I want to prevent this case and continue to operation, and the messages should queue and will continue to send to target service broker when it startup. How should I do?
Hi, I was aware of an idea that I want to share with you guys: Here's the thing, I have an Access DB that I will pass to SQL, optimizing its structure and data, to optimize then its performance. About this last issue, the present Access DB has some consultations very redundant. Take this example as "source code": Consultation A is made by selecting some fields with some conditions on consultations A and B. I want to end this. Consultation A is consultation A, B is B and C is C, they all are made by themselfs.However, a new idea came to light! This data is to be displaied on a Web application on VB.Net, and I want to show the results from consultation A, B and C, once per time, but all sequencially. What if I execute two commands, one reading consultation A (by a stored procedure), another to consultation B, and save that data in an arraylist of objects, and then generate consultation A based on those application objects that are alread instaned and ready to use, selecting the final data on those objects and not in the database? Am I making any mistake, or am I optimizing performance somehow? The final purpose is really getting the most performance as we can!Any tips on this?Thanks a lot!
View 3 Replies View RelatedI'm not sure if this is the right forum, but I have a general question about running/storing databases. I have been running a process with 60+ million records in one table and another 16 million in another table and it is taking forever to get everything imported in and run the appropriate queries. I've been doing this all on a desktop and I am anxious to learn of a more efficient, faster method of processing this amount of data.
What solution should I pursue if I am doing this work a few times a year so that it doesn't take three full days of processing to reach an answer with the data?
Thanks.
Hi,
one of our clients is running out of disk space on the SAN and I was simply wondering if it's possible to increase the disks on the fly without any major problems...? Should we take any special precausions? It's a clustered Win2k3 64-bit server with SQL Server 2005 Ent Edition...
--
Lumbago
Hi, i'm using SQL Server Express (9.0.2047).
My database's *.ldf file's size increasing everyday... How can i decrease it?
How can i make ldf file to small ?
Please, inform me.
Thank you.
Hello all,
Can I know somedetail about why the Sqlservr.exe app increasing in size drastically. Even I check all parameter of the server and I check the process running on server.
I feel server is not releasing the queues and It is occupying the memory. I any one suggest what could be the cause ?
Thanks,
Hello,
Does anyone know of a good process to increase a Column size of type char, that already had data in it, without losing the data you already have?
Thanks,
Kevin
I have increased the number of connections my Sql server will allow, and now I cannot restart my SQL Server, it keeps creashing and giving me an error message, has anybody else come across this, or know how I can restart my SQL server so I can atleast do a bit of work today!
View 1 Replies View RelatedI am having trouble increasing the size of the log device on a SQL 6.5 database. When I use SQL Enterprise Manager I get an error saying that the device has 0 MBs available. When I use the ALTER DB statement I get an error saying that there is not enough space on the disk, but I know this not to be the case. Has anyone any suggestions?
Thanks in advance,
Michael lawlor
To increase user connections in SQL 6.5 do I have to first increase the memory configuration numbers?
Current memory config is at 8192.
Hi, can anyone tell me the best/easiest way to increase a field length in a table. We are using SQL 6.5
Regards
Hello All,
I have faced a network problem during some days, what forced one of our replications to be stopped.
The Publisher database is a high volume database.
After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.
My concern is the way it is working, there will be no enough space for the log or for its backup files.
So, I have created a TSQL job within the following commands:
BACKUP LOG database_name
WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)
It's running every 20 minutes, however the transaction log remains increasing.
I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.
Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?
Thanks a lot!
Regards,
Felicia Schimidt
felicia.schimidt@br.flextronics.com
Hello, relatively new to SQL Server.
I have a database with 1 .mdf data file and 1 .ldf t-log file. There are multiple inserts/deletes/transactions performed on the data daily, but the size of the two files remains constant (5,774,,336 and 153,480 respectively)??? I perform daily full backups and hourly T-log backups (during business hours of 9-6) and these backup files change size, but why aren't my physical DB files changing? I have them set to auto-grow at 10% unrestricted...
Thanks for your help/advice
Arthur
Hi all
I have a Publisher,distributor and subscriber in the same server
The distribution database size has increased upto 30GB (.mdf file)
what are the reasons that .mdf file would incease to such extent.
we do some data migration to tables which are replicated
is there a way we can reduce the distribution.mdf size?
Thanks
I got a table, and inside column 1, i wish to insert number by increase into every rows untill column 2 has no record, how to do that?
EXPECT OUTPUT EXAMPLE
COLUMN 1 COLUMN 2
-------- --------
10000 ghjgh
10001 asdasd
10002 yhhh
10003 pkoprt
10004 45450
10005 tgtged
10006 retgret
. .
. .
and so on
STOP WHEN NO RECORD
I`m using IIS7..At present, our company has come not a difference indeyo of operating the server using Classic ASP recently we met a big problem : session is increases indefinitely. 503.3 - ASP.net has a queue is full. The phrase occurs continuously. Is there any solution for check problems? or how can i see what source code page makes the problem (ex. 123.asp : session 300. 234.asp : session 4000).
View 2 Replies View RelatedHello,
I am currently migrating a DB from Oracle to SQL Server (Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86))
I've used ssma to do the migration, and I'm reviewing the prodedures to check them. I have find a performance problem in one of them, which worked perfectly under Oracle, and I have tried lots of things with no luck, so I guess I need some help
I insert a row in a table, and the time it takes for this is fine, but seconds later I need to read the row, and this Select lasts 1-2 ms more every time. This process is repeated lots of times.
Every insert-select takes 200 ms when it receives the first data (including some other operations that are not increasing the response time), and 200 insertions later it takes about 500 ms, which is really too much, considering it keeps increasing.
The table has 25+ columns, and some of them contain varchar of 3000+ characters.
I make the select using 4 columns in the where part. One of them is a numeric, and the rest are varchar (no one is the primary key).
I've got a clustered index for the primary key, and two more non-clustered indexes. One of them refers to the columns I use in the Select, and the parameters are Fill Factor: 90, and Recompute Statistics Automatically.
I hope I explained properly...
Any ideas?
Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?
joe
Is there a way to increase the size of the procedure cache. Or is it only a auto configuring option.
I have 2gb of memory, and when I check the size of the procedure cache it is just 10mb. I would like to increase this to around 50mb. Not sure if there is an setting to do this. Had a look on BOL could not find anything.
thanks
Jane