Can't View Locks / Process ID
May 29, 2006
Hi,
We have SQL 7 running on Windows 2000 Server. For some reason we are
unable to view Locks / Process ID from workstations running Windows XP
SP2 with Enterprise Manager. Nothing shows up in the window on the
right. All it's says at the top of the window is "There are no items to
show in this view" If we use Enterprise Manager on the server, we can
view the Locks / Process ID. We can view both, Process Info and Locks /
Object from the workstations and server, just not the Locks / Process
ID. We used to, but I don't know what changed. I'm thinking it might
have to do with upgrading to XP SP2, or a Windows update, but I'm not
sure.
Has anyone out there experienced this? Or does anyone know the
solution?
Thanks,
Mark
View 1 Replies
ADVERTISEMENT
Jan 31, 2008
SQL 2000 is easy to see what process is blocking and what processes are locked. I find 2005 confusing and can't find any documentation on what the new icons mean and or details on interpreting the object/process lock screens.
Thanks, Cindy
View 6 Replies
View Related
Sep 22, 2005
Hello,i have a doubt here, after a close the connection, i still can see the last stored procedure active in the LOCKS/PROCESSID in enterprise manager of SQL SERVER.I think i'm closing the connection well
Try
If Me.ID > 0 Then
Dim strSql As String = "SP_CHANGECLASSCONTENTS"
Dim sqlcommand As New SqlCommand(strSql, New SqlConnection(ConnStr))
sqlcommand.CommandType = CommandType.StoredProcedure
sqlcommand.Parameters.Add("@PerformActivation", SqlDbType.Bit).Value = 0
sqlcommand.Parameters.Add("@PerformInactivation", SqlDbType.Bit).Value = 0
sqlcommand.Parameters.Add("@PerformDelete", SqlDbType.Bit).Value = 1
sqlcommand.Parameters.Add("@PerformUndelete", SqlDbType.Bit).Value = 0
sqlcommand.Parameters.Add("@PermanentDelete", SqlDbType.Bit).Value = CBit(PermanentDelete)
sqlcommand.Parameters.Add("@Class_ID", SqlDbType.Int).Value = _id
sqlcommand.Connection.Open()
sqlcommand.ExecuteNonQuery()
sqlcommand.Connection.Close()
Else
Err.Raise(10205, "CLASS", "CLASS ID is empty")
End If
Catch ex As Exception
_err = ex.Message
Return Err.Number
End Try
View 3 Replies
View Related
Jan 20, 2005
Hi,
I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database.
Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.
Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.
Thanx in advance.
Regards,
Sushma
View 1 Replies
View Related
Jul 16, 2015
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15
[code]...
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
View 9 Replies
View Related
Jan 30, 2008
Hi
I've been using Activity Monitor to view the SQL for a process id. However, I notice that not all process ID's have any SQL to view
I do see when a statment spawns multiple threads but even clicking all the threads turns up no SQL.
1. Why does Acitivity Monitor only show SQL for certain queries?
2. What system tables can I view to see the SQL beingexecuted?
I appreciate your help
View 5 Replies
View Related
Aug 5, 2015
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
View 7 Replies
View Related
Sep 1, 2006
Good afternoon everyone, I have written a view that pulls customer demographic infomration as well as pulling data from multiple scalar-valued functions. I am using this view to pull and send data from one database to another in the same SQL server. The problem that I am having is that I am running this import as a scheduled job in windows. The job is taking almost 24 hours to complete this task. The total number of records that are being pulled is around 21,000+. I have tried removing the functions from the view and it only takes the view 20 seconds to pull the demographic information from the same 21,000+ records but when I add the function calls this is where the time to complete goes through the roof. Has anyone encountered this before if so what would you suggest doing? Any help would be appreciated. Here is the syntax for my view: SELECT TOP 100 PERCENT CUS_EMAIL AS Email, CUS_CUSTNUM AS MemberID, CUS_PREFIX AS Prefix, CUS_FNAME AS FirstName,
CUS_LNAME AS LastName, CUS_SUFFIX AS Suffix, CUS_TITLE AS Title, CUS_STATE AS State, CUS_COUNTRY AS Country, CUS_ZIP AS ZipCode,
CUS_SEX AS Gender, CAST(CUS_DEMCODEA AS nvarchar(20)) + ',' + CAST(CUS_DEMCODEB AS nvarchar(20))
+ ',' + CAST(CUS_DEMCODEC AS nvarchar(20)) + ',' + CAST(CUS_DEMCODED AS nvarchar(20)) AS DemoCodes,
dbo.GetSubScribedDateMLA(CUS_CUSTNUM, CUS_EMAIL) AS MLASubscribedDate, dbo.GetSubScribedDateMLP(CUS_CUSTNUM, CUS_EMAIL)
AS MLPSubscribedDate, dbo.GetSubScribedDateLDC(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubscribedDate, dbo.GetMLAExpiration(CUS_CUSTNUM,
CUS_EMAIL) AS MLASubExpireDate, dbo.GetMLPExpiration(CUS_CUSTNUM, CUS_EMAIL) AS MLPSubExpireDate,
dbo.GetLDCExpiration(CUS_CUSTNUM, CUS_EMAIL) AS LDCSubExpireDate, dbo.IsProspect(CUS_CUSTNUM, CUS_EMAIL) AS AGMProspect,
dbo.IsCurrentCustomer(CUS_CUSTNUM, CUS_EMAIL) AS AGMCurrentCustomer, dbo.IsMLAMember(CUS_CUSTNUM, CUS_EMAIL) AS MLAMember,
dbo.IsMLPMember(CUS_CUSTNUM, CUS_EMAIL) AS MLPMember, dbo.IsLDCMember(CUS_CUSTNUM, CUS_EMAIL) AS LDCMember,
dbo.CalculateTotalRevenue(CUS_CUSTNUM, CUS_EMAIL) AS AGMTotalRevenue, dbo.GetPubCodes(CUS_CUSTNUM, CUS_EMAIL)
AS ProductsPurchased, dbo.GetEmailType(CUS_CUSTNUM, CUS_EMAIL, CUS_RENT_EMAIL) AS EmailType, CUS_COMPANY AS Company,
CUS_CITY AS City
FROM dbo.CUS
WHERE (CUS_EMAIL IS NOT NULL) AND (CUS_EMAIL <> '') AND (CUS_EMAIL_VALID = 'Y') AND (CUS_EMAIL LIKE '%@%.%') AND (CUS_RENT_EMAIL = 'Y' OR
CUS_RENT_EMAIL = 'R' OR
CUS_RENT_EMAIL = 'I') AND (CHARINDEX(' ', CUS_EMAIL) = 0) AND (CUS_EMAIL NOT LIKE '@%')
Thanks in advance Michael Reyeros
View 9 Replies
View Related
Nov 14, 2007
Hi,
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?
cherriesh
View 4 Replies
View Related
Dec 3, 2007
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.
View 13 Replies
View Related
Mar 11, 2008
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
Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
thanks in advance
Dave
View 1 Replies
View Related
Jan 30, 2007
How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.
Thanks,
Monisha
View 1 Replies
View Related
Mar 20, 2008
I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.
For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.
I am using the Execute Process Task and the process parameters I am providing are:
WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat
The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"
the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.
The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :
[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]
Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.
Can anyone help ?
View 9 Replies
View Related
Aug 20, 2014
I'm pulling data from Oracle db and load into MS-SQL 2008.For my data type checks during the data load process, what are options to ensure that the data being processed wouldn't fail. such that I can verify first in-hand with the target type of data and then if its valid format load it into destination table else mark it with error flag and push into errors table... All this at the row level.One way I can think of is to load into a staging table then get the source & destination table -column data types, compare them and proceed.
should I just try loading the data directly and if it fails try trouble shooting(which could be a difficult task as I wouldn't know what caused error...)
View 3 Replies
View Related
Feb 14, 2007
Hi Folks,
I am having this table locking issue that I need to start paying attention to as its getting more frequent.
The problem is that the data in the tables is live finance data that needs to be changed and viewed almost real time so what I have picked up so far is that using 'table Hints' may not be a good idea.
I have a guy at work telling me that introducing a data access layer is the only way to solve this, I am not convinced but havnt enough knowledge to back my own feeling up. (asp system not .net).
Thanks in advance
View 1 Replies
View Related
Jan 6, 2012
We are facing deadlock issue in our web application. The below message is coming:
> Session ID: pwdagc55bdps0q45q0j4ux55
> Location: xxx.xxx.xxx.xxx
> Error in: http://xxx.xxx.xxx.xxx:xxxx/Manhatta...Bar=&Mode=Edit
> Notes:
> Parameters:
> __EVENTTARGET:
> __EVENTARGUMENT:
[code].....
View 2 Replies
View Related
Feb 17, 2007
Hi,
I'm trying to upload the ASPNETDB.MDF file to a hosting server via FTP, and everytime when it was uploaded half way(40% or 50%)
I would get an error message saying:
"550 ASPNETDB.MDF: The process cannot access the file because it is being used by another process"
and then the upload failed.
I'm using SQL Express. Does anybody know what's the cause?
Thanks a lot
View 1 Replies
View Related
Nov 15, 2007
Hi. When I try to start a package manually clicking the Start Debugging button I get this after a little while:
Cannot process request because the process (3880) has exited. (Microsoft.DataTransformationServices.VsIntegration)
How can I prevent this from happening? This happens every time I want to start the package and
every time the process id is different. Here it is 3880.
Darek
View 13 Replies
View Related
Dec 20, 2006
select * from sysprocesses
How can I determine whether a process a system or user?
View 3 Replies
View Related
Oct 11, 2007
Hello,
I have had a full lock on my sql server and I have a few logs to found the origin of the lock.
I know the process at the head of the lock is the 55 process.
Here are the information I have on this process:
Spid 55 55
ecid 5 5
Ecid 0 0
ObjId 0 1784601646
IndId 0 0
Type DB PAG
Resource 1:1976242
Mode S IS
Status TransID GRANT GRANT
TransID 0 16980
TransUOW 00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000
lastwaittype PAGEIOLATCH_SH
CMD AWAITING COMMAND
Physycal id 1059
Login time 2007-07-05 04:29:53.873
nat address DFF06EBF974D
Wait type 0x0046
HostName .
BlkBy .
DBName grpprddb
CPUTime 54331
DiskIO 1059
ProgramName
Would someone know a way to identify the origin of the process 55?
I have already tried to execute the following request:
select * from SYSOBJECTS
where id=1784601646
But I have had no returns.
Regards,
Renaud
View 3 Replies
View Related
Aug 24, 2006
I have a File System Task Copy file operation to copy a file in an SSIS package. The package when scheduled as a job fails with the following error:
The process cannot access the file 'C:ETLConsignmentAppleAppleRawFile.txt' because it is being used by another process.".
However when I right click on the package and execute it manually from the Integration Services it runs successfully without any problem. I am not certain on how to resolve this issue any inputs will be much appreciated.
Thanks,
Monisha
View 19 Replies
View Related
Feb 6, 2008
Error: 0xC002F304 at Rename file 1, File System Task: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".
When running two File System Tasks after each other, with the same file, the file is still locked when running the second task. Resulting in an error: 0xC002F304 at Rename file 1, File System Task: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".
I found a workaround by addind a Execute Process Task before the second File System Task that pings to the localhost. This results in a 5 second delay, but there must be a better solution. Anyone?
View 9 Replies
View Related
Nov 23, 2006
While configuring log shipping using SQL 2000 Ent, the copy process failed with this message :
The process cannot access the file because it is being used by another process.
does anyone know what cause this ?
SAP R/3 is configured with the system, could this be the problem ?
View 3 Replies
View Related
Apr 7, 2008
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'
View 10 Replies
View Related
Nov 10, 2003
I have read that even during read procedures (sql select statements), sql server uses row locking. I know that you can use the NOLOCK keyword, but if you don't everytime that a user makes a selects statement on a table, does sql server really lock those rows, and if so are they then unavailable to another user who wants to make a select statement at the same time on that same table? That does not seem like it would be the case otherwise it would not scale well. Thanks for any clarification on this.
View 5 Replies
View Related
Feb 13, 2002
I am using SQL Server7.0. I opened a table through the Enterprise Manager and left it open. In the Query Analyzer when I try to update a field on that table(more than 2000 rows), it goes on running. When I watched the Current Activity, it shows that the update process is being blocked by the select query. But if I try to update the same column for less than 1500 rows, there is no blocking issue and the update occurs immediately. Can anybody let me know why this is happening and what should I do to prevent it?
View 1 Replies
View Related
Mar 28, 2000
I am using Sql Server 7.0
To I got the following error message. Can some one tell how to solve this issue.
Server: Msg 1204, Level 19, State 1, Procedure OPEN_OBJECTS, Line 2
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.
ranga.
View 1 Replies
View Related
Sep 26, 2000
what the best way to control locks, if inserting couple thousands records from one table to another.
View 5 Replies
View Related
Feb 20, 2001
Hi
I have a big query which updates around 14000 rows at a time if i place a lock on the table and others try to update the same table is it possible to let them know that table is locked by someone else.
View 1 Replies
View Related
Sep 19, 2002
2 quick questions :
1) How do I keep multiple users from editing the same record without locking the entire table? What would be a 'standard' way of handling this?
2) How do I keep 2 people from posting the same record?
Please help me understand locking, THANKS!!!
View 2 Replies
View Related
Apr 11, 2005
I have a stored proc which will be entering/updating a record into a table. The table's key is an integer field which I may have to increment by one. I know I can use
declare @nextid int
set @netxid = max(id) from table
insert @nextid into table
Is some kind of lock the best way to approach this?
View 4 Replies
View Related
Jun 15, 2004
Hello,
I have a problem in SQLSERVER 2000, when I execute a Query, the table get locked for insert or any other transaction, even for other queries.
Does SQL Server have a kind of lock mode different of Oracle ?
How do I solve this problem ??
View 5 Replies
View Related
Jun 16, 2004
Hello There !!
I have a very big problem, with SQL SERVER 2000. I want to know about the locks with select.
When I execute a Select (so big), and I try to update or Insert into one of the tables that I invoke in the select, I get locked.
Is there in SQL Server, something like a Select for update, that could be causing the problem ???
Is there any way to select rows from a table without locking it ?
I really have a big problem with this, and I don't know so much about sql server !
Thank you so much !!!
View 14 Replies
View Related
