How To Identify Locks In SQL Server And Kill Them
I have to find locks which have been caused by some particular users before the scheduled jobs kick off and kill the locked processes so that the scheduled job executes without failing. How to do this in SQL server? please help
Thanks in Advance
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
You Cannot Use KILL To Kill Your Own Process. Why?
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. Thanks
View Replies !
View Related
How To Identify What Consumes Cpu In Sql Server
Hi all I use 64 bit 2005 server with 8cpu and 8G of memory. This server is accessed by large number of intensive or not so intensive programs. I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good. But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it. If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good. Task manager shows that all of it 75% attributed to sql server. So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves. Thank you, Gene.
View Replies !
View Related
How Can I Kill Sleeping Processes In SQL Server?
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
View Replies !
View Related
How To Identify Service Pack 2 In Sql Server Is Installed Or Not
when i run sql server 2005 select @@version Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1) how to determine that i have installed sql server service pack 2 do i have to restart the server to see the effect, how will it show that it is service pack 2
View Replies !
View Related
Kill Cmd Process Started From SQL Server Agent
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? Thanks! Bix
View Replies !
View Related
How To Identify Permissions For SQL Server Tables && Stored Proc. Via VB Code
I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!
View Replies !
View Related
SQL Server Locks?
I have a simple web application using ASP.NET/C#/SQL Server 2000/Windows 2000 I'm having problems with the server "hanging" for ~15 minutes. Disk/CPU are idle so this isn't a resource issue. In Enterprise Manager, under "Current Activity" I see several processes waiting on: LCK_M_X LCK_M_S LCK_M_S NETWORKIO How do I investigate this? My application is so simple, I have trouble imagining how a lock-related problem could ocurr? There is absolutely no concurrent processing done on this server. There is only one user running this app and there are no separate threads spawned. Although this problem is sporadic, this happens the most while doing the following: - Open connection1 (for SELECT) - Open connection2 (for UPDATE) - Open SqlDataReader through connection1 with SELECT statement - Iterate through - For certain records issue UPDATE statement via connection2. Here are some sample code snippets. Default ADO.NET options and everything should be cleaned up through C# "using" blocks without relying on garbage collection. using (SqlConnection selectConnection = LeadsDatabase.OpenConnection()) { using (SqlConnection updateConnection = LeadsDatabase.OpenConnection()) { using (SqlCommand dbCommand = new SqlCommand(sql, selectConnection)) { using (SqlDataReader reader = dbCommand.ExecuteReader()) { while (reader.Read()) { using (SqlCommand updateCommand = new SqlCommand(sql, updateConnection)) { updateCommand.ExecuteNonQuery();
View Replies !
View Related
SQL Server Locks
I am having a problem with an SQL6.5 server locking up periodically. I go into the error logs and there is the following message: 98/12/08 20:16:30.64 kernel dpdb_asyncread: mirror: Operating system error 6(The handle is invalid.) encountered There is also this entry in the Event Application Log: Event ID: 17055 Source:MSSQLServer Type:Error Category:Kernel Description: Mesg 17053 : dpdb_asyncread: mirror: Operating system error 6(The handle is invalid.) encountered I have already searched that ever valuable MS knowledge-base for OS error 6 and found exactly ZIP! Can anybody shed some light on this? Thanks! Andy Roche Senior Consultant WARCOM Services
View Replies !
View Related
Sql Server Has Run Out Of Locks
Everytime I try to open the design-mode of a table from enterprise mgr, I get this error 1204.--- SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS. There no other users but me. Locks have been configured to their max (over 200000000). Don't understand whats wrong. Please help..
View Replies !
View Related
Locks In SQL Server
Hi All, We have one intranet system working absolutely fine since 9 months. But, all at sudden locking problems started and its just like a nightmare. I ran SQL traces, I checked event viewer and I made changes in my front-end coding so that all my recordsets use proper locking features. But, it didn't help. I know that the presentation of problem is very vague. But, I dont know what else I can write about it. Any help will be highly appreciated. Thanx
View Replies !
View Related
Sql Server Locks
We've experienced persistent hang up on our application. I tried opening the enterprise manager->management->Current Activity->Locks/Object and I've seen ridiculous entries like master.dbo.spt_values, SIS.dbo.feesclass,tempdb.dbo$##lockinfo205. I don't what this mean? does it tell us that something wrong with the application that does not release locks? please help me.
View Replies !
View Related
How To Log On The Server For Reconfiguring Locks
Situation: When I ran a query and bumped into a message that is "SQL server has run out of LOCKS. Re-run your command when there are fewer active ussers. or ask your system administratroe to reconfigyre SQL Server with more LOCKS." Then, I tried to reconfigure/increase the locks. It could not let me do it. So, I tried to relog on the server, then I kept getting the same message as above. Questions: Is there a way to log on the server and reconfigure the locks? Do I have reinstall the SQL server? I appreciate it ahead of time.
View Replies !
View Related
Locks Problem On SQL Server...
Hi, I am getting the following message from the SQL Server 7.0. I have increased the locks but still it is getting messages on few days. Coould you please advise me what is the main reason for ran out of locks and how can solve this problem otherthan increase the locks.If needs to be increased what is the calculation method to arrive at the number of locks. Thanks >RAvi " SQL Server Alert System: 'Demo: Sev. 19 Errors' occurred on CSQWBDB1 DESCRIPTION:Error: 1204, Severity: 19, State: 1 SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS. " The present configuration: name minimum maximum config_value run_value locks 5000 2147483647 225000 225000
View Replies !
View Related
Sql Server Running Out Of Locks
Hi, i have installed sql 7.0 sp1 on a server that frecuently report the error: 1024 "SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.." I reconfiguered the locks twice, but the error persist. What do you recomend to do to avoid this error?? thanks.
View Replies !
View Related
Data/index Locks In SQL Server
I found that SQL Server (I'm working with 2000), when doing SELECTslike:SELECT * FROM Table WHERE key='xxx'first puts a lock (shared) on the index (I'm assuming here there is anindex on the "key" column), then on the table.On the other hand, UPDATEs are treated differently if they touchindexed columns:UPDATE Table SET key='yyy' WHERE key='xxx'first locks the table, then the index.This sometimes leads to deadlocks. Is there any way to avoid them (forexample, by changing the order in which SQL Server acquires locks)?Thanks in advance,Alex Drobychev
View Replies !
View Related
LOCKS Issue On SQL Server Cluster
Hi All, I am frequently getting an error of Unable to acquire a LOCK at this time. Something about too many users logged in. What is the limit of LOCKS in MS SQL 2005 ? When I switch the cluster nodes, the LOCK issue goes away. The issue only occurs when someone tries a DDL command. Advice.
View Replies !
View Related
Linked Server Transaction Locks...
Hey all, I've been experiencing an issue related to linked server. Here is the scene: - Application A queries View V located in SQL Server S1 - View V accesses Table T located in SQL Server S2 using a linked server The problem is: - Application A start the transaction and it simply gets locked. It throws no errors nor exceptions. Important: - S1 and S2 are in different machines - When S1 = S2, then the application successfully access View V. Any suggestions?
View Replies !
View Related
SQL SERVER 2000 SELECT Waiting And Locks
Is anyone have already encountered such a problem : When a table is locked by several locks (key, pag), either a SELECT request which does not need to lock any row cannot succeed. In SQL Server Enterprise Manager, I see that my request is waiting for a lock "LCK_M_S" !!! How I can avoid this kind of situation which seems to me completely abnormal. I have never been faced with this kind of problem in Oracle. Thanks for anyone who will help me to resolve this "bug".
View Replies !
View Related
Help. MS Access Linked To Sql Server 2K Locks Up On BeginTrans
All of a sudden an access application is locking up when trying to update or delete a record on a table linked to a database on a sql server 2000. When I step through the code it locks up at the line gwspXXX.BeginTrans. I can select and view info using this app. I can use query analyzer and delete a record from the same table that the access application is trying to get to when it locks up. I have other applications that use this same database(although different tables using a different named workspace) any idea on what is wrong The application gives the error " The expression on click that you entered as the event property produced the error: The expression may not result in the name of a macro, the name of a user defined function or the event procedure. The may have been an error evaluating the event fuction or macro" as I said one day it works fine the next - not so much- no code has changed. and the line it stop on is the BeginTrans. Thank you very very much for any help
View Replies !
View Related
SQL Server 2005 - Locks Persisting Indefinitely
I have a web application that crashes/hangs IIS worker processes under heavy loads. It seems that if such a crash happens during a transaction that is updating the database (SQL Server 2005 on Windows Server 2003 SP2), SQL Server hangs onto the locks indefinitely. I.e. no other process can access the locked table or rows. Restarting IIS has no effect. This means that the lock persists even though, in theory, the connection has been dropped. This is true even after many hours later (e.g. our stress test failed at 7:20 pm, and when we check the next morning we are unable to access our database). We have confirmed that this is a locking issue using SQL Server tools. The only way that we have been able to regain access to the database is by restarting SQL Server. Clearly this is an unacceptable failure mode for SQL Server. It makes it very difficult to guarantee availability for our web application. Has this been observed by others? Is there a work-around, a fix, a patch? Thanks Quincy
View Replies !
View Related
A Lot Of Locks In SQL SERVER 2005 Using OLEDB Connection
hello, I have an application wrote in Power Builder 11 and use OLEDB connection for SQL SERVER 2005 DB. it works excellent till multiple users access the application. 2 seconds passes and locks appears and blocks all the DB! I realy don't know what is the reason. In past I used SQL Native driver and it works well with the same application!!! (other reasons caused me to change the connection. not this). Does someone can help me????????
View Replies !
View Related
ReportViewer, Server Report Locks Network Account
I have a WinForms application deployed through ClickOnce which includes a ReportViewer control. This ReportViewer is configured to run a Server Report from the ReportingServer on a nearby machine. This has all been working fine. Recently my windows logon required me to change my password and now since this change using the report viewer causes my account to be locked out. The Viewer gives me a "401" error. I have checked the construction of the report on the server and the Data Source is definately using Integrated Security. While my account is unlocked I can happily run the report through IE on any machine that I'm logged in on. But as soon as I run the report through the ReportViewer control in the application then my account locks out. This doesn't appear to be affecting my colleagues who also run the application, but I can't comment on if/when their passwords have changed. It's as though my ReportViewer is not using my latest security details to contact the ReportServer. Can anybody help? It's getting embarassing having to call my Network Administrators every 5 minutes to get my account unlocked while I work out what's going on. Best Regards, Kevin Prendergast.
View Replies !
View Related
Sql Server 2005 Queries Start Timing Out Because Sa Is Acquriing And Releasing Locks
I have a co-worker whose sql server 2005 is exhibiting strange behavior. We have already re-installed sql server 2005 and service packed it to SP2 to try and see if the behavior stops but it has not. Every so often during the day sql server 2005 will start to slow down to the point that my co-worker's queries begin to time out. He turned on profiler to look at what was going on behind the scenes. We see where sa is releasing and acquring locks to the tune of 180,000 rows in a fifteen minute span when this behavior starts so does his time outs. He has reporting options and analysis services installed but not configured. His only connection is to his local database. Occasionally, you see a number like - (03000d8f0ecc) appear in the Text Data column in profiler for sa. I read something about reporting options using sa for clean up but I don't think that is what is happening here. Does someone have a clue as to what is happening and a way we can prevent the behavior? It is affecting his ability to work on his application. Thx
View Replies !
View Related
Can't Kill ODBC Connection When Running Report In Report Server
Hi, I have a report including a ODBC datasource which is linking to a progress database instead of SQL Database. Actually, a number of connection are created when running the report and then those connections automatically killed in order to save the system resource (Running report in Report Manager). However, those connections can't be killed when running report in Report Server ? Do i configurate the related configurtaion file in order to solve this problem? Pls give me some suggestion. Thank you so much in advance! Elmer
View Replies !
View Related
Kill Won't Kill
I have a replication log reader SPID hanging. When the logreader tries to run again, it fails due to Error 14151 Replication log reader - task "blah" failed. Another log reader is replicating the database. I do a sp_who2 on the database and identify the spid that is running the logreader, from here I usually kill the spid and the log starts up again no problem. Now the spid WON"T DIE!!! Any help would be appreciated. Thanks Susan
View Replies !
View Related
Kill
Hi, We are planing to write a script that execute frequently to kill processes that are running for more than 1 minute. Does anyone knows the logic to filter out system or other required SQL processes from this auto kill script regards Priv
View Replies !
View Related
How To Identify An Array
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.
View Replies !
View Related
How To Identify Hot Tables
Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.
View Replies !
View Related
Identify Neg And Pos Values
How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit l've written it in layman's terms .Is it wise to use substring or they is a better method...... select case when amount is (negative) then 'C' when amount is (positive) then 'D' else end from table_Tran
View Replies !
View Related
SHOWCONTIG - Need A Bit Of Help To Identify If
Greetings. I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched. However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) ) Tipps, suggestions, help, all is very wellcome! :-) DBCC SHOWCONTIG scanning 'TInsurance' table... Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17 TABLE level scan performed. - Pages Scanned................................: 1275 - Extents Scanned..............................: 225 - Extent Switches..............................: 224 - Avg. Pages per Extent........................: 5.7 - Scan Density [Best Count:Actual Count].......: 71.11% [160:225] - Extent Scan Fragmentation ...................: 74.67% - Avg. Bytes Free per Page.....................: 520.0 - Avg. Page Density (full).....................: 93.58% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View Replies !
View Related
Kill Connection
Hello,I do maintenance on the Back endand have like 10 - 20 connections open...specialized scrips i run and theydont need to be stored proc'sis there a way to kill the connection when the script is finished...from myclient side.....not just disconnect...cause server still has the pool of the connection...iwant to kill the pool'd connection alsothanksDave P
View Replies !
View Related
Kill Process
Hi Peepz, 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. Thanks all
View Replies !
View Related
Kill Process
Hi Peepz, 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. Thanks all
View Replies !
View Related
Kill Processes
Can 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 Replies !
View Related
Kill Old Transactions
Does anyone have a script for killing transactions older than 24 hrs? I have been having problems with users bootting out and leaving open transactions, I'd like to clean up all the orphans early every morning. I am trying a WHILE on DBCC OPENTRAN. We are using TCP/IP sockets, apparently they never notify the db server that the user has dropped, sockets stay open by default. TIA
View Replies !
View Related
How To Kill Process
SQL Server 2000. 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
View Replies !
View Related
Kill Processes
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.
View Replies !
View Related
|
TRACKER
