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 database
clients 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 leaves
all 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 to
restart the SQL Server serive?
thanks,
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.
Thanks
Greg Flynn
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
Thanks for any thoughts.
Roy
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.
Thanks,
John
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.
Thanks for any help, Dave
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.
Any suggestions???
Thanks,
Chris Sheehy
Has anybody encountered SQL SERVER 6.5 using 100% server CPU at startup with no other processes running on the NT server or accessing SQL Server?
I have tried starting up in minimal config mode to access the config values, but am having SQL Server hanging by that point as CPU usage has hit 100%
If anybody knows an answer please let me know.
Thanks
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
View 3 Replies View RelatedHi,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
View 2 Replies View RelatedHi,
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.
Has anyone experienced this?
Your help is much appreciated.
Thanks,
Nick
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.
View 4 Replies View RelatedHi,
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.
thanks.
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 created a simple DTS which executes a VB standard exe that simply writes a string to an ascii file opened as append. SQL Server, the exe, and the ascii file are all on the same NT box (mine). If I execute the DTS myself the process works with no problems. When I attempt to execute the DTS via a job the job hangs with no apparent indications as to what may be the cause of the hangup. The SQL Server Agent is up and running and set to run under the system account. I have applied SQL Server SP3. The same problem was occuring prior to applying SP3.
Does anybody have any idea? All sugestions are appreciated.
Thanks in advance.
We have just combined 3 sites into one server.
Two of the sites are serving fine, however one site has spids
that won't disconnect. By the end of the day there are over 600 spids
from that one site. At first we thought it was due to the ASP pages
had a db connect, but nothing that closed that db connection. But after
they modified the pages, we are still having the same problem. Checked
the webserver and they are identical to the other webservers.
Any ideas???
I have a scheduled job on my SQL Server 2000 that runs a DBREINDEX job on all the tables in my database. For some reason the system just hangs for hours now when it reaches one particular table. Can somebody please help me to correct the problem. Thank you all in advance.
View 2 Replies View RelatedI am attempting a very simple bcp out to a text file in SQL 2000 for the first time. I've tried various syntax but the command consistently hangs no matter which i use. Nothing is being directed to the output log............
"bcp rfsspd..bcp_product_view out product.out -T -c -b500 -r>>test.log"
I have no problems with this command in SQL 6.5, could it be initial setup or config of 2000 or something really daft????
Any help greatly appreciated
I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?
Thanks.
When issuing the below command on any of our databases, it just hangsforever.ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;I realize that all connections expect the query window need to beclosed and that is the case I think, or at least we are resetting theweb server and still see the issue. The only way I have been able tofix it is to completely stop and restart the database server, thenissue the command and it returns immediately.This is a pain though and has to be done after hours. Is there a way toissue the command while the system is in use, possibly taking just thatdatabase offline (and not all other Dbs on the server) for a short timeand then returning it back to use using just scripting?
View 10 Replies View RelatedMy overnight backups hang occasionally. I say this because there is no consistancy to when the routine will hang. It happens on the first step where it reindexes. The only user database on the server is about 12GB. The server is dedicated to SQL so nothing else runs on it. It has 2x 800 MHz processors with 2 G of ram of which 1.5 is allocated to SQL.
I can't trace the error because it happens before the report is written to. There are a few tables with a million plus records that take up almost a 1G per table.
It was my understanding that if users were in the database they would notice a big performance loss but that the backups would still run. Could it dying because of resource contention? I'm at a loss and really could use some input. I can't afford to go a whole weekend without a backup.
Is there a way for me to progammatically check who is the database prior to the job kicking off?
Any ideas as to what would cause an inconsistant hang of job is greatly appreicated.
Thanks,
Kelsey
I have a daily task set up to email a report to me. When I go to look at the jobs it says it is still executing after a number of hours. I Can't stop the job with out stopping SQL Agent. I can't stop SQL Mail. If I stop the SQL Agent I can't start it again. I try to open Outlook (98) but it won't open either. Can't view the properties of Outlook either. Only way to cure this is to reboot the server. The account we are using to start SQL and SQL Agent is a FDomain Admins account. When I try to log off the server I get and error message saying olemainthreadwndname is not responding. Any help would be very much appreciated.
View 1 Replies View RelatedHas anyone seen this?
When I exit out of Query Analyzer - it hangs and gives the following error message in Event logger
Hanging application isqlw.exe, version 2000.80.760.0, hang module hungapp, version 0.0.0.0, hang address 0x00000000.
For MS SQL 7, an error occurs as shown at bottom.
Basically a piece of embedded sql is being sent to the db which has a syntax like:
select distinct WC_REFERENCE_MANAGER.logicalname,
WC_REFERENCE_MANAGER.referenceid,
wc_site.doclistid,
WC_REFERENCE_MANAGER.shared,
WC_REFERENCE_MANAGER.languageid
from wc_content inner join wc_content_ref on wc_content.contentid = wc_content_ref.documentid
inner join wc_reference_manager on wc_content_ref.referenceid = wc_reference_manager.referenceid
inner join wc_site on wc_reference_manager.referenceid = wc_site.referenceid
where xmlid = @p1 and contains(xmldata, @p2)
and wc_reference_manager.languageid = @p3
and wc_site.siteid = @p4
and wc_site.sectionid IN (
Followed by over 15000 numbers
(dont ask - its a generic product with a generic db design behind it!).
Any comments apart from rewrite the query (which isnt an option).
The error is :
02/27/04 14:30:05 Stack Overflow Dump not possible - Exception c00000fd E at 00499f17
2004-02-27 14:30:05.93 spid8 Address=499f17 Exception Code = c00000fd
2004-02-27 14:30:05.93 spid8 eax=394d29f4 ebx=00000000 ecx=394d29f4 edx=0000000d
2004-02-27 14:30:05.93 spid8 esi=394d29f4 edi=433a499c eip=00499f17 esp=3c263000
2004-02-27 14:30:05.93 spid8 ebp=3c26300c efl=00010a97
2004-02-27 14:30:05.93 spid8 cs=1b ss=23 ds=23 es=23 fs=3b gs=0
2004-02-27 14:30:05.93 spid8 Input Buffer 250651 bytes -
2004-02-27 14:30:05.93 spid8
2004-02-27 14:30:05.93 spid8 s p _ p r e p a r e & @ T @ p 1 i n t , @ p 2 n v a r c h a
2004-02-27 14:30:05.93 spid8 r ( 4 0 0 0 ) , @ p 3 i n t , @ p 4 i n t c s e l e c t
2004-02-27 14:30:05.93 spid8 d
I have couple of SQL Server 2000 databases, each of them having the size around 200GB.
Recently I started experiencing some problems while inserting/updating data.
The problem is that it seems like it just hangind and it never finish the insert/update. I tried a simple update that affects just few records and it still hangs without any reason. There is no process locking the update process.
If I go to the database properties and increase the size of the database and then perform the update/insert all works well. My databases are set for unlimited growth , 10% file growth. I have plenty of space free on the disk.
In any case, it looks like it is not able to grow by itself.
I use SQL Server 2000, Enterprise Edition.
I would appreciate your help.
Hi all,
I have a replication that worked for a couple of weeks without any problem. Since yesterday it is hanging. I am not aware of any changes done to the network or dataservers.
Dataservers are on 2005 SP1.
In the Replication monitor I see that the replication agent gets a 10 Minutes no activity seen message.
Therefor I started the merge process manually with Outputverboselevel 2.
It is hanging again, the outputfile tells me that sp_MSadd_merge_history90 is executed.
How to resolve this issue?
I am running SQL Server 2000. When I go into Enterprise Manager and right click on a database, the hourglass appears and it doesn't return.
Similarly, when I run a query agains that database using a 3rd party tool similar to Query Analyzer it doesn't return.
I am new to SQL Server, have worked with other db's. Any clues as to how I can trouble shoot what is happening with my DB?
Thanks
I'm running a DTS package on SQL Server. The source is MS Access and thetarget is Oracle.On a "Drop Table" command the process just hangs. There are no foreign keys onthe table. Several tables have already been processed successfully by thistime.I think I've ruled out corruption by dropping and recreating the targetdatabase on Oracle.Any ideas?M Man
View 1 Replies View RelatedI am using a script to copy a number of packages (50+) from my desktop computer (file system) to a remote server (MSDB). The script works fine, but I can never get through the entire script without it hanging up. Some times only 3-4 packages get copied, sometimes a lot more. If I close the windows cmd window and restart at the point it hung, it continues, but usually hangs up again. If you repeat this 2 or 3 times, it eventually gets all of the packages copied. It does not always hang at the same point. I have tried doing the copies with "quiet" mode - overwriting the existing packages and I have tried first deleting the packages, then copying - doesn't make a difference. I have also tried moving the packages to a couple of different installations of SQL Server 2005 using the script - the remote one and the local one on my desktop - same thing on both.
If I have the script echo a message just after each copy, the message just after the last successful copy never appears (and I know the copy is successful, because I deleted all the packages first and have checked that the package is there after the script hangs).
The copy command I am using look like this:
dtutil /FILE %1 /Decrypt %3 /Encrypt SQL;%~n1;2;%3 /DestServer %4 /DestUser admincis /DestPassword %2 /quiet
ECHO Done deploying package %~n1
%1 is the file spec of the ".dtsx" file to be copied
%2 is the password of the user account on the server
%3 is the encryption password (same one used on both ends)
%4 is the name of the server being copied to
Any ideas???
Jane Gaby
Hi
I set TransactionOption=Required for my SSIS package. In this package, 2 tasks are included,
one is execute sql (TransactionOption=supported)
2nd is DataTransformation Task (TransactionOption=Supported)
But it hangs on the execute sql task itself!
Didnt find any reason 4 it so far!
Hi,
On production server, I have two tasks in status "performing completion action" for last couple hours. Ususally these tasks take seconds to finish.
Do you know how can I kill or cancel these tasks? I tried to stop both the jobs couple times but nothing is happening.
There are no locks/blocks on current activity window.
Please give your suggestion.
Thanks.