Hanging During READ_COMMITTED_SNAPSHOT ON
May 25, 2006
When issuing the below command on any of our databases, it just hangs
forever.
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
I realize that all connections expect the query window need to be
closed and that is the case I think, or at least we are resetting the
web server and still see the issue. The only way I have been able to
fix it is to completely stop and restart the database server, then
issue the command and it returns immediately.
This is a pain though and has to be done after hours. Is there a way to
issue the command while the system is in use, possibly taking just that
database offline (and not all other Dbs on the server) for a short time
and then returning it back to use using just scripting?
View 10 Replies
ADVERTISEMENT
Feb 1, 2006
Hello All,
I have another problem with setting READ_COMMITTED_SNAPSHOT on SQL Server 2005 now.
The problem is:L
In SQL Server 2005, when running following statement:
ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
It takes so long time to fnish. Actually I have been waiting 25 minutes for it, it is still not yet finished.
Do you know why?
Thanks a lot
Mel
View 5 Replies
View Related
Nov 11, 2007
I asked this awhile back and didn't get any answers. I'm trying again -- maybe more people have started using the snapshot isolation features. This seems like a pretty fundamental question that is not adequately addressed by the documentation.
It is documented that READ_COMMITTED_SNAPSHOT is more efficient than ALLOW_SNAPSHOT_ISOLATION. Is the efficiency hit incurred merely by turning ALLOW_SNAPSHOT_ISOLATION on or does one actually need to be running a lot of SET TRANSACTION ISOLATION LEVEL SNAPSHOT queries?
In other words, if I set both READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION on but run 99% of my queries as SET TRANSACTION ISOLATION LEVEL READ COMMITTED, have I incurred a significant hit for turning ALLOW_SNAPSHOT_ISOLATION on?
Thanks
View 2 Replies
View Related
Jul 26, 2000
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.
thanks.
View 1 Replies
View Related
Aug 3, 2000
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
View 1 Replies
View Related
Mar 7, 2000
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
View 2 Replies
View Related
Oct 18, 2000
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
View 1 Replies
View Related
Feb 12, 2001
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.
View 1 Replies
View Related
Feb 22, 2001
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???
View 1 Replies
View Related
Aug 21, 2000
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
View 4 Replies
View Related
Feb 24, 2007
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 Related
Feb 11, 2004
I 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
View 1 Replies
View Related
May 27, 2004
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.
View 5 Replies
View Related
Sep 26, 2005
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,
View 4 Replies
View Related
Apr 4, 2001
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
View 2 Replies
View Related
Jul 10, 2001
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.
View 1 Replies
View Related
Jan 29, 2001
My 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
View 1 Replies
View Related
Mar 7, 2001
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 Related
Aug 18, 1998
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
View 1 Replies
View Related
Feb 23, 2001
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
View 3 Replies
View Related
Jul 1, 2004
Has 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.
View 1 Replies
View Related
Mar 2, 2004
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
View 9 Replies
View Related
Nov 16, 2006
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.
View 3 Replies
View Related
Mar 29, 2007
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?
View 3 Replies
View Related
Nov 10, 2007
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
View 17 Replies
View Related
Jul 20, 2005
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 Related
Oct 24, 2006
I 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
View 1 Replies
View Related
Aug 14, 2006
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!
View 4 Replies
View Related
Aug 23, 2001
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.
View 2 Replies
View Related
Nov 5, 2002
Hi,
My transaction log backup task for the production database which normally takes about 10 seconds has been running for almost about 2 hours now.
Is there any way to stop it without restarting sql services? The scheduled task was stopped from the jobs but spid is still in 'runnable' status. I should not kill 'backup log' or 'xp_sqlmaint' tasks.( It doesn't solve the problem anyway. the SPID goes in rollback status and stays like that until server is rebooted). Any way to find out what causes this problem and how to prevent it?
Thanks,
Shaili
View 2 Replies
View Related
May 18, 2005
How to capture information of any jobs that are hanging( for example, that are running for more than 6 hours) and send an alert in the email with job name and duration it has been running?
Thanks,
View 10 Replies
View Related
Feb 23, 2005
Hello,
I'm hoping someone could help me with a problem.
We often run a certain large stored procedure which is basically a select from a number of tables (these tables can be quite large 1million+ rows).
Usually the stored procedure runs in under 3 seconds, however, on occasion through query analyser we can manage to get the procedure to pretty much time out - there are no other processes running on the database, so why does the process sit there as 'sleeping' rather than finish?
Has anyone else had a similar problem?
Any ideas? :confused:
Thanks in advance,
Stu.
View 4 Replies
View Related
Jun 22, 2007
Hi folks,
I have a simple SSIS package I built to migrate data from a SQL db on one server to another SQL db on another server. All it does is: Execute SQL task to disable some triggers on the target table, Data Flow Task to pull the data from the source (a simple sql query) into the destination table (using OLEDB), then another Execute SQL task to re-enable the triggers.
This package ran fine several times during my testing. However, this morning it's not working anymore. When I run it and check its progress, the "Validation has started" is the only thing runs, then nothing else happens... no error messages, no timeouts, nothing in the output window besides "DTS.Pipeline: Validation phase is beginning".
The only thing that has changed is one change to a date in the where clause of my source query, so I'm baffled on why it no longer runs.
I'm pretty new to SSIS, so I could easily be missing something obvious here.
Anyone have any ideas on what's up, or who can point me in the right direction? I'm currently reading up on how to debug and add more comprehensive error handling, in hopes of finding a better description of the problem.
Thanks in advance,
Geoff
View 4 Replies
View Related
