I have a problem with a process hanging my SQL server machine and the only thing that can fix it is to reboot the machine. The environment is SQL Server 7, NT sp5, dual processor 500MHZ, 1 gig Ram. The applications are run through MTS written in VB all executing stored procedures. The symptoms are that the process hangs with an open transaction and it can be seen through DBCC OPENTRAN. It can also be seen in MTS on the Transaction List screen and shows as Aborting. It seems to get stuck in that state. The Kill command does not work on the process either. I have tried one fixs that I thought may be causing the error by setting the Max Degree of Paralelism in sp_configure to 1 but that does not fix the problem.
Has anyone seen this and/or have any ideas on how to fix/repair the problem.
One of my clients runs a report using Access retrieving data from SQL*server. Recently I often encounter a problem which makes the server hang. The error messages are something like this, can somebody help?
SqlDumpExceptionHandler: Process 42 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. Error: 0, Severity: 19, State: 0 CImageHelper::GetSym Error - The specified module could not be found Stack Dump being sent to H:MSSQL7logSQL00158.dmp
Can anyone suggest what might be causing SQL Server 6.5 to hang? The following messages were in the error log just before, but Books Online is not much help:
2000/08/21 10:35:20.90 ods Error : 17805, Severity: 18, State: 0 2000/08/21 10:35:20.90 ods Invalid buffer received from client. 2000/08/21 10:35:20.90 spid142 Process 142 entered sequencer without owning dataserver semaphore
there are then many messages like this one: 2000/08/21 10:36:21.06 ods Error : 17824, Severity: 10, State: 0 2000/08/21 10:36:21.06 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'E04180', hostname 'N90459'. 2000/08/21 10:36:21.06 ods OS Error : 109, The pipe has been ended.
and finally many more messages like this: 2000/08/21 10:51:20.75 ods Unable to connect. The maximum number of '750' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure.
I don't think that simply increasing the number of user connections will help, but if anyone can throw some light on the cause of the first message I would be grateful.
Hi,Periodically I run some very complex queries or stored procedures that"hang", and the bigger problem is that it locks up all of the databaseclients ie 50 users connecting to the db via a windows application.I never know when this is going to happen, but when it does it leavesall the users completely hung up.1. Can I avoid this?2. Is there a way to "clear" what I was doing so that I don't have torestart the SQL Server serive?thanks,
I'm running 7.0 sp2,windows 2000, 1gig of ram, and a 933mhz cpu. Server has been very stable with no problems until I moved a 2gig table into the database. Query performance is excellent even table scans take less than 2 minutes. The problem is that once a table scan is performed on the table (I can't index for every possible query) the query finishes but the enterprise manager freezes on the server and users can no longer connect. I've set SQL server to have only 650 MB of ram and the rest is free, problem also existed when the memory was controlled entirely by SQL server.
My cache Hit Ratio was 97% and Cache flushes 0.0 (unfortunately these can't be checked when the problem exist because the box is frozen). I may have a concurrency issue but I'm not sure how to be positive. I don't want to just throw memory at the problem because I'm not sure the problem will be fixed.
I was wondering if anyone could provide me with some help, to do with a problem we are experiencing with SQL Server 7 and Windows NT 4.
We have a database that we have imported data into and are trying to build some indexes on one of the tables. THe database size is about 4GB and the table contains 21,000,000 records. When we try to build the indexes SQL server starts off OK, but after about five minutes all drive activity stops, the server stops responsing, and we need to reboot.
The machine has 2 P3 800 processers and 768Mb of RAM. NT is running on SP6 and SQL Server is running on SP3. We were initially running with 512 MB of RAM, but increased it because we thought the memory was a problem. The Server is not a dedicated SQL Server.
I have used performance monitor when running the query in SQL Analyser and have noted the following counters:
Total Server Memory
This starts off about 208000KB and creeps up to about 625000KB just before the server hangs.
Free Memory Available
This starts off high and is about 6MB just before the server hangs.
Page Reads Sec
Normally 370 and 410
Page Writes Sec
157
Buffer Cache hit Ratio
99.1
I have tried setting the SQL Server max memory option to about 600 MB, and also let SQL Server dynamically allocate it.
I am using SQL Server 6.5 (service pack 3) on an NT 4.0 (service pack 3). It is being used as the back-end database server for Solomon IV (accounting package from Solomon Software). The client machines eventually lock-up after limited activity. The first user to lock seems to do so when they issue a save, once they are locked all other users lock. In SQL activity log the processes has several tables locked. If this process is killed, the other users seem to free up. The only error message found in SQL errorlog is an ODS Unable to write to ListenOn "pipe.sqlquery". This message seems to have been occuring for some time though (prior to the lock-up problems).
The only thing (I`m aware of) that changed is many of the clients where put on a 100Mb LAN and the database was expanded from 1G to 2G because it ws almost full.
The backup jobs on one of our sql servers started hanging. The job appears to be complete because the backups are on the disk but the job never completes when viewed in the activity manager. sqlmaint.exe had numerous instances running all night long but the job never completes.
sql server 2000 standard edition sp3a Windows server 2000
The jobs on this server have run successfully for well over a year and just recently they started hanging. We've done everything short of reboot the server. We've restarted the agent, shut down the sqlmaint.exe but alas nothing we do lets the job complete.
Other jobs that are not back up related are working as they should.
There is plenty of disk space.
Any ideas?
thanks in advance because we are lost as to the cause and resolution.
Hi all,Have a situation that my company has never run across before. Clientis running NT4 for the domain server, using terminal services 2000 andrunning an application with a SQL Server backend and they areexperiencing locking problems. Once one person gets locked out theneveryone trying to access that tables is also locked out as a result.It is not specific to a certain User, or module within theapplication. It's not a specific time of the day (like when a backupwould be running) and sometimes it's in the middle of the night whenthere are actually less Users on the system.We have 500 customers using this application. Most are using SQLServer backend, alot of the newer customers are using TerminalServices, and the number of Users is not accessive as compared to ourother customers. THe only difference is that I do not specificallyknow of another client with an NT4 Domain server in the mix.We actually switched to SQL Server as the recommended back end due tolocking issues using SQLBase because SQL Server is row locking andSQLBase is page locking. Since making this change we have stoppedseeing the locking for years until now. Is this a SQLServer issue orissue with the NT Domain server?Anyone have any ideas???ThanksA
Hi,I recently installed SQL Server 2005 Enterprise Edition (9.0.1399) andI have problems on queries concerning system file manipulation...For example, when I try to increase the Log File of MSDB database :ALTER DATABASE msdbMODIFY FILE(NAME = 'MSDBLog',SIZE = 50MB)This query never terminates...All SQL queries for database creation never terminateCREATE DATABASE EASYSHAREON PRIMARY(NAME = EASYSHAREData,FILENAME = 'D:AQSACOMDATAEasyshareEasyshareData.mdf',SIZE = 200MB,MAXSIZE = 1000MB,FILEGROWTH = 100MB)LOG ON(NAME = EASYSHARELog,FILENAME = 'E:AQSALOGEasyshareEasyshareLog.ldf',SIZE = 100MB,MAXSIZE = 300MB,FILEGROWTH = 50MB)Would someone have an idea of what happens...I'm running on Windows 2000 Server...ThanksPatrick
It appears that every 5 or so days, my transactional replication is hanging. I see that I have a couple thousand undistributed commands and this number keeps growing. It forces me to generate a snapshot in order to sync up. When generating the snapshot, I will get frequent messages like "waiting for a response from server...". So I will keep stopping and starting syncronization. Eventually the snapshot will go out, with a delay after generation.
We have found an issue with using MSS 2005 with odbc connections, some of our code inserts data, then reselects the data back with a select using a different handle. This hasn't caused any issues before but in one customer this causes a lock up. The timeout error doesn't occur as you would expect if trying to select data that is uncommitted by another user.
Although obviously we could re-code to avoid selecting uncommitted rows, can anyone tell me why this works sometimes but not others. Some kind of setting in MSS that we're unaware of maybe. The code works ok on other MSS 2005 & MSS 2000 servers and oracle & sqlbase.
I have a weird intermittent issue with an enterprise version of SS2014. When clicking or right clicking around SSMS will lock up and display the 'SSMS is busy - waiting for an internal operation to complete'. It is only specific to the server as when I connect using my local SSMS this doesn't happen. This was happening both pre and post SP1 install.
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 .
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Hello. I am using a SQL Server 2005 devenv and each time I open my ssis package, the server mem usage jumps up to almost 400mb ram. On opening, tries to validate each data flow task and that takes forever, I have delayed validation however still takes time to load. I am currently using terminal services to access visual studio and the project has two ssis packages that myself and another developer are working on, could this have something to do with this? In saying this, everything worked perfectly a week ago, now its extremely frustrating. Anyone with feedback would be greatly appreciated. Cheers
I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server. SQL Server works fine when connecting to it using Mangement Studio on Windows XP. However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly). When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly. The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal. The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe. On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account). Any ideas?
hey guys,could anyone help me out ..?i have a serious problem with my SQL server DB ..it uses up all my memory (i see it in task manager) after a whole day of asp's triggering the db .. it should go down when there are no triggers anymore, right?please help :)Stallema
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?
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?
For the last week, our production SQL server is running very slow and causing the CPU usage to go 80-100 % almost all the time. This causes certain queries to time out. Our application has never timed out before ever. Also, we did not do any updates on our production machine or installed anything recently.
Has anyone of you ever experienced this issue? If yes, then what did you do to resolve it? any help would be greatly appreciated.
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?
Hey Guys - I was just wondering - if at work you have started to use Linux Servers - and if so, which Distribution of Linux and for what purpose. Cheers - Mark Smith
Hello all,How can I tell how much memory SQL Server is using on a server. OnWindows 2000, whenever I go to Task manager/processes/memory usage SQLServer seems to be showing 1,744,124K. On all of my servers withvarious size, usage of databases, all of them show SQL Server to beusing about the same amount of memory. Can someone explain this to me?Shouldn't it use more for larger databases, heavy hitting databases?Also, I normally check Dynamically configure SQL Server memory and putthe maximum threshold to a little bit less than the max on the server.The minimum query memory is set to 1024. Is that 1024 a subset of thememory used by SQL Server, or is this additional that can be used?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
I'm wondering about the memory usage of SQL Server Ce and the Compact Framework.
I've heard I can use only 32 MB per process. SQL Server Ce runs in process and my database file is about 60 MB big.
1. Does that means, I can only use for my application: 32 MB - CLR - SQL-Server Runtime - parts of my database file hold in memory
2. Is SQL Server Ce swapping parts of my database file into my process memory? If so, is there a chance to group some tables, that they are swapped together to increase performance?
3. Means big databases big memory usage in my process memory?
Like most enterprise there is the database administrator (dba) and there are the developers(dev). The dba are conservative while the developers are also exploring their options.
One of the current usage I'm experimenting on is to provide data visualization - image for the data. Like most I needed to "create" the System.Drawing assembly in the database, marking it unsafe.
During my testing, my code had some exception and that brought down SQL Server.
I read that the CLR is better compare to the sp_OAs as well as the extended stored procedures written in C++ because it isolates the execution in a separate app domain and termination is clean - in case of any errors, it should not bring down SQL Server.
Also I read marking assembly unsafe void these benefits of isolation.
Instead of having to manage the situation where it involves code review by the dba and asking the dba to take some risk, is there a technique where all CLR code that runs in the production server does not pose stability issues.
Hi, I have a dts job that imports an IIS log(a text, space delimited file) into a table. I do only one, very simple activeX transformation, and other than that, i copy the columns right in. I am loading hundreds of these logs. DTS will load most of them just fine via a batch script, but it sometimes just stops and hangs. I get no errors, but my Server's cpu is at 99%. There are no locks anywhere in the database. Any clues as to why this happens? Since i don't ever get errors, i'm really getting nervous on this one.
My DTS job scheduled to run every half an hour is hanging for long.If I stop and restart it will succeed.It happened few times in the past where it ran for 3 days when nobody noticed,and still was executing!Any idea what could be the reason? Thanks1
I had two Window NT 4.0 Server, say A and B... We recently upgraded from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for Computer B, I installed SQL Server 2000 from scratch. These two have a same RAM(512MB) and CPU speed...so the setup for hardware is almost identical...
For server A, there are around 10 connection to this SQL Server... For server B, there are also around 10 connection to this SQL Server...
The database for SQL Server B is a copy from SQL Server A.... I restored the database on SQL Server B from the backup dump of Server A...
We have a store procedure called usp_GetMemo(ID Interger) ... we feed ID number to this store procedure and return its return Description... this table has ID column and it's Non-Cluster index, allow the duplicate row... there are 1.7 million records for this table...
Here is problem... whenever i execute more than 100 times for this store procedure(usp_GetMemo(ID)) continuously, the CPU usage of Computer A is 3 times bigger than computer B....
I did run the command "DBCC DBReindex" , "Update Statistics" for this table on computer A, but did not make any difference...
Keep in mind that Computer A has been upgraded to SQL server 2000 while Computer B installed from scratch... would that make difference somehow?? Before the upgrading, the CPU usage of computer A is the same as Computer B...
Any help will be really appreciated, Thanks alot, Kim,
Can anyone point me in the right direction to find documentation for the problem below?I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]). I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.