Waiting For Ole Db Resource And Cannot Kill Process In KILLED/ROLLBACK
Feb 21, 2007
I have a query to refer to the data source in as400 with ODBC 'iSeries Access for Windows ODBC data source' and a linked server with 'oledb for ODBC driver' in SQL2005 (9.0.2047)
Now we got 2 problems,
1. When we refer to the view with openquery e.g select * from openquery (abc, 'select * from dates')
This query continue to run without ending. In Activity monitor, we found that it is waiting for resource ole db
2. Since the process continue to run without ending, I kill the process in activity monitor or by command 'kill'. It shows the status 'KILLED/ROLLBACK' and it returns 'SPID 197: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.' in 'kill 197 with STATUSONLY'
In problem 1, I found some ppl encountered the same problem (waiting for resource oledb) with openquery to different data source e.g. vfp, oracle.
In problem 2, we can't restart the sql server for clear the unkillable process
I have a test database for the end users to test their select queries for reports. One of my users is writing queries that cause locking in the database. I killed the process last evening and they are in Killed/Rollback status but are still hogging 90% of the CPU resources for the past 12 hrs. I tried killing them several times but no go.
I know that the best way to clear of these processes is by restarting SQL Server. If that is not an option is there is any other way we can clean these processes?
Also the user running these queries has a read only and create view access to the database. From my experience processes that go into Kill/Rollback state after you kill them are processes associated with some update transaction. Since the user as far as i know is running Select commands would an infinite loop cause this ?
I was running a stored procedure it was suspended for about 11 hours so I decided to kill it now its in Killed/Rollback stage for 12 hours and when check the status of roll back it says "Estimated rollback completion: 0%. Estimated time remaining: 0 seconds." its using up CPUTIME 380000 and DiskIO 970000. How to do I stop this co.mpletely
Hi all, I am running Sql 7.0 with sp2. I have a memory 4GB. When i am running some batch jobs and replications i am getting following error number: ERROR NUMBER : 8645 A time out occurred while waiting for memory resources to execute the query. Rerun the query.
Can anyone suggest me how i have to resolve the above error, because i was facing this error very often. Pls suggest me a permanent solution.
When I was trying to get my maintenance plans to work there were many processes I had to kill. These processes were killed over a week ago but they still list in the Current Activity | Process Info list. Under Command it says "Killed/Rollback". If I go into QA and run kill 65 with statusonly it says the process is complete. How do I get these processes off the list?
I have a C# SQL 2005 .net stored procedure which scrubs a text file looking for characters not in a range of characters and replacing them with another character. This works fine except when the process is killed. When this happens the file handle of the file being scrubbed is not released. I use a try catch finally block when opening the file and the output file. The finally section fiushes the output file and closes all files and streams but still when I go to access the file again or use the file in explorer it says the file is still in use. Should I be handling this some other way? How do I know the files will always be closed correctly.
I'm trying to kill a bunch of processes in SQL 6.5 and I can't. I'm running the only machine with SQL tools installed on it (the server) and it won't let me kill them. I try the GUI screens and the Kill statement in ISQL_w. Is there any way around this?
I've stopped the SQL Server and rebooted the NT Server. Is there anyway I can get rid of these processes. They are locking some tables and keeping me from inserting data within my code. Very frustrating.
We are useing merge replication with MSSQL Server 2000 for our two location, we have more than 6 database in replication on has issue following error and few time not updated data due to this error -
The process is running and is waiting for a response from one of the backend CONNECTION
I have SQL Server 7.0 with SP2 on it and I am not able to kill one of the SPID's who is running a SELECT Statement. When I saw in the current activity in EM I see the STATUS as ROLLBACK, COMMAND as SELECT, APPLICATION as MS SQLEM-DATATOOLS, WAITTIME as 5375 and WAITTYPE as CXPACKET..
I also tried to reboot the machine of that particular SPID , but it still shows up in the Enterprise Manager.
Can anyone tell me how to kill this process and how to get around this Problem
Hi! After I killed maintenance process (57) in the current activity window and run ‘kill 57 with statusonly’ I got message: ‘SPID 57: Estimated rollback completion: 100%. Estimated time remaining: 0 sec.’ When I reopened current activity window I still see that SPID 57 is runable. Then I run select*from sysprocesses where SPID = 57 It also reports that process status is runable. What is the problem? How can I remove (and should I) this record from masterdb. Thank you, Natalia
Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.
Im just a newbie using SQL Server anyway i noticed in my present company that most of the process/Stored Procedure that are being executed or connection that was being made by the application is not being terminated or disconnected. they use very large IO and CPU resources making the server slow or sometimes hang.(well that was my diagnostics). i try to kill them one by one but it seems endless process is redundant. to cut my problem short im thinking of having an application that would automatically kill all this unwanted process but ofcourse i should specify the parameter which process to kill. is this possible? i saw the KILL statement in the online books but it seems uncomplete with what i wanted to accomplish.
Hi, Could anyone tell me why I am not able to kill processes even with kill spid. I had scheduled some db backups through DB Maintenance. All of them are running for days together when I tried to kill them spid. It still does not get killed. Can anyone tell me why
I have SQL Server 7.0 and I am not able to kill one of the SPID's who is running a SELECT Statement and showing as sleeping. When I saw in the current activity in EM I see the STATUS as sleeping COMMAND as SELECT, APPLICATION as Microsoft Transaction server, WAITTIME as 7877 and WAITTYPE as EXCHANGE and CPU 10074 Thanks in advance.
I'm having a problem killing a process in SQL 7.0. I have a spid that has been around for a couple of weeks! I try to remove that process by right clicking and selecting 'Kill Process' but nothing happens. The process originated when I deleted large amounts of data from a logging table. NOw I can not remove the process. I'm concerned that this might indicate the database is getting corrupted. Any suggestions?
I executed xp_cmdshell command. More than 24 hours this process still running. I tried killing this process with enterprise manager and query analyser, both gave me a message saying its successfully killed. But when i do a sp_who, the process still their executing.
how can I kill this process that's running xp_cmdshell
OS: windows server 2003 database version: sql server 2005
i want to kill user process.when i tried to kill user process using its spid... kill 57 i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds. i also found that this process has suspended status and wait_type is BACKUPTHREAD one more thing i wait to complete rollback transcation since last 3 days. but after 3 days getting same error and estimated time increase every time i issue KILL command. even i tried activity monitor to kill process.but no luck is there any way to slove this issue??? how to kill this process.?? well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue. your help is really appreciate. thanks, saurabh
OS: windows server 2003 database version: sql server 2005
i want to kill user process.when i tried to kill user process using its spid... kill 57 i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds. i also found that this process has suspended status and wait_type is BACKUPTHREAD one more thing i wait to complete rollback transcation since last 3 days. but after 3 days getting same error and estimated time increase every time i issue KILL command. even i tried activity monitor to kill process.but no luck is there any way to slove this issue??? how to kill this process.?? well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue. your help is really appreciate. thanks, saurabh
OS: windows server 2003 database version: sql server 2005
i want to kill user process.when i tried to kill user process using its spid... kill 57 i got error SPID 57: transaction rollback in progress. Estimated rollback completion: 52%. Estimated time remaining: 448373 seconds. i also found that this process has suspended status and wait_type is BACKUPTHREAD one more thing i wait to complete rollback transcation since last 3 days. but after 3 days getting same error and estimated time increase every time i issue KILL command. even i tried activity monitor to kill process.but no luck is there any way to slove this issue??? how to kill this process.?? well i havnt try to stop and restart sql server processes.because its producation server.will database complete shudown and server process restart help to overcome this issue. your help is really appreciate. thanks, saurabh
Hi! I have a small problem , but it's still a problem. I have a SQL Server Agent job that runs a .cmd file. This CMD is logged to a textfile. This process is locked, waiting for me to type a password, but I have nowhere to type that pass.
What I want to do is kill the process that i locking the logfile, because since the logfile is locked, the job cannot be started again (and it's a scheduled job). The jobs status is 'Not Running'. I have solved the problem by making the cmd write to another logfile, so the schedule will work, but the file is still locked, and I don't want to restart the server since it's a productionserver.
How to I find the process that is initialized from SQL Agent, and kill it?
I have this weird situation on one of the production database. Somebody ran a sql script couple days ago and must have hit cancelled. Somehow this script is still rolling back. The script is actually very simple statement..should not take more than couple miliseconds to run. Is there any way to see whether rollback is really in process or this process has simply hung? is there any other way to get rid of this rollback without stopping and restarting sql server services? Can it cause problems in getting sql server up again if I restart sql server services?
I have over 500 transaction records in a single DB process handling within SQL transaction (Begin, Commit, RollBack and End). Is there any limitation for the following rollbacktransaction function to handle more records (eg. over 500 records)? Public Shared Sub RollBackTransaction() Dim transactionObj As Object Try transactionObj = SqlTransaction.GetExistingTransaction If (Not IsNothing(transactionObj)) Then CType(transactionObj, SqlTransaction).RollBack() End If
Catch ex As Exception Throw New Exception(ex.Message) End Try End Sub
We upgraded QA and production to sql server 2012 last year ( in place) leaving the user databases at sql 2005 ( 90 ). A few months ago the QA user databases were set to sql 2012 compatibility mode. Management is worried about upgrading production and wants to know if we can quickly roll back.
I want to confirm that we can roll back using the same command, and if dbcc freeproccache can be used to avoid having to update all statistics.
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 90
This works fine in QA on my own test user database. No errors.
Is there any specific event i have to select in SQL profiler to monitor the process / user that kills active connection which is performing a batch data transfer. Any other alternative other than profiler that catches this (like XEvents)?
I installed 2K5 developer on my box. I was impressed with the Summary tab report functions and the reports available there. Then I ran the "Reporting services configuration" tool to get RS running, but now several of the summary reports are broken. There are several different error messages given in the summary window, depending on the report selected.
For example, the "server dashboard" now displays
Summary Unavailable
The information needed to display a summary page for the selected object is not available.
Error:
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
The next two reports work, but the "Scheduler Health" report displays
Summary Unavailable
The information needed to display a summary page for the selected object is not available.
Error:
Incorrect syntax near '.'.
"Memory Consumption" works GREAT, but "Activity - All blocking transaction" shows:
Summary Unavailable
The information needed to display a summary page for the selected object is not available.
Error:
Incorrect syntax near '.'. Incorrect syntax near '.'. Incorrect syntax near the keyword 'as'. Incorrect syntax near the keyword 'as'.
What could be the cause of such errors? Is there an "easy fix"? I wouldn't know how to "unconfigure" reporting services and have a feeling that wouldn't help anyway...
We are having a problem with an SSIS package. The package gets stuck when it runs a data flow component that reads data from 5 large tables, performs a union on the data, does a conversion on one field and then writes the data to another table. This all happens in the same database and the ssis package runs on the same server as the database.
When I look at the waiting tasks on the sql server I always see the following type of scenario. It is always the ASYNC_NETWORK_IO that is causing the others to wait.
SPID Wait type duration Waiting Address Blocking Address
In some cases the duration column goes up to several hours until we finally just kill the package. At the same time, we also see that session 118 is consuming very large amounts of memory (in sys.dm_exec_query_memory_grants). However, when we look at the dtexec process that is running the package on the server it only has a working set of about 100Mb. We've tried rewriting the dataflow task as a simple stored procedure and this goes through smoothly.
Can anyone shed some light on what is going on and how to avoid this type of locking situation (other than just using plain old T-SQL).
We have a two node cluster running W2K and SQL2000. The agent starts, but we cannot access any of the jobs. It gives us the 14258 Agent is starting, try again later... error message. We know that this is not the case, we have waited several hours without it "starting". The SQL Agent log indicates that the agent is waiting on SQL Server to recover databases, but all of our databases are up. It is almost as if during startup a flag did not get set to indicate that a database was recovered.
Has anyone run across this before? If so, did you ever figure out how to fix it?
