I am having a few problems using ASP-DB advertised on this site. Queries that were working rather quickly a week ago are timing out for no apparent reason. (asp-db seems to be buggy)
I have no idea as to what is causing the query (ie select * from tables X, Y, Z) to time out. The process id on SQL server says AWAITING COMMAND or SLEEPING.
Also, the processes on SQL server do not seem to die. Can this be accomplished via ASP or other means.
I ran a DBCC SHOWCONTIG on a large database. Because the execution was taking longer than I wanted I cancelled and then killed the process running the DBCC. This action seems to have abnormally ended SQLServer and subsequently crashed the entire NT server. Have you experienced any behavior similar to this when using DBCC and/or killed processes?
Hi One DTs package job running and we stop the job.Job was stoped but process not killing.We tried using kill spid but no use.please any body give suggestion. this production server.
I hope you can help me to find the way to resolve this issue.
When I've accidentally triggered a process in SQL 6.5 without knowing that it's going to hold a lot of resources and making the network very slow and end users will be started complaining about the slowness.
I've no other way other than the killing the process kill <spid>. Funniest part is, even it is not get killed even when I tried multiple times and the process is still active and still running.
When I tried to find the way out, One another guy stopped the SQL server and restarted again. That's it, it took a long time to stop and restart and ends up with the recovery mode of the database and it was running more than 3-4 hours to get into the usual mode.
Based on the scenario, what would be your suggestion when I've encoutered the same situation. I've triggered some application like DBCC checkdb and it's keep running for a long time. But I need to kill the process immediately without affecting any other process. Pls. advise me.
If we have a deadlock we will check in the error log and and find the spids which are involving in the deadlock. We will kill one of the process by using SPID (no) KILL. Is there specific steps to consider while killing a process
If I kill a blocked process, why does the current activity window still show the process? Both processes, blocking and blocked, are scheduled tasks. Also, the blocked process is still listed as a running task in the manage scheduled task window.
Hi everybody,We have a very large database and high transaction volume. Time to timethese transactions are locking each other and decrease the performanceof the database. Is there any way that I can automate the killingprocess when blocking and deadlock time is exceeded in certain timeelipsade? Can somebody help me on this please?Regardsasa.
we have tables that load overnight. Sometimes a user will try to run a query up against that table while the table is loading and a deadlock occurs. I do not notice this until I get into the office. By this time many tables have not loaded. Is there a way to have SQL6.5 automatically Kill deadlock processes.
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then 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.)" this my sql script use [master]Go -- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO -- Switching to our databaseuse [DatabaseName]GO CREATE SCHEMA schemaname AUTHORIZATION usernameGO ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO /* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter' -- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter] -- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber] -- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
Hey, How can we kill a process initiated by an Extended Stored Procedure. For example, I issued exec xp_cmdshell "C:Notepad.exe" and scheduled as a job and it started running and it never finished. I dunno watz goin on behind the scene and i couldnt kill the process. Anybody who knows how to do it,please help me out. And my process is still running in the server for more than two days. Do i have to restart the server? If so, everytime when it get a problem like this, am i supposed to restart?
Hi, i was try a very simple transaction, but it show me error: Exception Details: System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. Below is my code, wat i did wrong for tis... i tried 2 days just for transaction alrd.... pls help.Sub bt1_click(sender as object, e as eventargs) dim i as integer Dim myTrans As SqlTransaction dim strExDate,StrSAPNum,strID,StrPartNum,strRemark,strWAID,strQty,strQty1,StrPartNum1 as string '================ loop thru n insert data=============================== try for i=0 to DgData.items.count-1 strExDate = CType(dgData.Items(i).FindControl("tbExDate"), textbox).text strRemark = CType(dgData.Items(i).FindControl("tbRemark"), textbox).text strQty = CType(dgData.Items(i).FindControl("tbQty"), TextBox).text StrSql="Insert into tbl_GrDE(Qty, ExDate, Remark, EntBy) Values " & _ (@Qty, @ExDate, @Remark, @EntBy)" ObjCmd=New SqlCommand(StrSql, ObjConn) With ObjCmd.Parameters: .Add(New Sqlparameter("@qty", strQty)) .Add(New Sqlparameter("@ExDate", strExDate)) .Add(New Sqlparameter("@Remark", strRemark)) .Add(New Sqlparameter("@EntBy", session("User_ID"))) End with ObjCmd.Connection.Open() myTrans = ObjConn.BeginTransaction ObjCmd.Transaction = myTrans ObjCmd.ExecuteNonQuery() ObjCmd.Connection.Close() next myTrans.Commit() catch ex as exception response.write("error") myTrans.Rollback() end tryEnd SubRegardslife's Ng
Please could anyone help I run an restore on a specific database overnight, in order to do so I have to kill all user connections. When I try to kill all user SPIDs some still remain ? , why please can anyone help me !
I have a spid that I'm not able to kill, this were a select statement from with in access 97 application using a DNS connection. So even that I reboot the client pc and kill the spid it still shows as active with status RollBack. We have similar problem before and the only way that it disappear were re - starting Sql. (system Sql 7.0 with sp1 and Access 97)
I have a script that I use to see if someone has been logged in for too long. Does anyone know how to take a varible spid and kill that login. I tried using kill @spid, but that does not work. Any Suggestions?
I have a problem. I am trying to kill a spid that is blocking updates to a table. The spid number is '-2'. I am using KILL with UOW and I am getting this error:
Server: Msg 6112, Level 16, State 1, Line 1 Distributed transaction with UOW {FCF8D536-27ED-11D6-9CF2-0002A56BDA54} is in prepared state. Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.
Users are connecting through MTS server. I am runnnig SQL2000 sp2 +hotfix, on NT4.0.
Has anyone encountered this problem before, and has a solution for it (besides rebooting the MTS and SQL Server)?
Just wondering if there is any way to kill a thread within an sqlerver process. The thread we are trying to kill is a rollback statement that has been running for a very long time.
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?
Hi Peepz! my problem is this i am managing more than 3 servers which has a many users. this servers have one common problem. the most users uses high cpu utilizations what make it worst is even if the process is already done for a long time (status = sleep) they still uses high cpu or IO utilization. One time i ask to confirm one user if they are really having that process and found out that the user have gone home already and no other is using thier computer. And assuming that we have more than 20 users with the same case it really make the server slow and occationally hangup. i try to kill these process/user but i think killing is not an option. Kindly help pls. are there any way to refresh connections or terminate it, how do you handle this situations?
Thanks, Keez.
If you give me a fish ill eat for a day but if you teach me how to fish ill eat for life. :beer:
I am working with a report performance issue.The report is a consolidated report of 4 other report. I need to remove one of the report from that. How to do?
I did Index defragmentation a week ago . for 1 database only , In the middle of rebuild I kill the process twice cause It takes more than 1 hour so I killed it and wonder how many high level fragmented indexes left ...
I am having a problem with an application that does not kill timed out connections. This is normally not an issue, but when something causes the timed out connections to build up, it stops the frontend from working correctly. The frontend developers are trying to figure out how to change their code to check for and drop timed out connections at the application. Until then, I need a way to check for timed out connections at the database and drop them there via a job that will run every 10 minutes or so. I have to make sure that only timed out connections are dropped and not active ones. Any suggestions?
I would like your help with the Foreach loop container. Boy, am I having issues with using it for looping through an ADO.NET dataset or what!?!? My control flow has a data flow task that is executing a Data Reader task (creating a .NET dataset for me). Now I go back to the control flow and add a Foreach loop container to loop through each record in my dataset. But which type of the Foreach loop container should I be using? I see an option to use Foreach ADO.NET schema enumerator but not sure how to configure it. Also I used the Foreach ADO option, but setting up variables to go through every column is an absolute pain in the you know what!!?!! I have about 200 columns and I want an easier way to refer to those columns in my transformation phase.
I was trying to extract data from the source server using OLEDB Source and SQL Server Destination when i encountered this error:
"Transaction (Process ID 135) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".
What must be done so that even if the table being queried is locked, i wouldn't experience any deadlock?
Hello all, I am running into an interesting scenario on my desktop. I'm running developer edition on Windows XP Professional (9.00.3042.00 SP2 Developer Edition). OS is autopatched via corporate policy and I saw some patches go in last week. This machine is also a hand-me-down so I don't have a clean install of the databases on the machine but I am local admin.
So, starting last week after a forced remote reboot (also a policy) I noticed a few of the databases didn't start back up. I chalked it up to the hard shutdown and went along my merry way. Friday however I know I shut my machine down nicely and this morning when I booted up, I was in the same state I was last Wenesday. 7 of the 18 databases on my machine came up with
FCB:pen: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf'. Diagnose and correct the operating system error, and retry the operation. and it also logs FCB:pen failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
I've caught references to the auto close feature being a possible culprit, no dice as the databases in question are set to False. Recovery mode varies on the databases from Simple to Full. If I cycle the SQL Server service, whatever transient issue it was having with those files is gone. As much as I'd love to disable the virus scanner, network security would not be amused. The data and log files appear to have the same permissions as unaffected database files. Nothing's set to read only or archive as I've caught on other forums as possible gremlins. I have sufficient disk space and the databases are set for unrestricted growth.
Any thoughts on what I could look at? If it was everything coming up in RECOVERY_PENDING it's make more sense to me than a hit or miss type of thing I'm experiencing now.
Dear list Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server
What Im trying to do is convert this cmd that works into an execute process task D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log the above dos cmd works 100%
However when I use the Execute Process Task I get this error [Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".
There are two package varaibles User::gsPreplogInput = ex.log User::gsPreplogOutput = out.log
