Blocking SPID ... No Apparent Reason
Jun 14, 2004
Hi,
I hava a JAVA application that updates a SQL2000 (SP3a)database.
The application handles different types of "jobs" which effectively update the DB.
One job in particular appears to block all subsequent jobs. It comprises of a large amount of inserts/updates in a single transaction. This is necessary as it is an "all or nothing" scenario - so we cannot break the transaction into smaller ones. The transaction appears to succeed as it reaches the COMMIT TRAN statement without error.
However the records do not get written to the database.
EM indicates a large number of locks held on the tables accessed by the transaction and these do not get released.
Using the SP sp_blocker_pss80, the blocking SPID has a waittime of 0 and a waittype of 0x0000 - the lastwaittype is WRITELOG and its status is AWAITING COMMAND
I am using MS SQLSERVER JDBC Driver SP2 (considering using jTDS)
I have tried
- increasing Transaction Log size
- Moving Transaction Log to a separate Disk
- Reducing Isolation Mode to Read Uncommitted
- Set AutoCOMMIT to true
- set Close Cursor on COMMIT
- set SelectMethod to Direct - (we use Cursor by default)
None of these have succeeded in fixing the issue.
The job will succeed if it is the first/only job to access the database.
But if another job precedes it - then the blocking occurs.
I have verified that the preceding job only holds shared dataabase locks
before the blocking job is run.
Each job will use its own JDBC connections to access the database for reading
purposes, but all of the writing goes through the blocking SPID.
Any ideas?
Thanks, Liam
View 4 Replies
ADVERTISEMENT
Jan 19, 2004
Hope someone can help me with this because its driving me potty!
I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).
This happens randomly, the scripts will be working fine and then fail a few times before magically working again!
Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!
Any help or tips would really be appreciated.
Thanks.
View 5 Replies
View Related
Oct 7, 2005
In the master database for a SQL Server instance I noticed the dt... objects (procedures, etc.) in create date of today about 7 minutes before I looked in sysobjects. No changes have been made to that instance for months. Also in other cases I noticed the same thing just different dates for each instance. Any ideas on why the dates would change?
View 1 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
Apr 17, 2007
We're having an issue with packages being blocked by SPID -2 on SQL Servers with SP2 installed. We did not have this issue on SP1 SQL instances.
The packages are pretty basic and have a truncate followed by an insert data flow. We have our Transaction options set to:
IsolationLevel = Serializable
TransactionOption = Supported
Has anyone seen this before and have any advice for resolving this issue?
View 4 Replies
View Related
Feb 12, 2003
We are experiencing an intermittent locking or hanging problem on our SQL Server (at the application level on the clients) and it has gotten worse very recently.
I haven't seen any processes that appear to be locked yet but I noticed that we don't have a lot of space available (the database is 1.4 gigs and we have about 245 megs available).
If this is inadequate disk space, could that be causing an apparent locking problem?
Thanks in advance for any help.
View 3 Replies
View Related
Apr 17, 2007
Hi,
I use SQL Server 2000 as a backend database for my Access Front end. It has been working fine for months with no problems.
This morning I arrived in work to find a problem with a table called "TimeSheets". If I try to access the table through Access I get an ODBC timeout error. Likewise, if I open the table in Enterprise manager, it opens fine, but any sorts or if I try to go to the last record, it returns the following error: "[Microsoft][ODBC SQL Server Driver] Timout expired".
So I tried to query the table in SQL Query Analyzer. Everytime it freezes on record 15,936. The table holds 17,643 records.
I tried running DBCC CHECKTABLE ('Timesheets'), and get the following message:
DBCC results for 'Timesheet - Item'.
There are 17643 rows in 401 pages for object 'Timesheet - Item'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I can't see any error messages, but by now I'm reaching the limit of my knowledge of SQL Server.
So, can anybody please help me with this? Any suggestions why my table has apparently become corrupted? Any suggestions how I might fix it?
Thanks a lot for any help
Colin
View 4 Replies
View Related
Sep 1, 2007
SQL Server 2005 SP2 (build 3054)Consider the following scenario:- A complex multi-statement table valued function is created. Let's callit dbo.tfFunc(@Param1, @Param2)- A SELECT statement is executed, that calls the above function twice,each time with a different set of parameters. In pseudocode:SELECT <column list>FROM dbo.tfFunc(1, 2) AS f1<some JOIN operatordbo.tfFunc(3, 4) AS f2ON f1.col = f2.colINNER JOIN dbo.Table1 AS t1ON ...etc.The exact statement is probably irrelevant, as long as the same table-valued function is called twice (I have observed the issue in two verydifferent statements calling the same function). The statement isexecuted in a SNAPSHOT isolation level transaction, although this mayalso be irrelevant.- The statement continues executing for a long time. If sp_who2 is run atthat time, the following row is returned for the statement connection(only relevant columns are shown):SPIDStatusBlkByCommandCPUTimeDiskIOLastBatch63 SUSPENDED63SELECT2928268308/31 18:17:37The statement appears to be blocked by itself. If sp_lock is run at thattime, the following rows are returned:spiddbidObjIdIndIdTypeResourceModeStatus63213166246410TABSch-SGRANT63213166246410TABSch-MWAITIt appears that SQL Server waits indefinitely trying to obtain a schema-modification lock on a resource which already has a schema-stability lockplaced on it by the same connection.The following is pure speculation, but it seems reasonable to assume thatthe server has materialized the result of the first call to the functionusing a temporary table in tempdb, and is trying to materialize theresult of the second call using the same temporary table (same ObjId insp_lock results).I do not know why this does not cause a deadlock error.Unfortunately, I do not have a simple repro script for this. The actualcode is rather complex. While I can devise a workaround, this does looklike a bug. I am posting it here before submitting a bug on Connect, incase anyone can shed some light. Thanks.--remove a 9 to reply by email
View 11 Replies
View Related
Oct 22, 2007
We have created a multithreaded application that reads result sets from MS SQL Server (both 2000 and 2005). The MS SQL Server app and our app reside on the same machine. We are using the latest version of ODBC32.DLL (3.526.1830.0) and SQLSRV32.DLL (2000.86.1830). Our application is written in C++ using the Visual C++ 6.0 compiler and libraries. Our app runs as a service, therefore the apparent memory leak is a real problem. Our app needs to run on a server in a closet without human intervention.
We are kicking off many user threads that each can read from the database tables. Each of the reads from the database occurs within a critical section to minimize the threads stepping on each other. The ODBC interface class follows all the steps defined in the ODBC application developers documention (see code below).
We see or app memory steadily increasing over time (we used PerfMon to monitor Private Bytes and Virtual Bytes, per ODBC documentation). If we terminate the threads which are retreiving the result sets, the memory drops back to the level noted prior to starting the threads.
The code below is admittedly inefficient, however, it should not leak memory when accessing the database. Note that it works very well, returns the result sets that we expect exactly.
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
SQLHSTMT hstmt1 = SQL_NULL_HSTMT;
SQLRETURN retCode;
SQLSMALLINT sColCount = 0;
CODBCInterfaceColumnList lColumnList;
CString strDSN;
CString strUID = _T("AccountName");
CString strPWD = _T("Password");
CString strServer;
CString strDatabase;
CString strDebugMsg;
if(!CreateDSN( pRecordList->m_strDefaultODBCConnect,
strDSN,
strDatabase,
strUID,
strPWD,
strServer))
{
strExceptionMsg.Format( _T("Cannot create DSN from connect string %s"),
pRecordList->m_strDefaultODBCConnect);
bReturn = true;
return bReturn;
}
if(pRecordList->m_strDefaultODBCConnect.IsEmpty())
{
bReturn = true;
return bReturn;
}
// Allocate the environment handle
retCode = SQLAllocHandle(SQL_HANDLE_ENV,NULL, &henv);
if(retCode != SQL_ERROR && retCode != SQL_INVALID_HANDLE)
{
// Set the environment to ODBC Version 3.0
retCode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if(retCode != SQL_SUCCESS)
{
GetErrorMsgs(hdbc, SQL_HANDLE_ENV, strDebugMsg);
}
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
// Allocate a ODBC connection handle handle
retCode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
retCode = SQLConnect( hdbc,
(UCHAR*)(LPCTSTR)strDSN,
SQL_NTS,
(UCHAR*)(LPCTSTR)strUID,
SQL_NTS,
(UCHAR*)(LPCTSTR)strPWD,
SQL_NTS);
if(retCode != SQL_SUCCESS)
{
GetErrorMsgs(hdbc, SQL_HANDLE_DBC, strDebugMsg);
OutputDebugString(strDebugMsg);
}
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
// Allocate a statement handle
retCode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt1);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
// Fixup the select statement
retCode = SQLPrepare( hstmt1,
(UCHAR*)(LPCTSTR)pRecordList->m_strSelectStatement,
pRecordList->m_strSelectStatement.GetLength());
if(retCode == SQL_SUCCESS)
{
retCode = SQLExecute(hstmt1);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
retCode = SQLNumResultCols(hstmt1,&sColCount);
if(retCode == SQL_SUCCESS || retCode == SQL_SUCCESS_WITH_INFO)
{
if(sColCount > 0)
{
if(lColumnList.DescribeColumns(hstmt1, strExceptionMsg))
{
if(lColumnList.BindColumns(hstmt1, strExceptionMsg))
{
int i = 0;
while((retCode = SQLFetch(hstmt1)) != SQL_NO_DATA)
{
if(retCode != SQL_SUCCESS)
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
break;
}
else
{
// Class to hold the result set.
CTableColumnList* pColList = new CTableColumnList();
lColumnList.PopulateColumnListObject(pColList);
pRecordList->AddColumnListRecord(pColList);
}
}
}
}
}
}
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
retCode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
retCode = SQLDisconnect(hdbc);
}
else
{
GetErrorMsgs(hstmt1, SQL_HANDLE_STMT, strExceptionMsg);
}
}
retCode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
retCode = SQLFreeHandle(SQL_HANDLE_ENV, henv);
We are at our wits end, not sure what to do next. Any help in sorting this out will be very, VERY much appreciated.
View 3 Replies
View Related
Dec 1, 2006
Found some bad behavior in the 2005 Management Studio Import and Export Wizard.
In the Select Source Tables and Views box, I selected multiple objects, and then clicked Edit Mappings.
The Transfer Settings dialog box appears, and states: "Define the settings that can be applied to all selected table transfers."
I selected the "Delete rows in existing destination tables" option to overwrite the existing data. But Management Studio DID NOT DELETE THE ROWS IN THE EXISTING TABLES. Instead, the records were appended to existing data, wreaking havoc on our month-end accounting system.
This happened multiple times, and occured even though the final screen confirmed that existing records would be deleted in each of the individual tables.
View 2 Replies
View Related
Mar 15, 2007
I used the SQL Server 2005 Upgrade Advisor to upgrade from SQL Server 2000 Enterprise to 2005 Standard. The only complaint I got concerned DTS packages, but I had none anyway. When I open SQL Server Management Studio, I can run queries, but they're against tables in the old 2000 databases. The SQL engine and Server agent are version version 8.0. Not surprising that new TSQL statements like 'BACKUP SERVICE MASTER KEY TO FILE' won't work.
Do I have to uninstall my previous version before upgrading?
Thanks.
View 3 Replies
View Related
Dec 12, 2005
I am using installshield to distribute SQL Server 2005 Express. I have the SQLEXPR.EXE file and I want to run it in /qb mode so the user can see the pretty dialogs pop up but not have to click anything.
View 6 Replies
View Related
Dec 14, 2000
Could someome please tell me what is going on with the following stored procedure:
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
View 1 Replies
View Related
Dec 13, 2005
HI!
if i have SPID. then can i get the storedprocedure name ?
Regards,
Shabber Abbas Rizvi.
View 1 Replies
View Related
Mar 28, 2006
All,
Is there any way to see the SQL for any given spid in SQL server?
I can see "properties" under enterprise mgr - which shows you a snippet of the SQL - or "sp_execute" depending on what you use to get to the DBMS.
Is there a way to take a deeper look?
Thanks,
Isaac
View 2 Replies
View Related
Apr 15, 2004
Hi,
Can I have more than one value for variable @@SPID during a connection?
PS: sorry my english.
Thanks.
View 3 Replies
View Related
May 18, 2004
Hi All,
Sql Server 7.
I have a table cust_detail when ever there is an insert/update/delete in this table it is getting blocked and in turn its blocking other table inv_credit which is doing select statement.
Please suggest me what might be the reason.
Waiting for Reply
TIA
Adil
View 1 Replies
View Related
Jun 19, 2008
Hi to all.
I have a small doubt.
In my server some processs are running slow.
With out the use of profiler hw we can find the spid's that are running slow. is there any procedure for that.
View 5 Replies
View Related
Dec 29, 2006
I am seeing intermittent blocking problems. The strange part of it is,that the SPID listed in the Blk By column is the same as the SPID thatis being blocked.Has anyone seen this before, or have any idea what this may suggest?
View 1 Replies
View Related
May 12, 2000
Hi,
Here i have a question..
Whenever i see my spid in LOCK/PROCESS ID
one command DBCC BUFFERINPUT(MY SPID) always running
and it is showing ''''tempdb.dbo.##lockinfo'''
MODE---- X
Is it pretty normal or something wrong..
i am not running any thing, just simply opened EM..
Pls any suggestions...
thank u
kavira
View 2 Replies
View Related
Dec 22, 1999
I'm going to try to explain my situation.
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)
Thanks.
View 2 Replies
View Related
Feb 20, 2003
How do COM+ transactions relate to SQL Server SPID?
I am trying to solve a deadlock issue. Using SQL trace, I find SPID 65 and 71 are trying to acquire resources that are locked by each other. But what I am confuse with is tasks performed by 65 are triggered by different transactions (same for 71). Should different COM+ transaction uses different SPID?
The entire COM+ application uses only one db login.
Please advice.
View 2 Replies
View Related
Feb 19, 1999
I can not stop a process using 'KILL spid'.What else should I try?
process has begun a transaction and does not commit.
View 2 Replies
View Related
Apr 15, 2002
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)?
Thanks,
Peter
View 1 Replies
View Related
Oct 6, 1998
What is SPID -1? I see nothing listing it in any activity monitor, but it has just recently started blocking some processes.
Thanks,
jim craddock
View 3 Replies
View Related
May 8, 2008
can we just kill spid and drop it without doing any rollback ...?
Thanks,
ServerTeam
View 9 Replies
View Related
Apr 22, 2008
We are using SQL Server 2000 SP4.
Every week to few weeks we'll have a situation where the cpu will max out on our main db server.
This server is a hoss, dual quad core with 16 gigs of ram. All the system type stuff looks good.
I look into the spids though and I notice a few of them that are very high in cpu.
I believe that I've noticed over time that a certain SPID never releases and starts fresh. Could it be possible that a program is written in a way that it just keeps using it over and over with each new user and it nevers releases that CPU?
I know this is a probably a pretty dumb question and there's alot of details I have to leave out for being brief. Ultimately I have to find out why the CPU is hitting 100 percent and hanging there.
I know it's this program because when I stop the app pool for only that program then the CPU on the SQL box goes back down.
Any help would be appreciated...and feel free to ask more questions for more detail and I'll try to give it.
View 14 Replies
View Related
Aug 8, 2000
What is "SIGNAL HANDLER" (SPID 1)?
And what is SPIDs 1-6 ?
Exec Sp_who:
SPID Status DBName Command
----- ------------------------------ ------ ----------------
1 sleeping master SIGNAL HANDLER
2 BACKGROUND master LOCK MONITOR
3 BACKGROUND master LAZY WRITER
4 sleeping master LOG WRITER
5 sleeping master CHECKPOINT SLEEP
6 BACKGROUND master AWAITING COMMAND
View 1 Replies
View Related
Aug 16, 2004
Hi Everyone,
I noticed that in my SQL 2000 system, one SPID can have multiple ECID when I run sp_who.
Recently, users get timeout message. In the server performance monitor, I can see the SQLSERVER.exe takes a lot of CPU time when this happens.
Could anyone please explain this to me and provide me the solution for it?
Thanks in advance,
Jason
View 1 Replies
View Related
Feb 1, 2002
I trying to find a way to find what the SPID of a given job that is running. I am trying to create a script that will give me the SPID the JOB_ID, and JOB_NAME. The problem comes in that if I use sysprocesses I have to pull the JOB_ID from program_name in sysprocesses and convert it into something then join it to sysjobs. Have not been sucessfull in that conversion. Any Ideas
thanks for any & all help
sean
View 1 Replies
View Related
May 10, 1999
The following is an extract from sp_who2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO
----- ---------- ------- -------- ----- ------- ------- ------- ------
162 sleeping om18682 . -1 STIPROD SELECT 236 120
Has anybody experienced a process being blocked by SPID -1.
View 4 Replies
View Related
Nov 15, 2004
Hello -
I have a Foxpro app with a SQL Server 7 backend. The Idle Connect setting on VFP is set to indefinite. I'm having some connectivity issues where the SPID for the client seems to disappear from the active SQL processes on the server when the client sits idle for a while (10 minutes or so). On some workstations, resuming activity at the client results in the SPIDs reappearing
as if they were always there with no problem. On others, the connection is not reestablished and the client gives a 1526 (VFP ODBC) error.
Does anybody know why this might be? Are the SPID connections cached at both sides and if so, how?
Thanks.
Steve
View 2 Replies
View Related
Feb 27, 2006
Hey all,
I've got a third party app running atop my sql server instance. When auditing logons in profiler, I see that the user (sa) logs off, and then immediately logs back on. Interestingly, the app logs back on with this user again, and the instance gives it the same spid as it previously had.
Can anyone explain this behaviour, or point me to a place where I can investigate ?
Thanks,
-Kilka
View 2 Replies
View Related