Hi All
When i see the result of Sp_who2, i see the status of a process as
"SUSPENDED" for some of the processes like logwriter, checkpoint etc.
and also found the same status for "IF @@TRANCOUNT>0 COMMIT TRAN"
Please let me know what does it mean in the context of SQL 2005
Thanks in advance.
We are facing lot of problems with Blocking,can any one help us in this matter,The problem is as follows
We have SQL Server 7.0 running on Nt4.0, and three web servers and 5 application servers are accessing SQL server.
Till Yesterday everything was fine,Suddenly today more than 18 processes
were blocked by other(Like chain),First i killed some blocking process,then it was fine,once again it started and continuously some processes are blocked by other,and i found that all blocking process are running from webservers.I ran SQL Profiler to get some information,but no use.
I am not understanding why suddenly it happend,because we have't modified anything.Is there any way to overcome this situation,this is production server.
because of this users are getting slow responce/no responce.
----Here i want to know why it happend?
---How to trace the problem and fix it
Can any one help me please
Thanks
Ananth
Is anyone aware of how to start a scheduled job once it has been marked as "suspended"? (SQL Server 7)
Thanks Brian
Hi All,
Using SQL Server 2005 w/ SP2 I have a simple select query with a parameterized WHERE clause that in Management Studio runs nearly instantly, everytime. Using the same login, when I run the same query using SQL RS the results (only 6 rows) takes upwards of 5 minutes. If I look at the Activity Monitor, it shows the process as suspended and indicates a CXPACKET. Everything I have read on the forums about this indicate it is an issue with not having SP2 , which I do. Or a 64 bit version, I have 32. Or for large bulk updates, I am doing a very simple select. Does anyone have any clue on this?
regards
Bill
Hi,
I have a database shown a SUSPENDED mode on both side of Principal and Mirror server. What should I do now?
1). Is the breaking the Mirror sesssion my only choice?
2). If I break it, how do I reestablish the Mirror again quickly. What is my potential data loss?
Thanks,
Hello,
On my sql server 2005 when I run sp_who, I often see process in suspended status. These process block
my sql server so its utilization is low.
Is there any way to kill suspended process automatically, or can I set any timeoutfor these process?
Hi,
I am having a problem with one package that attempts to do a join on two large tables. Within the data flow task control is the ole db source control which has the initial query that joins and filters the tables.
The total number of rows that should be returned are about 80-90million. Running this in debug mode shows the number of rows read/inserted into the ole db destination and it always hits the about the same number of records where it just stops/stalls/suspends around 75 million. I have read some possible solutions such as changing the max degrees of parrallelism but that has not worked.
I am currently using SQL Server 2005 64bit SP2 on a 64 bit platform. This is the only package that does anything like this and I am at my wit's end with this problem.
Please help!
Thanks,
Cheston
I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").
The data is stored something similar to the following...
Ticket Date Status
------ ---- ------
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"
This is the result i'd like to see....
Ticket Start Hold End Hold Time Suspended
------ ---------- -------- --------------
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...
Has anybody done something along the same lines? If so, could you point me in the right direction?
Thanks in anticipation
Jon
Hello. We recently ran into extremely large transaction log sizes on our SQL 2005 box...turns out they were not being backed up. After doing some research, I found that they should be backed up very frequently in order to kee their sizes down. I kind of inherited the mess, but got three maintenance plans set up:
FULL Backup that runs weekly
DIFFERNTIAL Backup that runs nightly
TRANSACTION LOG backup that runs hourly
However, since I implemented the plans there has been high CPU utilization at all times. I finally figured out what was causing it today...the BACKUP LOG process is hanging in "Suspended" mode when I view processes. I have to manually kill them to release the CPU utilization.
How can I trace the cause of the hang? Is there a better way to backup/truncate the log files?
Regards,
Matthew
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
View 4 Replies View RelatedI have checked my Production databases, both are went to Disconnected state, both are unable to connect to the application, i checked all the services and its working fine. and restarted the end points on Principal and Mirror, still issue not solved, after restart the witness server End point both the databases are working fine.
View 3 Replies View RelatedSP2 refuses to install for the Database Engine because €œthe previous upgrade did not complete€?, i.e. it detects a suspended installation. The same thing happened to me when I installed SP1. I then remember opening the maintenance dialog and completing the setup, after which SP1 installed just fine.
When I try to do the same thing this time, I€™m presented with the €?complete the suspended installation€? option, which when I run it reports the installation completed successfully. However, in reality the installation still remains in suspended mode for whatever reason. If I reboot and open the maintenance dialog, I€™m still presented with the €?complete the suspended installation€? option. I€™ve even tried to run it more than once, but it doesn€™t help. Needless to say, SP2 still refuses to install.
I don't know why the installation is in a suspended state. I know every part of SP1 installed successfully in the end, and the computer never crashed during the installation. I've been using it every day since then, without any problems. All other parts of SP2 did install successfully.
I created a feedback entry on Microsoft Connect, where you can find the log files created by Windows Installer. The URL is:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259606
Any ideas what I can do to resolve this problem?
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.
View 2 Replies View RelatedSQL7 SP1 NT4 SP5
Hi.
Very long story and I will not bore you with it. What I need to do is call a stored procedure and from within that stored procedure, initiate other sp and get out before those other sp are finished running.
I know I can execute a job but my volume would be about 1 job per second with a life of 2-5 seconds. So in a very short period of time, I can stack up a large volume of jobs.
Anyone have any better ideas ?
Help Please !@!!
Craig Somberg
csomberg@stageone.com
Hi,
How to kill process???
I turn off workstation but the process still available in the sysprocesses table and in the Curent activity window.
I can't to kill this (and I can't to restart serever, because the users) .
If anyone know something about this problem.
Thanks
I have an SQL server with several (100) users connected. When I run a long running process, it severely impacts user performance. The long running process is a store procedure with several cursors.
Are there SQL configuration settings that would reduce the impact of these long running processes on other Users?
/*
The Following Stored Procedure helps to
Kill All Processes in a Particular DataBase
With Out Current Process
*/
Create Proc Sp_KillAllProcessInDB
@DbName VarChar(100)
as
if db_id(@DbName) = Null
begin
Print 'DataBase dose not Exist'
end
else
Begin
Declare @spId Varchar(30)
DECLARE TmpCursor CURSOR FOR
Select 'Kill ' + convert(Varchar, spid) as spId
from master..SysProcesses
where db_Name(dbID) = @DbName
and spId <> @@SpId
and spId > 50
and dbID <> 0
OPEN TmpCursor
FETCH NEXT FROM TmpCursor
INTO @spId
WHILE @@FETCH_STATUS = 0
BEGIN
Exec (@spId)
FETCH NEXT FROM TmpCursor
INTO @spId
END
CLOSE TmpCursor
DEALLOCATE TmpCursor
end
/* The Above Query Helps TO Change a Database in Single USer Mode Quickly*/
I have been needing to disconnect users often, before backing up a database or setting it to restricted users.
Each time, I have to go to Current Activity & kill one process at a time. Is there a way, by which I can kill all processes on a database or force out all coonections to it?
Thanks.
What is the max number of processes that can be running at the same time?
View 1 Replies View RelatedCan you kill processes in SQL Server 2K without stopping SQL Server and restarting it? I am using sp_who2 to get a list of active users and I see some accounts that are logged off but still showing up and I am trying to find a way to Kill these accounts and the processes they are doing.
View 3 Replies View RelatedFrom the system table sysprocesses, how to do in order to extract
the SQL command related to the processes ?
What are system views/tables, scripts used to monitor and obtain more information
about processes, connexions... ?
Hi all,
Can anybody tell me what are the background processes running in sqlserver databases.
regards
Susheel
I need to know exactly what VBA lines Create and Destroy SQL ServerProcesses - i.e. those visible in Enterprise Manager under Management[color=blue]> Current Activity > Process Info.[/color]Why?I am experiencing strange behaviour with Processes that are createdwhen I create a DAO Database Object with the following line:Set m_ResDatabase = DBEngine.Workspaces(0).OpenDatabase(strDSN, False,False, strODBC)This creates the process as expected.However the following lines don't always close the ensuing Process:If Not m_ResRecordSet Is Nothing Thenm_ResRecordSet.CloseSet m_ResRecordSet = NothingEnd IfIf Not m_ResDatabase Is Nothing Thenm_ResDatabase.CloseSet m_ResDatabase = NothingEnd IfIf Not m_ResWorkspace Is Nothing Thenm_ResWorkspace.CloseSet m_ResWorkspace = NothingEnd IfIt seems as if SQL Server keeps hold of the first two Processes andthen will release any subsequent ones.Can anyone shed any light in this - or any good web pages where Imight find some answers?Regards Chris
View 2 Replies View RelatedHi, just a quick problem/question. Why do processes in the middle of the package goto sleep? Is there a method of making them go?
View 2 Replies View RelatedI have a foreach loop which contains a call to an Execute process task. I want the Execute process task to run once for each element in my foreach, but I need to run as many Execute process tasks as my server can handle. What is the best way to do this? Any help would be greatly appreciated.
I had to move my developed software onto another computer in order to demo this. The new computer has IIS installed as well as VS2005 and SQL Server 2005. When I try to execute 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.)
I have worked on this problem for two days now and have to demo it first thing Monday. Any help would be appreciated.
I am receiving an error from my ODBC driver “Maximum number of DBPROCESSes already allocated.”
I confirmed that there are 25 connections and that this is the default. This is caused by error message 10029, SQLEDBPS, when the maximum number of simultaneously open DBPROCESS structures exceeds the current setting. I would like to increase this maximum.
I have found only two ways to do this. One is using dbsetmaxprocs using C and the other is using SqlSetMaxProcs using Visual Basic. My problem is that I am interfacing to SQL Server using a third party tool that is doing the lower level programming.
Is there some way that I can increase the maximum number of DB processes for all databases that are part of the SQL Server 7 environment, or can I set this value using a program that is called from a stored procedure?
Any ideas in this area will be greatly appreciated.
I wish to select processes from sysprocess that are SLEEPING and more than a certain time old (say 10 minutes) so that I may KILL them. I can get the query to do the select, but how do I KILL the process? I have tried selecting the SPID into a local variable and then trying KILL @var_name, but I get "Incorrect syntax near '@var_name'".
I have tried all of the resources that I can find, but without success. Is this possible? If so, how do I go about doing it?
Thanks in advance.
Matthew
Sorry, didn't quite know where this should go.
I'm reading Kimball Group's "Microsoft Data Warehouse Toolkit"
Has anyone got any experience in defining business processes? I'm struggling a little to look at my employer's recruitment busines and work out exactly what the real processes are.
Or maybe one of you has read something somewhere about this specific skill?
------------------------
Me: What do you want to know from your data warehouse?
Client: Err...Emm...Everything
Me: OK, that's great. That's all I need to know. I'll see you when it's done.
Hi ,
I want to capture all Processes,Applications and Servers (name) which is accessing(linking) to my server. Is it Possible without using Profiler?
We currently have a routine that "forks" out (to use the unix term)TSQLcommands to run asynchronously via SQL Agent jobs. Each TSQL commandgets its own Job, and the job starts immediately after creation.Sometimes we can have too many of these jobs running at the same, andthe box crawls to a slow speed until the jobs finish up.Is there a way we can limit the number of active jobs running under theSQL Agent at one time? Or is there away to limit the number of active(runnable) processes on SQL Server, in general?
View 2 Replies View RelatedIs there a common way to document ETL (packages / data flows)? I looked for documentation templates in the internet but didn't find anything which was useful.
Thanks,
Kenan