Locked Process Hang Ups Not In Error Logs?? Memory Usage Too??
Jul 20, 2005
Details:
MS SQL 2000 dual Intel 1.2 GHz processors.
1 GB RAM
2.1 GB dB
Dynamic Memory Managment.
No other apps running on this server.
First question:
Since I have Dynamic Memory Managment setup, Is it usuall that the
sqlsrv.exe process on the server steadily climbs and is in the 800 to
900 MB range. There is only about 20 MB free. In theory this is how
DMM 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 support
that connects to this dB. At first I thought it was the large use of
memory, but once I was able to see in Enterprise Manager that there
was process blocking several other processes. EM then locked up and I
couldn't get to the details of what the exact process was that was
doing 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 the
logs about it. The logs actually seem very thin on any information.
Thanks,
T.
View 1 Replies
ADVERTISEMENT
Aug 28, 2015
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.
View 10 Replies
View Related
Feb 21, 2007
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.
Should I take any action? How?
View 7 Replies
View Related
Jul 23, 2005
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
View 2 Replies
View Related
Jun 29, 2006
Anyone seen this SSIS error when importing data? I have a 64bit quad processor with 8gb and am importing from Oracle 9 using 32bit DTExec.exe from the command line.
OnInformation,Myserver,MyDomainSQLAdmin,J001OracleDimExtract,{CEB7F874-7488-4DB2-87B9-28FC26E1EF9F},{1221B6EB-D90A-466E-9444-BA05DBC6AFD8},6/29/2006 10:58:08 AM,6/29/2006 10:58:08 AM,1074036748,0x,The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Thanks
View 5 Replies
View Related
Apr 7, 2008
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'
View 10 Replies
View Related
Oct 11, 2007
Hello,
I have had a full lock on my sql server and I have a few logs to found the origin of the lock.
I know the process at the head of the lock is the 55 process.
Here are the information I have on this process:
Spid 55 55
ecid 5 5
Ecid 0 0
ObjId 0 1784601646
IndId 0 0
Type DB PAG
Resource 1:1976242
Mode S IS
Status TransID GRANT GRANT
TransID 0 16980
TransUOW 00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000
lastwaittype PAGEIOLATCH_SH
CMD AWAITING COMMAND
Physycal id 1059
Login time 2007-07-05 04:29:53.873
nat address DFF06EBF974D
Wait type 0x0046
HostName .
BlkBy .
DBName grpprddb
CPUTime 54331
DiskIO 1059
ProgramName
Would someone know a way to identify the origin of the process 55?
I have already tried to execute the following request:
select * from SYSOBJECTS
where id=1784601646
But I have had no returns.
Regards,
Renaud
View 3 Replies
View Related
Nov 2, 2007
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
View 6 Replies
View Related
Aug 17, 1999
Hi,
I've started 2 dbcc processes using SQL Scheduler overnight. and I've got
one is flagged rollback and the other is still running, they are blocked
together. I've tried to kill them using kill <spid> without success.
How can I delete this processes ? Shall I shutdown the server ?
Thanks
Herve
View 1 Replies
View Related
Mar 15, 2006
I am writing a package to process perfmon logs. The issue I have come across is that the perfmon process holds onto the log file and SSIS fails because it wants to exclusive read access. Is there any way of getting SSIS to not take an exclusive read on the file.
I can read the file in notepad fine but not SSIS.
View 1 Replies
View Related
Oct 4, 2015
i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
View 9 Replies
View Related
Jan 30, 2014
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
View 7 Replies
View Related
Oct 22, 2006
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
View 1 Replies
View Related
May 27, 2008
Hi,
My problem is the following:
tempdb is currently 20Gb data space and still some applications request more space :eek: .
Since this server contains multiple applications, I'd like to isolate the application
that uses most tempdb space / get a detailed view on who is using what.
It would be ideal if there was a way to find the SQL / temp table
that cause the problem.
Can anyone help me with this problem.
Thanks
Alren
View 6 Replies
View Related
Mar 26, 2008
Good day to all,
I'm new here, so I don't know if this is the right forum to post my problem. I have a web application written using C# .net 2005 (W/ajax). The application has a module that uploads data from excel file to the sql server 2005 database. w/c is by the way, i'm using SQL 2005 Express Edition, the app can upload up to more than 10,000 records from an excel file. Everything is ok until it was deployed in a test environment, while having a run through with the system, the application encounter an error after which, we cannot log in to the system anymore. I restarted the server (web and sql server in 1 machine running winxp) then I can log-in again in the system. When I'm tracing where the problem came from, I noticed that the memory usage of sqlservr.exe increases everytime the app connects to the server. I already fix some code to close some objects that might have caused the high memory usage, then I run sp_who in the management studio and there are still connections used by the app AWAITING COMMAND. Then I manually kill (using kill spid) connection that are left opened by the application. But the mem usage of sqlservr did no decrease. Is there a way to release the memory usage of sqlservr.exe? In ASP.Net ? I have a hint that this has been causing the error. Thanks a lot.
View 4 Replies
View Related
Nov 25, 2001
Hi,
it seems that every day SQL Server 2000 has some kind of memory leak,
the memory usage creeps above 150000 approximately 3 times per day. Is this normal? It starts at about 13000.
Is there any way that I can monitor what is causing the memory usage to be so high and maybe rectify it?
Thanks for the help.
Steve
View 1 Replies
View Related
Jul 7, 2006
I was on my friend's server earlier and I checked his system stats. Between sqlservr.exe and msftesql.exe he was using 2.9gb of ram. This is not a high traffic server so I felt that was just a wee bit high. What are some usage experiences you have seen? I have not worked with 2005 so I don't know what the usage should be like for a low to medium usage server. The guy that admins the db is kinda a moron anyway. He said he has said to the server admin it has been optimized, but I dont believe it one bit at 2.9gb of usage. Any comments would help.
Pete
View 3 Replies
View Related
Sep 18, 2007
Hello,
I need to know which of the following two methods do need less RAM.
There are 2 big tables, each about 9 M rows, and 6 small dimension tables with each about 10 to 100 Rows. The dimension tables are joined by their id's with one of the big table.
The Structure of a dimension Table looks like
CarID (tinyint), Description (varchar(20))
1 BMW
2 Porsche
I want to join the 2 Big Tables in a materialized view. Later i will run queries like
select * into #temp from dbo.vw_materialized_view where Car = 'BMW'
So, back to my question, will such a query take less memory (ram) when i joined all 8 tables before I created the mat. view or will it take less when I only join the 2 big tables in a mat.view and later join the mat.view with the 6 dimension tables?
Hope you got that ;-)
Thank you
View 1 Replies
View Related
Jan 13, 2004
Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfoming
View 8 Replies
View Related
Jun 16, 2008
Hello, I have SQL Server 2005 64 bit on Windows Server 2003 Enterprise 64 bit on a server with 8GB of RAM
Task Manager reports the process sqlserver.exe consuming 7GB of memory
Is this normal ?
View 7 Replies
View Related
Feb 1, 2007
how to check the memory usage other than performance monitor for the server?
When I execute the SP_WHO2 on the server, it showing each user have 5 connections there and the status is sleep. what is that means?
Does it takes up memory?
why have so many connection or entries for the same user, they all are using the same db with same application.
Thanks!
View 3 Replies
View Related
Dec 11, 2007
I have setup 2 x64 SQL boxes now that are having problems with memory. Both boxes were pretty much setup with the defaults on windows 2003 R2 64 bit server, only changes were file locations etc.
On both servers the memory usage continues to climb over the course of a few days until all physical and virtual memory is used and the server comes to it's knees. A reboot will stabilize it for a few days while the memory usage ramps up again.
The sqlservr.exe process does not report much memory but the commit charge in the performance tab of task manager shows the maxed out value. I thought the sqlservr.exe process only didn't report the correct memory figure when AWE is enabled which it isn't.
I thought as a default, SQL server would dynamically allocate memory as needed and had internal mechanisms that would not use more than the physical ram available. The one server has 16GB and I even set the max SQL memory usage to 12GB but still my total memory usage would get out of hand. SQL Server is the only application on these boxes.
Am I making some incorrect assumptions and need to change the way SQL is setup? Any suggestions?
Thanks
Scott
View 5 Replies
View Related
Jul 20, 2005
I'm trying to get the machine free ram value using either T-SQL orSQL-DMO.I found .Registry.PhysicalMemory which takes care of the total ram butI still need eithrt the free or the used.Any ideas?Thanks!
View 1 Replies
View Related
Jun 13, 2007
Hi All -
We have some CLR sprocs and tvf's we run in a batch job that recently have been getting the out of memory issue. I want to increase the amount of memory allocated to the CLR using the -g startup switch but i want to make an intelligent decision on how much to allocate. What are some of the best ways that you have found to estimate how much to give the CLR?
Thanks,
Cameron
View 1 Replies
View Related
Mar 14, 2007
How much available memory is best practice for SQL servers? We have some alerts set up by our system admin, they go off if the available memory goes below 500mb. Which is fine for other servers but I feel for SQL server it isn't quite correct. Currently we stand below at about 475mb and the PF usage is around 7gb.
It is SQL 2005 standard SP1 x64
dual intel xeon 5160s
8gb physical memory
buffer hit cache is > 99%
I am just not confident enough to make a solid decision here that this is acceptable. In the beginning of this month I ran some counters for 24hrs to get a feel for the baseline. The average for available memory was about 850mb at that time. So I am wondering if it is going to keep declining and turn into a problem. The server/instance has not been restarted since maybe last November.
thanks
View 3 Replies
View Related
Oct 17, 2000
Does anybody know why BCP on v6.5 grabs so much memory for SQL Server? I have a few table imports where the BCP process will consume over 460MB of RAM during the imports.
The BCP cmd file is executed via an xp_cmdshell call. The server has 2+GB of RAM, but the BCP process effectively flushes large amounts of data from the buffer. It takes quite along time for the cache to recover from this, and after this, the rest of the nightly processes run much slower, as they end up having to hit the drives to retrieve information that should already be in cache.
If anyone can shed some light on this it would be much appreciated.
Ian Dundas
DBA
Assante Asset Management
View 2 Replies
View Related
May 9, 2003
Hi,
One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now.
We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point.
We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%.
Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage.
There is no noticable difference performancewise yet.
My questions are:
Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it?
Thanks in advance for your opinions,
Shaili
View 3 Replies
View Related
Sep 25, 2006
hi all
I got a small MSSQL server.. total database file size less than 7GB. with 2G rams installed 2 cpus. but for some reason when i check the task manager process mem usage is over 1.7G. the sql server memory setting in on Dynamically not fixed. and maximum 2G i believe is default. anyway. my question is over 1.7 memory usage is too high? because i dont have alot of transaction going. and cpu usage is very low. wondering if this's normal or not. and if is not normal . what cause the memory usage so high...... and how can i adjust back to normal. ? can anyone help me out? or any suggestion? thanks
View 4 Replies
View Related
Dec 1, 2004
Hi All,
Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important.
What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.
Upgrading the memory is currently not an option :(
I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?
Can SQL release the memory and still act as normal?
Any input would be generously appreciated
Cheers!
View 2 Replies
View Related
Jan 8, 2004
I am using SQL Server 7.0 on a Windows NT machine. We have been having problems with SQL Server not releasing the memory after it has utilized it.
Currently it is configured to allow a max of 511MB memory (1024 is total on the machine). I had some advice that my best solution would be to reduce the Max Memory to a lower value (say 400 MB) to help reduce the problem.
Is this not counter-intuitive? Or is this the correct solution?
Thanks.
View 9 Replies
View Related
Apr 4, 2008
With SQL Server 2005, I have the problem that sqlserv.exe keeps using more and more memory. Now I did some research and found the following anwers in different places:
*It seems to be the way that SQL Server works. It just uses all the memory which it can get. I have two remarks about this:
1) I that is true, then why is a fresh installation of SQL Server not consuming all the memory it can get?
2) After a few weeks, it consumes so much memory that the whole system (even SQL Server itself) start running very slow. I don't think that's the intended behavior?
So I thought about restricting the maximum memory that SQL Server uses, but people keep saying that's not the way it should be done: SQL Server should be allowed to allocate memory dynamically.
Can anyone help me with my questions?
One other thing that may be of relevance: This instance is a disitributor for replication and the distribution.MDF file also seems to be increasing over time. It became much larger than the application database.
Thanks in advance for replies
View 2 Replies
View Related
Nov 27, 2006
Hi there group.
Could some please point me in the right direction?
We have a database and it's about 28GB in size, recently the SQL server process that runs uses approximately 1.6GB of Memory.
I have tried running SQL profiler to find out which Stored Procedure is causing this but came up unsuccessful.
When restarting SQL the process it run's at about 50MB for about 20sec and then starts climbing up to 1.6GB of memory usage.
Please assist.
View 12 Replies
View Related
Feb 27, 2008
Hi to all,
I front a series problem with my SQL Server.
I Use SQL Server 2000 standard on Windows 2000 Server machine.
The problem: after certain actions such as DTS (that takes table
contents and insert to a a file) the Memory usage of SQLservr.exe is grown significantly and after it finshes with success it doesn't free the used memory for the action - in this case the DTS. maybe this explains why I have Memeory Usage of 1700MB of Sqlservr.exe on windows task manager.
Is it a bug? why SQL doesn't free the memory after finishing the action.
Any Solutions ? Please send me a short feedback.
Roy
View 3 Replies
View Related