Process Block Itself
Jul 3, 2007
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
View 4 Replies
ADVERTISEMENT
Feb 11, 2008
Ho do I clear a kill process blocking others to run
View 3 Replies
View Related
Apr 11, 2007
Process ID 59:0 owns resources that are blocking processes on Scheduler 2
I am getting tons of this into sql server log second day in a row (2000 sp4)
Noticed heavy usage prior
What is causing it? Solution?
Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
View 1 Replies
View Related
Oct 29, 2007
i'd like to block all other processes that interact with my database while my archive process is running on that database. Can this be done?
View 4 Replies
View Related
Jan 25, 2008
Does an sp_dboption 'dbname','single user',true command execute succesfully when a background process is working on a database.
View 3 Replies
View Related
Dec 3, 2014
I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
View 1 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 8, 2008
I've written a couple blocks but I have no idea when it comes to this one.
Create a PL/SQL block to retrieve the last name and department ID if each employee from the EMPLOYEES table
for those employees whose EMPLOYEE_ID is less than 114. From the values retreived from the employees table, populate two PL/SQL tables,
one to store the records of the employee last names and the other to store the records of their department IDs.
Using a loop, retreive the employee name information and salary infromation from the PL/SQL tables and doisplay it in the window,
using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables.
Any help helps
View 1 Replies
View Related
May 18, 2006
If I've got a 64-bit OS such as Windows XP Professional x64 Edition running and I have the SQL Server 2005 Express Edition, is AES permittable or does TRIPLEDES still need to be used?
View 1 Replies
View Related
Apr 10, 2008
Hello,
I have been experiencing a lot of blocking activity in my database. My trace results show that the following extended stored procedures sp_prepexec and sp_cursorfetch are executed by both the user causing the block and the user tha is blocked. Any ideas as to how to rectify this situation will be appreciated.
Regards,
Albert
View 3 Replies
View Related
Dec 11, 2006
HI,
i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Whereas the following block works fine and the Catch block executes.
Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Any idea why is it so?
View 5 Replies
View Related
Mar 5, 2008
Can anyone guide me to write check block in SQL Server?
I Mean if blocking occurs, just inform people.
View 3 Replies
View Related
Mar 18, 2008
Hi there! I need such thing: I have a data in Table A. When I get this data, I also increment this data by one, what I want is block Table A in order to other people will not get access to data.. I think about transactions, but are transactions reaaly blocks a table's content?
P.S. For example in MySQL there are Lock table command for blocking table for reading/writing. I need such thing/
Thanks
View 8 Replies
View Related
Oct 25, 2007
hi there,
to retrieve more then one record with a cursor we can use block cursor, so we have to set the cursor attribute SQL_ATTR_ROWSET_SIZE to some number...i am not getting any code example on this.
do anyone know this???
View 6 Replies
View Related
Apr 12, 2006
Hi:
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb
go
create table t1 (a int)
create table t2 (b int)
create table t3 (c int)
Begin tran
Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)
IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------
Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------
Drop table t3
-------------------------------------------------------------
Set xact_abort on
Begin try
Begin tran insertNow
Insert into t1 values (1)
Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3)
commit tran insertNow
End try
Begin Catch
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;
-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END
End Catch
View 9 Replies
View Related
Oct 2, 2006
Hi all,
here my question :
I have 2 applications.
connection 1.
one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1
connection 2.
an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.
Can I do this ?
connection 1
select max(grp) from orv WITH HOLDLOCK
connection 2
what should I use to avoid lock when I need to insert into orv. ?
thanks for your quick answer
View 1 Replies
View Related
May 18, 2004
Hello, everyone:
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
ZYT
View 1 Replies
View Related
Sep 27, 2005
hi,
i've performed a test on 2 machines based on the topic above, but it doesn't seem like blocking each other... This is the context...
I've created a table call TEST_DEL i.e.
quote:CREATE TABLE test_del
(v_id INT,
desc CHAR(3)
)
In machine 1, i'm login as USER 1 and try to insert a set of records into the table
quote:
Machine 1
~~~~
BEGIN
DECLARE @li_num int,
@li_start int
SET @li_num = 100000
SET @li_start = 1
WHILE @li_start < @li_num
BEGIN
INSERT INTO test_del VALUES (@li_start, 'zzz', 'xxx')
IF (@li_start > @li_num)
BREAK
ELSE
SET @li_start = @li_start + 1
CONTINUE
END
END
In machine 2, i login in as USER 2 to retrieve the records at the same time...
quote:
SELECT * FROM test_del
but, the system still allow me to retrieve the records at the same time... May i know when will "Writer block reader, reader block writer" occur and in what situation
Thanks in advance
View 1 Replies
View Related
Oct 10, 2006
Can anyone give a template for a stored procedure which involves a transaction and has a TRY CATCH block too...
Thanks in advance.
View 1 Replies
View Related