Wanting to know how do you detect and kill orphaned spid in SQL Server left behind by an XML service which was not completed normally. The client requests an XML service which open a connection, and due to network problem the client did not manage to call the another service which suppose to end the connection. Thus leaving behind in SQL Server orphaned spid which continue to hold locks. This created problem when the next user wants to access the resource.
The simplest way is to get all spid which are holding locks for more than a certan period and kill them. Is there a better way to actually detect orphaned spid?
I am not sure if this is actually a sql connection I didn't dispose of.The database the app use is franchise_search, login testHere are the results of sp_who before I start the web server and run the page1 0 background sa 0 NULL RESOURCE MONITOR 02 0 background sa 0 NULL LAZY WRITER 03 0 suspended sa 0 NULL LOG WRITER 04 0 background sa 0 NULL LOCK MONITOR 05 0 background sa 0 master SIGNAL HANDLER 06 0 sleeping sa 0 master TASK MANAGER 07 0 background sa 0 master TRACE QUEUE TASK 08 0 sleeping sa 0 NULL UNKNOWN TOKEN 09 0 background sa 0 master BRKR TASK 010 0 background sa 0 master TASK MANAGER 011 0 suspended sa 0 master CHECKPOINT 012 0 background sa 0 master BRKR EVENT HNDLR 013 0 background sa 0 master BRKR TASK 014 0 sleeping sa 0 master TASK MANAGER 015 0 sleeping sa 0 master TASK MANAGER 016 0 sleeping sa 0 master TASK MANAGER 017 0 sleeping sa 0 master TASK MANAGER 018 0 sleeping sa 0 master TASK MANAGER 020 0 sleeping sa 0 master TASK MANAGER 022 0 sleeping sa 0 master TASK MANAGER 024 0 sleeping sa 0 master TASK MANAGER 051 0 sleeping NT AUTHORITYSYSTEM HPDEV 0 msdb AWAITING COMMAND 052 0 sleeping sa HPDEV 0 ReportServer AWAITING COMMAND 053 0 sleeping sa HPDEV 0 master AWAITING COMMAND 054 0 sleeping sa HPDEV 0 ReportServer AWAITING COMMAND 055 0 runnable sa HPDEV 0 master SELECT 0 then I run the page and all I do is using (m_sqlConn = new SqlConnection(m_strSQLConnect)) { m_sqlConn.Open(); //Log_History(); //FillCompany_DropDown(); //m_sqlConn.Open(); //BuildCategoies(); }I open the connection never use it and return the using should close the connection.But after I close the page sp_who add the line56 0 sleeping test HPDEV 0 Franchise_Search AWAITING COMMAND 0Is this connection pooling or am I missing something?No other app or anything can use the test connection I just made it and changed the password. Jon
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
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
Dear, Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies. How can I kill this sleeping processes?Regards,Echo
I don't even know where to begin looking... I have a page that loads multiple web user controls... I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures). The problem is when the first page is rendered and each user control queries the database (SQL Server), it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item. So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping. My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How? What do I do to check either my code is creating the connection object properly. Thanks!
I have a table and a specific column inside this table. I know this table is being updated, by using sys.dm_db_index_usage_stats, I was able to determine this, by some process (stored procedure / SQL Job / etc), but the problem is, I am not sure what process is doing it.
How would I search our SQL Server 2008 database to find any process that manipulates this table / column (I only care about Inserts / Updates and Deletes, but do not really care for SELECT).
What are the basic processes for maintaining an SQL SERVER 2005 Database? Is there a top ten list of things you should do periodically to all SQL SERVER 2005 databases?
How the code inserted works.....I am trying to do the same thing with
GetProcesses
So I want to insert into a table the results of all the processes on a server and determine if a process/service is running.......
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.IO; using System.Collections;
The following question applies to SQL Server 8.0.2187 (2000 + SP4+916287/914384/898709/915065/915340):
We have now twice had an incident where the same SQL Server has stopped responding. The only workaround is to restart the SQL Service. After this occurs, the log is filled with the following messages:
2007-09-10 16:42:14.29 spid3 Process ID 197:320 owns resources that are blocking processes on Scheduler 1.
2007-09-10 16:42:14.31 spid3 Process ID 74:324 owns resources that are blocking processes on Scheduler 5.
We haven't been able to pinpoint a cause or reporduce the problem on a dev server. I've seen several posts about this issue online but not many answers. Does anyone have any advice on how to troubleshoot this issue?
Hello. I have a 32-bit SQL 2005 (SP1) server that is my current Production server. I also have a 64-bit SQL 2005 (SP1) server that will become my Production server. I have several SSIS packages that load/refresh data on a nightly basis to a few of my databases from DB2 (MVS). I have the packages setup on the current Production server (32-bit) and all is working well through the Microsoft OLE DB provider for DB2. However, on the 64-bit server, I am experiencing some issues with the SSIS packages failing due to large loads. Loads that are loading tables with 500K, or less, data seem to run without issue (through SQL Agent Jobs). But, larger table loads are failing.
I do have a linked server set up on the 64-bit server to the 32-bit server, for other processes. And because of this I have lightweight pooling turned off on the 64-bit server (because of distributed querying). Lightweight pooling is turned on on the 32-bit server. Could this be what is causing some of my issue? Since I don't have the lightweight pooling option turned on (on the 64-bit server), am I not getting the proper amount of through-put for my 8 dual core CPU server?
Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?
I'm new to SQL server 2005. I installed SQL server 2005 and had some problems with the original install. I therefore uninstalled SQL server 2005. Rebooted and than installed SQL server 2005 again. Everything appears to have installed correctly except for the fact that when I start SQL Server Management Suite I get 2 copies running in the foreground. The last copy of the SQL Server Management Suite shows a dialogue box that contain a warning sign (yellow triangle) with no text but an OK button is in the dialogue box. When I press the OK button, this instance of SQL Server Management Suite kills itself. The first instance is now given window focus and it brings up the "Connect to Server" dialogue box. I can connect to my local server and run a script to add columns to the database. But everytime I bring up SQL Server Management Suite, I always end up with 2 copies running and I have to hit the OK button to get rid of the second copy. Can anyone help me get ride of this annoying problem?
On SQL 6.5 we have had a couple of instances recently where an orphan connection cannot be killed and the only way to get rid of it is to stop and start the service. Does anyone know of any other way to get rid of that orphan connection?
As I do analysis on a database I am finding multiple clustered indexes defined on a single table. I know this is not possible since SQL Server only allows one clustered index per table. With more research I found these suspected clustered indexes have a corresponding row in sysindexes but without a correlating id match in either sysobjects or syscolumns. Is this orphaning of indexes common and what is a recommended solution. Thanks, Randy Garland
/************************************************************************************* This procedure should be created in the Master database. This procedure takes no parameters. It will remap orphaned users in the current database to EXISTING logins of the same name. This is usefull in the case a new database is created by restoring a backup to a new database, or by attaching the datafiles to a new server. *************************************************************************************/
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_fixusers IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>' END
GO
CREATE PROCEDURE dbo.sp_fixusers
AS
BEGIN
DECLARE @username varchar(25)
DECLARE fixusers CURSOR FOR
SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers INTO @username
WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END
CLOSE fixusers DEALLOCATE fixusers END go IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>' go
Had to rename a SQL 2000 box and now the scheduled maint jobs that were created under the previous name of the box cannot be deleted via EM. Can anyone offer help?
i have an app that starts a transaction (some deletes, etc...) in the middle of this transaction the connection drops (say we pull out our network cable) but this will leave the transaction still running and it will be orphaned. i know i can kill them in sql server, but what i'm looking for this to somehow not happen at all. I tried SET XACT_ABORT ON but no luck.
Is what i need even possible?
_______________________________________________ Causing trouble since 1980 blog: http://weblogs.sqlteam.com/mladenp SSMS Add-in that does a few things: www.ssmstoolspack.com
There is still a problem with mirrored (mapped) SQL Users. If you mirror a database where an application connects with an sql user, the mapping (login / user) will be lost on the mirror server. After a failover occurs, it it not possible to log onto the new principal database because the database use will be an orphaned user and has to be remapped to the login (using sp_change_users_login 'update_one', 'user', 'user').
Is there any chance to do it in a system trigger? What is the firing event after the failover occurs? I've tried something like following, but it doesn't fire.
We have 4 sql servers which service 4 load balanced web servers (with sticky sessions) and we recently brought one of the four SQL servers online.
In the last week the new SQL Server 2000 Standard (SP4) has been exhibing orphaned connections and attempts to use invalid connections out of the connection pool on the app servers. This manifests itself in General network Errors and Timeouts (when the sql is not really doing much other than simple table requests).
Does anyone have any experience in what might be causing this on a new server. I've asked our networking people to verify that the network routing and firewall are set the same as the other three servers. The application is the same acrossed all of the app servers so I've ruled out any issues with the application not closing connections.
I€™ve recently set-up database mirroring between two servers in the same domain: DMZSQL01 and DMZSQL02 with a witness of DMZSQL03. The mirroring as all gone according to plan.
Set up all the users/databases on the Mirror (DMZSQL02) and then do a back-up/restore to sync the databases and then enable the mirroring, this has all gone fine and we have lots of synchronised/mirrored databases now. However, if I do a failover the logins on the mirror are not valid, they are in SQL Server and also the database but they don€™t work. I€™ve read through other posts and found links to this SQL Server article: http://support.microsoft.com/kb/918992/
This has had no effect though. Do I need to remove everything off my mirror and start again setting up the users first and then doing a backup/restore or is there some other way. Both servers are reporting the SID€™s are the same for the logins which do not work. However the principle_ID is different, I'm quite sure if this is a problem or not??
Can anyone point me in the right direction of what to do next? Or have any ideas.
Looking through the archives, I didn't see any articles that specifically addressed the problem, so here it is:
SQL 7.0, NT4SP6, 2G ram, 4x
I've got a user process as follows: Status: ROLLBACK Command: SELECT Application: Enterprise Manager Wait Type: EXCHANGE Login time: 06/25/00 4:07:05AM Last batch: 06/25/00 4:07:20 AM The last TSQL command batch is a correlated subquery with grouping...
Apparently it hung and the user quit ungracefully.
No other processes are blocking it, but periodically it blocks other processes, including some index maintenance I need to perform.
I have tried to kill it with Enterprise Manager and with the KILL [id] command. Neither have worked.
Mgmt is reluctant to bounce the database, as am I.
I am copying a database to an alternate server by restoring a full backup onto the new server. However, whether I create the logins prior to the restoration or not the user accounts in the database no longer map to logins in the master.
Unfortunately it is not simply a case of dropping the acounts as they own objects in the database.
My best solution to date has been to use the sp_addalias to alias logins of the same name to the original user. However this is a far from ideal situation as one cannot readily tell that the logins are aliased and there must be an additional overhead in doing this.
I seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.
How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.
Been asked to restore an orphaned MDF file leftover after a botched uninstall - no .bak file. Tried to reattach, but got an error, I don't think it had been detached. My initial answer was, "No, very likely can't be done".
Am I right? Or is there a way of attaching it that doesn't require it to have been detached?
All is happened when a server crashed some weeks ago and it was removed from my network. After that, under my SQL Server 2012 I get an orphaned account which cannot be removed. This account is a computer account related to an old SCOM installation.
If I try to execute the command DROP USER [NETWORKSERVERNAME$] I get the following error message:
The database principal has granted or denied permissions to objects in the database and cannot be dropped. Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped.But if I run the following command to know all permission granted to the account, I get an empty result:
select * from sys.database_permissions where grantee_principal_id = user_id ('NetworkSERVERNAME$');
Furthermore, following the instructions provided by the official KB for troubleshooting orphaned users, I get another error [URL].
sp_change_users_login 'update_one', 'NetworkSERVERNAME$', 'NetworkSERVERNAME$' Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114 Terminating this procedure. The User name 'NetworkSERVERNAME$' is absent or invalid.
The only thing I can retrieve is 15 permissions that this account granted to another account in the past:
select * from sys.database_permissions where grantor_principal_id = user_id ('NetworkSERVERNAME$' -- class class_desc major_id minor_id grantee_principal_id grantor_principal_id type permission_name state state_desc 17 SERVICE 65538 0 5 19 SN SEND G GRANT And more 14 rows…
resolve my issue and safe delete the account SERVERNAME$? I need this because I have to reinstall SCOM with the same computer name on another server, but installation fails due to this behavior.
I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.
On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.
To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.
I've restored my database from backup on another SQL 2005 server and can't login, could you tell me what is the best way to repair this on SQL 2005, please
