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?
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!
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.
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?
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
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?
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa; Max pool size = 200;
And now strange thing is happening ,, I am receiving error :
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.
Huh , So stucked up , how to increase the Max pool size.. Is there any way .
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error "A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" this my sql script use [master]Go -- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO -- Switching to our databaseuse [DatabaseName]GO CREATE SCHEMA schemaname AUTHORIZATION usernameGO ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO /* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter' -- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter] -- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber] -- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
I frequently see the following message on SQL Server log
2008-06-09 07:46:18.17 spid3s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1079156, committed (KB): 17156388, memory utilization: 6%.
What does it indicates and what appropriate action has to be taken to fix it.
The database runs on
SQL 2005 Dev 64-bit SP2 9.00.3042.00 Win 2003 standard x64 SP2 16GB RAM
I am using a tool to monitor SQL Server and Windows. It is warning me that:
Process 1004:services has a virtual address space of 1,846.20 MB. This is close to the Windows two gigabyte address space limit.
When locate the process 1004, it shows 15 threads that Elapsed time for all of them is 1d, 3hrs. The Thread state is Waiting and the Thread Wait Reason is "Waiting for an Execution Delay to be resolved".
I think that 1d, 3hrs is from the time I rebooted my server.
Hi AllSome my SQL Server are experience high memory usage.1. How can I detect which process which process cause the big memoryusage and not released?2. Which sql server components in this memory, and what are their usagedistribution?Any help will be appreciated.ThanksWillie
I got a Small Business Server 2003 running. It has 2 sqlserverprocesses. One of them is growing by 200mb every day. Does anyone havea clue to this. It's serving as a printserver, fileserver and exchangeserver. There is no specific use of the sqlserver. The antivirus isMCaffee
I see the following message in SQL Server logs. What does this indicates. What should I do to avoid this.
2008-05-20 01:25:02.12 spid2s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 33920, committed (KB): 15142988, memory utilization: 0%.
The server configuration is
SQL 2005 Dev edition SP2 64bit Win 2003 R2 SP2 Standard X64 editioin RAM size is 16GB
In my SQL Server Errorlog, I see the below error. The system has 8 GB of RAM with enough free RAM, something I can do to prevent this alert? (Note: I have no MIN/MAX memory set on this Instance)
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 76896, committed (KB): 167628, memory utilization: 45%.
Hi all, Is it just my server, or am I missing a point here. SQL Server 7.0 Ent Edition on NT4.0 SP6 running only one 5GB database.
About 30 fat clients connecting to it via the application. It also has about 20 connections (concurrent) coming via our web users. The sqlserver.exe uses upto 868MB out of the 1GBmemory on the server. The server has "dynamically configure sql server memory" turned on. After a reboot, sqlserver.exe starts with 38MB of mem and slowly starts to grow until it takes up almost 90% of the physical mem that is available on the server.
Hi all: New to sqlserver 2000. When I run an application the sqlserver memory goes up and with each window that gets opened the memory goes up again. All this seems logical. However, when I exit the application, the memory remains at the last level it was in before closing the application. Is this normal? Thanks Terry
We are running SQL 2005 SP2 x64 on Windows 2003 SP2 X64.
Server specs: Quad Core, each core runs at 2.33Ghz. 4GB memory OS and SQL on RAID 1 set Transaction log on RAID 1 set Databases on RAID 5 set
-The SQL service account is a domain account with the "lock pages in memory" rights. -The min memory use for sql server is 3000MBand the maximum is 2147483647MB.
For whatever reason, the sqlsrvr.exe process uses much less physical memory than the Virtual Memory (VM). When I look at the Task manager, I see 230MB for Mem Usage column and 300MB for VM size column under full workload. Why is the SQL server process being paged out? I have 3GB for it to use. Should I be concerned about the my SQL server?
I didn't configure the AWE as MS doesn't recomment using it on x64 bit systems. When I do "dbcc memorystatus", I get the following:
VM Reserved 4269536 VM Committed 196680 AWE Allocated 0 Reserved Memory 1024 Reserved Memory In Use 0
I thought granting "lock pages in memory" rights to the sql service account automatically enables AWE in x64 bit version of SQL 2005 standard server.
Hi, I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet. The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows. When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message. aspnet_wp.exe was recycled because memory consumption exceeded the 306 MB (60 percent of available RAM) How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues. -Thanks
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
I have worked in other ETL tools. So, i am trying to figure out how to do thefile decryption and process the data in memory using SSIS.I am using SSIS on Azure VM and my source files are on Azure storage. The files are encrypted and we are trying to use Phython script to decrypt the files and pass it to SSIS. I found out that Execute Process task can call the Phython script. However, i would like to get the decrypted data from the file and pass it to the next task (control flow) in SSIS without saving it as a file (in-memory). I found that execute process task output can be stored as a Standard Output Variable or to an object. Will this work or do I need to follow any other methods (since we need the entire file to be sent for additional processing).
Running sqlserver 2000 on a w2k server with 1gb of memory. After a reboot the memory usage is around 500m but quickly climbs. At 1 point it was up to 1.5gb so it must have been swapping. Are there any good docs about this or any recommendations on how to limit sqlserver from using all the memory. It is the only application on the server so it isn't affecting anything else so maybe it isn't a problem. I just wanted to get people's inpit on this.
We have a Server that is set to use 80mb of a 128mb machine. The machine is also set to use up to 300mb of Virtual memory as needed.
After running for 10 days, we got the message "Low on Virtual memory". We did some looking and found SQLServer was using 300mb between real and virtual memory.
So the question: Is there a manual method to get SQLServer to release extra memory with out stopping and restarting the service?
Help me, Help me. When I backup the MSSQL databases using ARCServe 2000 in the server, the memory of sqlserver.exe is 1.5G in task manager. After backup was finished, the memory of sqlserver.exe cannot released. It is still 1.5G. Our users can get the resource of that server but they get it slowly. I need to stop and restart the MSSQL service. The server can back to normal. Can everyone fix it ?
My server is Windows 2000 Server Standard Edition with SP4 Windows SQL 2000 Standard Edition With SP3 Memory is 2G RAM
Details:MS SQL 2000 dual Intel 1.2 GHz processors.1 GB RAM2.1 GB dBDynamic Memory Managment.No other apps running on this server.First question:Since I have Dynamic Memory Managment setup, Is it usuall that thesqlsrv.exe process on the server steadily climbs and is in the 800 to900 MB range. There is only about 20 MB free. In theory this is howDMM can work, but do people really see it work this way.Second question:I had users complaining about lockups in the app I have to supportthat connects to this dB. At first I thought it was the large use ofmemory, but once I was able to see in Enterprise Manager that therewas process blocking several other processes. EM then locked up and Icouldn't get to the details of what the exact process was that wasdoing the blocking. After restarting SQL services things were fine.When I checked the logs there was nothing there about a hung process.The logs seemed very sparse. Why would there not be anything in thelogs about it. The logs actually seem very thin on any information.Thanks,T.
When I try to install MsSQL Server 2005 Develop Edition do I get the error:
[Microsoft][SQL Native Client]Shared Memory Provider: No process is on the other end of the pipe.
I have trying to look at other posts on this forum and elsewhere, but cant find any solution that works for me - mainly cuz all solutions is after the installing.
Before trying to install MsSQL Server 2005 Dev did I install VS.Net 2005 Pro. First did the Native Client make troubles, but got it to work with reinstalling it, but now does the SQL setup stop on every try with the error above.
I have tried looking if the MSSQLServer is running when it tries to connect during install, and everything says it is running (Services, Net start, Taskman.).
I dont run any special setup on my system - it is a normal Windows XP Pro SP2 with all updates. I just need the SQL server installed so I can develop locally without access to out main SQL server.
I have been using MsSQL 2000 before and never had any problems, but the 2005 keep on bugging me.
The only solution I havent tried is to reinstall Windows itself, but I will pref. not to do so.
And to be honest, then have I no idea what a "pipe" is - I am used to develop webapplications and not so much on server maintaince/troubleshooting.
Hi Gurus,Our system is using SqlServer 2005 on XP.( On my machine,only thisapplication access SQLServer.) The sqlserver memory is configured to128MB (Min)~512 MB(Max)After our system get started, sqlserver takes about 100M memory. Afterthe system runs for a while, the memory used by sqlserver goes up to512MB. Then we exit our application and expect the memory usage ofSqlServer could be dropped down to the min value. Unfortunately, itseems SqlServer never release any memory until we restart it.I thought dynamic memory configuration means that SQLServer can autoshrink memory usage if there is no more database accessing. But basedon my observation, SQLserver just holds all the memory . :( How canwe let the sqlServer shrink the memory usage accordingly to workload?I do appreciate for your help!Best regards,Jennifer
Hello,i have a doubt here, after a close the connection, i still can see the last stored procedure active in the LOCKS/PROCESSID in enterprise manager of SQL SERVER.I think i'm closing the connection well Try If Me.ID > 0 Then Dim strSql As String = "SP_CHANGECLASSCONTENTS" Dim sqlcommand As New SqlCommand(strSql, New SqlConnection(ConnStr)) sqlcommand.CommandType = CommandType.StoredProcedure sqlcommand.Parameters.Add("@PerformActivation", SqlDbType.Bit).Value = 0 sqlcommand.Parameters.Add("@PerformInactivation", SqlDbType.Bit).Value = 0 sqlcommand.Parameters.Add("@PerformDelete", SqlDbType.Bit).Value = 1 sqlcommand.Parameters.Add("@PerformUndelete", SqlDbType.Bit).Value = 0 sqlcommand.Parameters.Add("@PermanentDelete", SqlDbType.Bit).Value = CBit(PermanentDelete) sqlcommand.Parameters.Add("@Class_ID", SqlDbType.Int).Value = _id sqlcommand.Connection.Open() sqlcommand.ExecuteNonQuery() sqlcommand.Connection.Close() Else Err.Raise(10205, "CLASS", "CLASS ID is empty") End If Catch ex As Exception _err = ex.Message Return Err.Number End Try
Hi I was not able to connect to SQL Server machine. On examining the Error log (which was huge 53MB), I found the following messages that filled 95% of the logfile. Is this something to do with memory allocation.
Someone, please let me know what is going on. After the server reboot everything works fine. I am worrired that this message may occur again.
Thanks Machilu
2004-11-30 20:15:03.64 logon Login failed for user 'NT AUTHORITYSYSTEM'
2004-12-01 08:15:03.77 logon Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
2004-12-01 00:47:25.28 spid70 WARNING: Failed to reserve contiguous memory of Size= 65536. 2004-12-01 00:47:25.31 spid70 Buffer Distribution: Stolen=127590 Free=4176 Procedures=182443 Inram=0 Dirty=14180 Kept=0 I/O=0, Latched=154, Other=10049 2004-12-01 00:47:25.31 spid70 Buffer Counts: Commited=338592 Target=338592 Hashed=24383 InternalReservation=357 ExternalReservation=0 Min Free=256 2004-12-01 00:47:25.31 spid70 Procedure Cache: TotalProcs=66212 TotalPages=182443 InUsePages=88547 2004-12-01 00:47:25.31 spid70 Dynamic Memory Manager: Stolen=310033 OS Reserved=38512 OS Committed=38457 OS In Use=38388 Query Plan=332158 Optimizer=0 General=15540 Utilities=8 Connection=473 2004-12-01 00:47:25.31 spid70 Global Memory Objects: Resource=10685 Locks=119 SQLCache=4540 Replication=2 LockBytes=2 ServerGlobal=45 Xact=201 2004-12-01 00:47:25.31 spid70 Query Memory Manager: Grants=0 Waiting=0 Maximum=92118 Available=92118 2004-12-01 00:50:04.10 logon Login failed for user 'NT AUTHORITYSYSTEM'. 2004-12-01 00:50:04.32 logon Login failed for user 'NT AUTHORITYSYSTEM'. 2004-12-01 00:51:08.78 spid70 WARNING: Failed to reserve contiguous memory of Size= 65536. 2004-12-01 00:51:08.82 spid70 Buffer Distribution: Stolen=138829 Free=5944 Procedures=169283 Inram=0 Dirty=14431 Kept=0 I/O=0, Latched=154, Other=9951 2004-12-01 00:51:08.82 spid70 Buffer Counts: Commited=338592 Target=338592 Hashed=24536 InternalReservation=360 ExternalReservation=0 Min Free=256 2004-12-01 00:51:08.82 spid70 Procedure Cache: TotalProcs=67783 TotalPages=169283 InUsePages=76116 2004-12-01 00:51:08.82 spid70 Dynamic Memory Manager: Stolen=308112 OS Reserved=38512 OS Committed=38457 OS In Use=38398 Query Plan=330249 Optimizer=0 General=15535 Utilities=8 Connection=476 2004-12-01 00:51:08.82 spid70 Global Memory Objects: Resource=10685 Locks=118 SQLCache=4540 Replication=2 LockBytes=2 ServerGlobal=45 Xact=202
I am getting the following error when i try to connect to the my web site using froma different server. A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) i am using sql express and i attach the database through the connection string in the web config. Any ideas
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
