Blocking Transaction
Mar 29, 2006
I have a 3 simple packages get called from master package. First package populates time dimension (stored proc), second one populates transaction type dimension(stored proc) and the final one populates date dimension.
I set the TransactionOption = Required in the Master package, every thing else (package & component) set TransactionOption = Supported.
I have been testing transactions, basically I made time and transaction type dimension to fail and the package did roll back. I am stuck in the date dimension.
Within Date dimension I got a stored procedure which populates calendar date attributes into Wrk_Date table. Then I have a data flow task which reads from thats working table, but it cant access it. I tried running SP_WHO2 command, and the status is SUSPENDED, being blocked by id -2.
I saw someone had similar problem and I did read Ash's comments. I did try to change the isolation level, didnt help.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74372&SiteID=1
Any thoughts please?
Thanks
Sutha
View 4 Replies
ADVERTISEMENT
Jul 20, 2005
We are having a really big problem with a zombie process/transactionthat is blocking other processes. When looking at Lock/ProcessIDunder Current Activity I see a bunch of processes that are blocked byprocess 94 and process 94 is blocked by process -2. I assume -2 is azombie that has an open transaction. I cannot find this process tokill and it seems that this transaction is surviving databaserestarts. I know which table is locked up and when I run a select *from this table it never returns. Does anyone have any ideas as tohow to kill is transaction.Any help is appreciated.A. Tillman
View 4 Replies
View Related
Jul 20, 2006
Hello,
I seem to be misunderstanding the way transactions work with service broker queues. We have developed and deployed a service broker application that 5 queues and a windows service for each queue on multiple servers (3 currently). Due to a last minute issue, we had to not use transactions when the services executed a recieve and I am not updating the code base to use transactions and am running into blocking issues. One of the services runs for 90 seconds (spooling to the printer) and all of the servers block on the receive operation for this queue. I thought that if I was receving messages from a single conversation, other receives against this queue would not block.
Thanks,
Jim Stallings
View 9 Replies
View Related
Oct 1, 2007
We have a web-based third-party application that has both background processes and user activity requests running in the same database (SQL Server 2005 SP2). The problem is that a background process will start a long-running transaction and hold an exclusive lock on a few rows in a given table (a small table, <100 rows). The web clients need to scan this same table, but when their "select *" statements get to those locked row(s), the web client queries stall waiting for that exclusive lock to be released. This effectively brings the entire web front end to a halt because all clients must hit this table for each user action. I realize that this is the classic lock condition that multiversioning databases like Oracle, PostgreSQL, SQL Server Compact Edition, and other databases do not suffer because they don't use shared read locks like SQL Server. But since we're on SQL Server for this app, what is the way to get around this problem? Modifying the clients to use WITH (NOLOCK) is not an option... there will be major consistency issues unless the clients run in Read Committed or higher. Any ideas? We could tweak this app if needed. Does SQL Server 2008 introduce multiversioning or at least some mechanism to get around this problem? I did not see it mentioned on the Microsoft site, but maybe I missed it. Thanks in advance.
Austin
View 6 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
Dec 22, 2006
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
View 8 Replies
View Related
Jan 8, 2008
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
View 2 Replies
View Related
Aug 15, 2000
One sql transaction is in rollback state and optimization job is trying to restructure index on the same table that this transaction is using. As a result,
transaction is in 'blocking' state and optimization job is in 'blocked' state. 'Kill process' doesn't do anything. How can this situation be resolved?
View 1 Replies
View Related
Nov 26, 2001
Is it possible that blocking can occur between a select and update statements running at the same time on the same object??thanks for the reply!
Sheila.
View 1 Replies
View Related
Nov 1, 2007
Anyone out there having "self blocking" issues since upgrading to SP4 on SQL 2000? Enabing trace flag 9059 has not helped, is this a parallelism issue ?
thanks,
Paul
View 7 Replies
View Related
Jan 6, 2004
Any ideas?
I execute a sproc from QA.
It interogates a folder:
Select @Command_String = 'Dir ' + @FilePath + '*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @Command_String
[/code[
Which I then Parse and get the details...
Set up a CURSOR (Booo hiss....)
[code]
DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]
WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN
Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)
I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)
SET @cmd = 'bcp ' + @db_name + '..' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + ' in '
+ @FilePathAndName
+ ' -f' + 'd:DataTax' + SUBSTRING(@File_Name,1,CHARINDEX('.',@File_Name)-1) + '.fmt'
+ ' -S' + @@servername + ' -U -P'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + '''' + ', NO_OUTPUT'
INSERT INTO #XLAT_BCP_Results(Col1) Exec(@Command_String)
MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH
A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time....
I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...
When I do sp_who, it shows the blocking...
I'm so confused....
View 8 Replies
View Related
Aug 31, 2006
Hi just read an article on the internet that states
"A quick way to resolve a blocking problem is to disconnect the connection blocking the rest of the users. All you have to do is ask your database administrator to execute the KILL statement. Obviously, this is not a permanent solution, and won't really help anything except take care of an immediate need."
Now when i run sp_who ... i see that the spid that is blocking is the same as the spid itself....
Now the above article states that killing the one that is blocking the rest of the other spids is ok ...
But what if its the same SPID that is blocking ...what does it signify & whats the solution
Thanks
View 2 Replies
View Related
Nov 13, 2007
Hi Gurus,
I am running a simple SELECT statement on my TempDB
SELECT * FROM TempDB..SysObjects
When I run this , it's getting blocked by some other Process which is not at all relevant to the above Qry. First it gets blocked by one Stored Proc say Proc1 ( The Proc1 is just a SELECT statement on a table which just has four rows ). So I just kill this PROC1 and again its gets blocked by some other stored Proc PROC2 which again is a simple stored Proc and it runs in not time.
Any Suggestions as to where to start.
Thx in Advance
Venu
View 13 Replies
View Related
Jul 23, 2005
hi,I am using SQL SERVER 2000.Problem that i am facing is when ever I check locks in EnterpriseManager I findfollowing blocking - :1) sp_cursoropen;12) sp_cursorclose;13) sp_cursorfetch;1We dont have any control over application(source code).Whateverwe have to do, has to done from database side.can anyone guide me to solve this problem.Thanks
View 4 Replies
View Related
Dec 13, 2007
I have one batch jobs running 4 time a day and web application.
We have hosted the site long time already but this month we are facing the store procedure blocked problem.
The problem is that when user submit the form when during the execution of batch job, two storeprocedure are blocked.
One store procedure that is using from batch job read the table "A" and another store procedure that is using from web app update the table "A".
Then deadlock is occure. but i don't understand it is hosted long time already. Please let me know if you know how to solve this issue.
Thanks,
Aung
View 6 Replies
View Related
Jan 14, 2002
Hi ALL!
Can a sleeping process block another process sleeping/runnable?It's happening in our SQL server.Can someone expalin??
thanks.
Di.
View 1 Replies
View Related
Apr 24, 2002
I have a powerbuilder application that is connecting to SQL Server. We get very frequent DeadLock situations. Is there a way that I can check to see as what SQL is causing the Lock?
Thanks in advance for any suggestions...
-Raj
View 1 Replies
View Related
May 29, 2001
I have upgraded a MS SQL database from 6.5 to 7.0. The database functioned fine in 6.5, now I have a table that is locking due to a blocking process. If I kill the process all is fine, but am trying to determine what is causing the process to hang. Has anyone experience any similar situations.
View 2 Replies
View Related
Jul 10, 2001
Hello,
In the morning at 9:32 one of the spid was blocked and got time out msg for select statement. the statement was sp_execute wht is sp_execute?
can anyone guide me?
Thanks
View 2 Replies
View Related
Jul 13, 2001
Hello,
I've a problems with blocking
simple select * from table1
is blocked
how to know which statement is blocking
select * from table1 is spid 30 blocked by spid 26
and in current activity spid 26 has no select or any statement
how could i know the spid 26 statement ?
And can just select statement be blocked?
wht should we do to monitor locks?
Thanks
Binoy
View 2 Replies
View Related
Nov 15, 2000
I know that spid 22 is blocking 2 other users. How do I tell who that user is? Is this just matching the same spid# in the process info in EM and if it doesn't give me an NT name it's probably because they're coming through the application and I can't figure it out?
Thanks
View 2 Replies
View Related
Feb 4, 2001
Hi there,
I've a encountering a problem with "Blocking" and would highly appreciatd, if anyone out there please advice how to get to the bottom of this. the problem in as follows:
Every now and then, my SQL Server Freezes and when i look at the process info, i see some user connections in blocked mode and some in blocking. i dont know, where to start looking for. my first question would be, what should I do here? Should i kill the blocked user or blocking user? Also, Whats the difference between this two modes (blocked and blocking) and why does this happening? can i trace the user id which is causing this problem?
Experts please advice, what to do in the near future to aviod this.
I search at microsoft support site and came up nothing good about this problem.
Appreciated, If anyone out there advice on this.
Many thanks in advance.
Sindi
View 1 Replies
View Related
Apr 19, 2000
When an SA attempts to change permission on any object they are shown as blocked as expected. However as new users access the object the SA's process is continuely blocked until the object is free. Can anyone explain this??
Thanks
David
View 3 Replies
View Related
Dec 7, 1999
Hello...
Is it normal in SQL Server 6.5 the user who only running the query blocking the other user who try to update/add the records?
note: The query is a complex SQL.
Many Thanks!
View 2 Replies
View Related
Jun 2, 2005
found many blocking on SQL2000 enterprise by insert statement, the same insert statement failed in the sql/w with violation on the primary key.
however, the appl insert also got the same error from log, but still in 'blocking' instead of to be 'killed' by the pk violation.
what would be the options to deal with this issue?
thanks
-D
View 5 Replies
View Related
Nov 17, 2005
Please advice.
I have a sql2K SP3 active/passive failover cluster running on W2K SP4. I epxerienced a blocking scenario. The blocking process was killed only to find another process cause blocking. Killing the blocking process didn't resolve the blocking issue.
Eventually I had to fail over the SQL Server instance to another node and that brought resove the blocking issue.
What direction should I look for investigation and solution.
URGENT PLEASE.
Help appreciated
View 3 Replies
View Related
Aug 24, 2001
I have a vendor supplied database which seems to have a simple blocking problem. The blocking will occur when a user tries to delete from a table where another user might be inserting or selecting from. The table has only 3 columns and the delete statement looks fine. (they are not deleting the same record) This is on SQL 2000 sp1. has anyone seen this or have any ideas. The db is on a fast server (quad 2 gig of ram). I did have the thought on a problem I ran into with an older db on SQL 7 where the write-ahead RAID controller was actually causing a simlar problem. Any help appreciated
View 2 Replies
View Related
Dec 8, 2006
Hi All,
I have a hypothetical scenario. I have few developers who all know the sa password. I want to block one developer from accessing the SQL server eventhough he knows the sa password.
Do we have any mechanism in SQL Server 2000 to block client based on the IP address of the client?
SQl Server is installed on Windows 2000 server. It uses SQL Server & Windows authentication.
Regards,
Anand
View 8 Replies
View Related
Sep 4, 2007
I don't understand why a CREATE PROC from 3.5 hours ago is the head blocker. The SP wasn't changed since in months so this must be a compile/recompile? It's not a COMPILE lock though but a PAGE lock. It's certainly not the initial creation. What else can be done to debug this? It's happening and usually resolving itself in 4-6 hours or if I kill the head blocker myself. It doesn't happen every day but almost every day.
I've retrieved this info about the blocking from DMVStats and found similar info using my regular blocking info script also pulling info from DMV's.
I'm running SQL 2005 SP2 Enterprise.
statement started 6:35AM
waiting statement (PAGE lock):
insert into grades (blah,blah)
select blah blah from homework join blah blah
statement started at 3:01AM
blocking statement: =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[ResultsHomeworkDelete]
@homeworkResultId int,
@NumDeleted int output
AS
delete homeworksessions where homeworkresult_id = @homeworkresultId
set @NumDeleted=@@ROWCOUNT
delete homeworkresults where homeworkresult_id=@homeworkresultId
set @NumDeleted=@NumDeleted + @@ROWCOUNT
View 3 Replies
View Related
Sep 23, 2005
I'm wondering if I'm doing this right. Wondering about the transactionsand error handling. (Do I even need to put BEGIN TRANSACTION ANDCOMMIT TRANSACTION in there?)I think that this sproc is causing occasional blocking:Alter Procedure sprocINSERTSTUFF@Col1Data int = Null,@Col2Data nvarchar(255) = Null,@Col3Data ntext = Null,@UniqueID int OUTPUTASset nocount onset xact_abort onDECLARE @err intDECLARE @ServerDate DateTimeSELECT @ServerDate = GETUTCDATE()BEGIN TRANSACTIONINSERT INTOtblStuff (Col1, Col2, Col3, DateCreated, etc.)VALUES(@Col1Data, @Col2Data, @Col3Data, @ServerDate, etc.)SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN@err ENDSELECT @UniqueID = SCOPE_IDENTITY()COMMIT TRANSACTIONBEGIN TRANSACTIONINSERT INTOtblMoreStuff (UniqueID, DateCreated, Col1, Col2, Col3)Values(@UniqueID, @ServerDate, @Col1Data, @Col2Data, 'Text Label: ' +isnull(Cast(@Col3Data AS nvarchar(4000)),'<none>')SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN@err ENDCOMMIT TRANSACTIONSELECT @err = @@error IF @err <> 0 RETURN @err
View 9 Replies
View Related
Oct 19, 2005
MOM contains its own Block Analysis script. It is a script written onVB, that looks for the monitored servers, creates for each oneMomCreateObject("SQLDMO.SQLServer"), afterwards connects to eachdatabase on each server and executes SELECT GETDATE() query. If MOMdoesn't receive answer for 6 minutes, it sends alert. Several timessince I started to monitor my servers I received the next alert:The program "SQLDMO_789" has been blocked for 6 minutes on databaseBurstingDataWarehouse in the SQL instance MSSQLSERVER. The definedacceptable blocking threshold is 1 minute(s). "SQLDMO_789" is runningon SPID 134 as login NT AUTHORITYSYSTEM and is blocked by SPID 133.The resource id is KEY: 10:2:1 (a2007950f190)SQLDMO_789 - is the MOM itself (number varies from time to time). 10 -is BurstingDataWarehouse database. As far as I can judge, MOM connectsto the server succesfully (otherwise, how can it know SPID?) The serveritself worked fine at that time - nothing unusual, all the jobsfinished succesfully including the heavy ones. What can block SELECTGETDATE() query for 6 minutes???
View 1 Replies
View Related
